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:

1. Execute the subquery
2. Pass the result of the subquery to the outer query
3. 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 EXISTS <sub_query>.

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 IT and FINANCE department.

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 Employee and Department tables. 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.