#PostgreSQL Subqueries

#Overview

Subqueries are queries nested inside other SQL queries. They can appear in WHERE, FROM, SELECT clauses and can return single values, single rows, or multiple rows. Subqueries enable complex data retrieval logic to be completed in a single SQL statement.

#WHERE Subqueries

#IN Subquery

IN checks if a value exists in the result set returned by the subquery:

-- Query all products in the "Electronics" category
SELECT * FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE name = 'Electronics'
);

#NOT IN Subquery

NOT IN excludes values in the subquery result:

-- Query users who have no cancelled orders
SELECT * FROM users
WHERE id NOT IN (
    SELECT user_id FROM orders WHERE status = 'cancelled'
);

#EXISTS Subquery

EXISTS checks if the subquery returns at least one row:

-- Query users who have at least one order
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

#NOT EXISTS Subquery

NOT EXISTS checks if the subquery returns no data:

-- Query products with no reviews
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM reviews r WHERE r.product_id = p.id
);

#ANY / SOME Subquery

ANY (or SOME) compares a value with any value returned by the subquery. The condition is true if it matches at least one value:

-- Query products with price higher than any category average price
SELECT * FROM products
WHERE price > ANY (
    SELECT AVG(price) FROM products GROUP BY category
);

#ALL Subquery

ALL compares a value with all values returned by the subquery. The condition must be true for all values:

-- Query products with price higher than all category maximum prices
SELECT * FROM products
WHERE price > ALL (
    SELECT MAX(price) FROM products GROUP BY category
);

#FROM Subqueries (Derived Tables)

Subqueries can be placed in the FROM clause as temporary tables (also called derived tables or inline views):

-- Query category statistics with more than 10 products
SELECT * FROM (
    SELECT
        category,
        COUNT(*) AS product_count,
        AVG(price) AS avg_price
    FROM products
    GROUP BY category
) AS category_stats
WHERE product_count > 10;

#SELECT Subqueries (Scalar Subqueries)

Subqueries can be placed in the SELECT clause as computed columns. In this case, the subquery must return a single value (scalar value):

-- Query each user with their order count
SELECT
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

#Correlated Subqueries

A correlated subquery references columns from the outer query. The subquery is re-executed for each row processed by the outer query:

-- Query users with large orders (amount over 1000)
SELECT u.* FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
    AND o.total > 1000
);

-- Query the highest priced product in each category
SELECT p.* FROM products p
WHERE p.price = (
    SELECT MAX(price)
    FROM products
    WHERE category = p.category
);

#Summary

Subquery key points:

• WHERE clause can use IN, EXISTS, ANY, ALL operators
• Subqueries in FROM clause are used as derived tables and must be aliased
• Scalar subqueries in SELECT clause are used for computed columns and must return a single value
• Correlated subqueries reference outer query columns and are re-executed for each row
• NOT IN has special behavior with NULL values; prefer NOT EXISTS