EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Subquery
 

PostgreSQL Subquery

Priya Pedamkar
Article byPriya Pedamkar

Updated May 6, 2023

PostgreSQL Subquery

 

 

Introduction to PostgreSQL Subquery

PostgreSQL Subquery is also known as the nested query or inner query; It is an embedded where clause query within another query. A subquery in PostgreSQL can retrieve data that narrows down the data retrieved in the main query by using it as a condition. It is used to select, insert, update, and delete statements with operators like >, =, <, >=, <=, IN, etc. There are many rules available to use a subquery in PostgreSQL.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax

Below is the syntax as follows.

1. With a select statement

Select column_name1, .., column_nameN
From table_name1 [, table_name2]
Where column_name operator
Select column_name from table_name1 [, table_name2]
[Where] condition)

2. With Insert statement

INSERT INTO table_name [ (column_name1 [, column_name2 ]) ]
SELECT [ *|column_name1 [, column_name2 ] ]
FROM table_name1 [, table_name2]
[WHERE VALUE OPERATOR]

3. With update statement

UPDATE table_name
SET column_name = new_value
[WHERE OPERATOR [VALUE]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[WHERE) ]

4. With delete statement

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]

Below is the parameter description of the above syntax as follows.

  • Select – Used to select the statement.
  • Column_name1 to column_nameN – It specifies the Column name.
  • From – You use the “From” clause to retrieve data from the chosen table.
  • TABLE_NAME – Used to specify the Table name.
  • Where condition – Where condition specified to fetch data per the query described to fetch the data.
  • Operator – Operator is used to specifying the condition. Operators like >, =, <, >=, <=, IN, etc., are used in PostgreSQL.
  • Insert – Used to Insert statement.
  • Delete – Used to Delete statement.
  • Update – Used to Update statement.

Working of PostgreSQL Subquery

Below is the working as follows.

  • A nested subquery, also known as an inner query, is what this refers to.
  • We have used the PostgreSQL subquery to select, insert, update, and delete statements.
  • We have selected Subquery in operators like >, =, <, >=, <=, and IN. These operators are used along with the where condition.
  • It’s important to note that when working with PostgreSQL, you cannot use a subquery between operators with another subquery. However, you can use it within the Subquery itself.
  • Enclose it with parentheses.
  • We have used only one column in the select clause and multiple columns in the main query to compare it with the selected columns. Subqueries in PostgreSQL do not support the use of the “Order by” clause, but they can still be utilized in the main query.
  • Instead of the order by, we have used group by to perform the same operation as order by.
  • It will return more than one row.
  • You can use a subquery to return data that serves as a condition to restrict further the data retrieved by the main query.

Types of PostgreSQL Subquery

Below is the type as follows. We have used Employee_test1 and Employee_test2 tables to describe types.

1. Table1 – Employee_test1

CREATE TABLE Employee_Test1 ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL);

PostgreSQL Subquery Table 1 - 1

INSERT INTO Employee_Test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (1, 'ABC', 'Pune', '1234567890', 20000, '01-01-2020');

PostgreSQL Subquery Table 1 - 2

INSERT INTO Employee_Test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (2, 'PQR', 'Pune', '1234567890', 20000, '01-01-2020');

PostgreSQL Subquery Table 1 - 3

INSERT INTO Employee_Test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (3, 'XYZ', 'Mumbai', '1234567890', 35000, '02-01-2020');

PostgreSQL Subquery Table 1 - 4

INSERT INTO Employee_Test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (4, 'BBS', 'Mumbai', '1234567890', 45000, '02-01-2020');

PostgreSQL Subquery Table 1 - 5

INSERT INTO Employee_Test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (5, 'RBS', 'Delhi', '1234567890', 50000, '03-01-2020');

PostgreSQL Subquery Table 1 - 6

select * from Employee_Test1;

PostgreSQL Subquery Table 1 - 7

2. Table2 – Employee_test2

CREATE TABLE Employee_Test2 ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL);

PostgreSQL Subquery Table 2 - 1

INSERT INTO Employee_Test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (1, 'PQR', 'Pune', '1234567890', 20000, '01-01-2020');

PostgreSQL Subquery Table 2 - 2

INSERT INTO Employee_Test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (2, 'XYZ', 'Mumbai', '1234567890', 35000, '02-01-2020');

PostgreSQL Subquery Table 2 - 3

INSERT INTO Employee_Test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (3, 'BBS', 'Mumbai', '1234567890', 45000, '02-01-2020');

PostgreSQL Subquery Table 2 - 4

INSERT INTO Employee_Test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (4, 'RBS', 'Delhi', '1234567890', 50000, '03-01-2020');

PostgreSQL Subquery Table 2 - 5

INSERT INTO Employee_Test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (6, 'ABC', 'Pune', '1234567890', 20000, '01-01-2020');

PostgreSQL Subquery Table 2 - 6

select * from Employee_test2;

PostgreSQL Subquery Table 2 - 7

  • Subqueries with the SELECT Statement

 Below is the example of the Subqueries with the SELECT Statement as follows.

select * from Employee_Test1 where emp_id IN (select emp_id from Employee_Test2 where emp_salary > 35000);

Sub queries with the SELECT Statement

  • Subqueries with the INSERT Statement

 Below is the example of the Subqueries with the INSERT Statement as follows.

INSERT INTO Employee_Test1 SELECT * FROM Employee_Test2 WHERE EMP_ID IN (SELECT EMP_ID FROM Employee_Test2) ;

Sub queries with the INSERT Statement 1

select * from Employee_test1;

Sub queries with the INSERT Statement 2

  • Subqueries with the UPDATE Statement

 Below is the example of the Subqueries with the UPDATE Statement as follows.

UPDATE Employee_Test1 SET EMP_SALARY = EMP_SALARY * 5 WHERE EMP_ID IN (SELECT EMP_ID FROM Employee_Test2 WHERE EMP_ID > 2 );

Sub queries with the UPDATE Statement 1

select * from Employee_test1;

Sub queries with the UPDATE Statement 2

  • Subqueries with the DELETE Statement

 Below is the example of the Subqueries with the DELETE Statement as follows.

DELETE FROM Employee_Test1 WHERE EMP_ID IN (SELECT EMP_ID FROM Employee_Test2 WHERE EMP_ID > 2);

Sub queries with the DELETE Statement 1

select * from Employee_test1;

Sub queries with the DELETE Statement 2

Conclusion

A subquery in PostgreSQL is also called a nested or inner subquery. It does not use the ORDER BY clause, but the main query can use it to order the results. We used to group by clause instead of the order by clause in the PostgreSQL subquery.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Subquery” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL Date Functions
  2. Guide to PostgreSQL Schema
  3. Complete Guide to PostgreSQL IN Operator
  4. Learn the PostgreSQL LIMIT

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW