Subquery in PostgreSQL
PostgreSQL supports a query nested inside another query. The nested query is called Subquery. The Subquery can be used in SELECT, INSERT, UPDATE or DELETE statements.
SELECT <columns> FROM <table1> WHERE <column> <OPERATOR> (SELECT <column> FROM <table2>)
In the above syntax, the first SELECT query is called the outer query and another query inside brackets is called a subquery or inner query. The operators after the WHERE clause can be =, >=, <=, >, <. IN, or EXISTS.
Let's use the following
Department (parent table) and
Employee (child table) to understand a subquery.
Subquery with IN operator
PostgreSQL executes a query that has a subquery in the following sequence:
- Execute the subquery
- Pass the result of the subquery to the outer query
- Execute the outer query
SELECT * FROM Employee WHERE dept_id IN (SELECT dept_id FROM Department WHERE dept_name IN ('IT','FINANCE'));
In above query,
(SELECT dept_id FROM Department WHERE dept_name IN ('IT','FINANCE'))
is a subquery, which can return one or more rows. The subquery is executed once and sends the result
(2,3) to the outer query. Then the outer query
SELECT * FROM Employee WHERE dept_id IN (2,3) is executed and returns list of employees with
dept_id = 2 or dept_id = 3.
Subquery with EXISTS operator
In PostgreSQL, the subquery can be passed as input to EXISTS operator
The subquery has a condition specified that joins with a table of the outer query. For every row of the outer query, the subquery is executed. If the subquery returns at least one row for the outer query matching row, the EXISTS returns true. If the subquery returns no rows for the outer query matching row, the EXISTS returns false.
The EXISTS operator only checks the number of rows returned from the subquery. It does not care about the content of the row.
The subquery with EXISTS operator is also called a correlated subquery.
Let's use subquery with EXISTS operator to fetch all employees who belong to the
SELECT * FROM Employee emp WHERE EXISTS (SELECT 'X' FROM Department dept WHERE dept.dept_id = emp.dept_id AND dept_name IN ('IT','FINANCE'));
As you can see, we are getting the same rows in the result set as using IN operator.
The subquery has a joining condition
dept.dept_id = emp.dept_id that performs an inner join between the
For each row of the
Employee table, the subquery is executed once to check whether that employee belongs to the IT or FINANCE department or not.
If for an employee, a matching row is found in the department then that employee will be shown in the result set.