Introduction to PostgreSQL Subquery
PostgreSQL Subquery is also known as the nested query or inner query; it is the query within another query and embedded within where clause. It is used to returns data that will be used in the main query as a condition to restrict the data further to be retrieved. It is used to select, insert, update and delete statements along with the operators like >, =, <, >=, <=, IN, etc. There are many rules available to use a subquery in PostgreSQL.
Syntax of PostgreSQL Subquery
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 are as follows.
- Select – Used to select the statement.
- Column_name1 to column_nameN – It specifies the Column name.
- From – From clause is used to select the specified table from which we need to fetch the data.
- TABLE_NAME – Used to specify the Table name.
- Where condition – Where condition specified to fetch data as per the condition described in the query to fetch the data.
- Operator – Operator, is used to specifying the condition. Operators like >, =, <, >=, <=, IN etc. 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.
- It is a nested subquery, also knows as an inner query In PostgreSQL.
- We have used the PostgreSQL subquery in select, insert, update and delete statements.
- We have selected subquery in operators like >, =, <, >=, <=, IN. These operators used along with where condition in PostgreSQL subquery.
- In PostgreSQL, subquery between operators cannot be used with a subquery, but it is used within the subquery.
- It must be enclosed with parenthesis.
- Only one column we have used in the select clause and multiple columns is in the main query to compare it with selected columns.
- Order by clause is not used in the PostgreSQL subquery, but we can use it within the main query.
- Instead of the order by, we have used group by to perform the same operation as order by in the PostgreSQL subquery.
- It will return more than one row.
- The subquery is used to return data that is used in the main query as a condition to restrict the data further to be retrieved from the 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);
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');
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');
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');
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');
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');
select * from Employee_Test1;
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);
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');
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');
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');
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');
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');
select * from Employee_test2;
- Subqueries with the SELECT Statement
Below is the example of the Subqueries with the SELECT Statement is as follows.
select * from Employee_Test1 where emp_id IN (select emp_id from Employee_Test2 where emp_salary > 35000);
- Subqueries with the INSERT Statement
Below is the example of the Subqueries with the INSERT Statement is as follows.
INSERT INTO Employee_Test1 SELECT * FROM Employee_Test2 WHERE EMP_ID IN (SELECT EMP_ID FROM Employee_Test2) ;
select * from Employee_test1;
- Subqueries with the UPDATE Statement
Below is the example of the Subqueries with the UPDATE Statement is 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 );
select * from Employee_test1;
- Subqueries with the DELETE Statement
Below is the example of the Subqueries with the DELETE Statement is as follows.
DELETE FROM Employee_Test1 WHERE EMP_ID IN (SELECT EMP_ID FROM Employee_Test2 WHERE EMP_ID > 2);
select * from Employee_test1;
Conclusion
It is also known as nested as well as an inner subquery. Order by clause not used in Subquery but used in the main query. We used to group by clause instead of the order by clause in the PostgreSQL subquery.
Recommended Articles
This is a guide to PostgreSQL Subquery. Here we discuss the types, working, and parameters along with the examples. You may also have a look at the following articles to learn more –
