Subqueries

Code

-- Scalar subquery
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

-- IN subquery
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- EXISTS subquery
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- FROM subquery
SELECT dept, AVG(salary) AS avg_salary
FROM (SELECT dept, salary FROM employees) sub
GROUP BY dept;

-- WITH subquery (CTE)
WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 50000
)
SELECT dept, COUNT(*) FROM high_earners GROUP BY dept;

Explanation

Subqueries can appear in SELECT, FROM, WHERE clauses; CTE (WITH) improves readability.

More SQL Snippets