SQL Subqueries

SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users); # subquery returns single value

SELECT * FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE active = 1
); # subquery returns multiple values

SELECT avg_price
FROM (
    SELECT AVG(price) AS avg_price FROM products GROUP BY category
) AS subquery; # derived table

SELECT name, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary) FROM employees e2
    WHERE e1.department = e2.department
); # references outer query

SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
); # check if exists