EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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
PROGRAMMING LANGUAGES Course Bundle - 54 Courses in 1 | 4 Mock Tests
338+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SELENIUM Course Bundle - 15 Courses in 1 | 9 Mock Tests
39+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
IOT System Course Bundle - 7 Courses in 1
43+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
JENKINS Course Bundle - 6 Courses in 1
15+ Hour of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test
 15+ Hour of HD Videos
5 Courses
1 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more