SQL Subqueries

Overview

Subqueries are queries nested inside other SQL queries. They can appear in WHERE, FROM, SELECT clauses, enabling 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
);

FROM Subqueries (Derived Tables)

Subqueries can be placed in the FROM clause as temporary tables:

-- 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:

-- 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

Correlated subqueries reference columns from the outer query and are re-executed for each row:

-- 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 operators
• Subqueries in FROM clause are used as derived tables
• Scalar subqueries in SELECT clause are used for computed columns
• Correlated subqueries reference outer query columns