Sometimes the answer to one question is needed to ask another.
Direct answer
A subquery is a SELECT nested inside another statement. It can appear in WHERE (to compare with a value or list), in FROM (as a temporary table) or in SELECT (to compute a column). Common table expressions (WITH) can make complex queries easier to read.
Examples
-- customers who spent more than average
SELECT name FROM customers
WHERE id IN (
SELECT customer_id FROM orders
GROUP BY customer_id
HAVING SUM(total) > (SELECT AVG(total) FROM orders)
);
-- subquery in FROM
SELECT city, COUNT(*) FROM (
SELECT * FROM customers WHERE active = 1
) AS active_customers
GROUP BY city;
A CTE alternative
WITH active AS (
SELECT * FROM customers WHERE active = 1
)
SELECT city, COUNT(*) FROM active GROUP BY city;
Tips
- Test the inner query on its own first.
- Prefer CTEs for long queries.
- Watch performance on large tables.
Where Teyro fits
Teyro's SQL and Databases for Beginners course turns this into short lessons you can play in a few minutes a day. It has 50 lessons, and you can start free.
The bottom line
Break a hard question into smaller queries.
