PostgreSQL Subquery

Understanding Subqueries

Subqueries in PostgreSQL are queries nested inside another query. They can be used in various parts of a SQL statement, such as SELECT, WHERE, and FROM clauses, providing flexibility and power to your database queries.

Types of Subqueries

There are two main types of subqueries: scalar subqueries, which return a single value, and table subqueries, which return a set of rows or a table.


SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Using Subqueries in SELECT

Subqueries can be used in the SELECT clause to compute a value for each row. This allows for complex calculations and aggregations.


SELECT name, (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count FROM customers;

Subqueries in WHERE Clause

Subqueries in the WHERE clause allow filtering based on conditions that involve multiple tables or complex calculations.


SELECT product_name FROM products WHERE category_id IN (SELECT id FROM categories WHERE name = 'Electronics');

Subqueries in FROM Clause

Using subqueries in the FROM clause allows treating the result of a subquery as a temporary table, making it possible to join with other tables or perform additional operations.


SELECT subquery.name, subquery.total FROM (SELECT name, SUM(amount) as total FROM sales GROUP BY name) AS subquery WHERE subquery.total > 1000;

Correlated Subqueries

Correlated subqueries reference columns from the outer query. They are evaluated once for each row processed by the outer query.


SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

Subqueries with EXISTS

The EXISTS keyword is used in a subquery to check if any rows are returned. This can be useful for checking the existence of related data.


SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.id);

Subqueries with NOT EXISTS

NOT EXISTS is used to exclude rows where a condition is met in the subquery, commonly used for finding records without related entries.


SELECT name FROM customers WHERE NOT EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.id);

Subqueries with ANY and ALL

The ANY and ALL keywords are used in subqueries to compare a value to a set of values returned by the subquery.


SELECT name FROM products WHERE price > ANY (SELECT price FROM products WHERE category_id = 1);

Subqueries with UNION

Subqueries can be combined using UNION to merge results from multiple subqueries into a single result set.


SELECT name FROM employees WHERE department_id = 1 UNION SELECT name FROM employees WHERE department_id = 2;