What are the different kinds of subquery, and where can each one appear?
There are three places a subquery can go, and what it means depends on the place.
-- In the FROM clause. It gives you a table.
SELECT * FROM (SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id) AS totals;
-- In the WHERE clause. It gives you a value or a list.
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'London');
-- In the SELECT list. It gives you one value for each row.
SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;
So what are the kinds? It helps to think about what the subquery gives back.
A subquery that gives back one row and one column is a scalar subquery, and you can use it anywhere a single value can go. That is the select list, the where clause, the having clause, and the values of an insert.
A subquery that gives back one column and any number of rows is a list subquery, and you can only use it with the IN operator, with the ANY and ALL operators, or with the EXISTS operator.
A subquery that gives back a whole table is a derived table, and it goes in the FROM clause. It needs a name, and that is what the word AS is for.
There is one more thing to know. A subquery that refers to the row of the outer query is called correlated, and it behaves quite differently. That is the subject of the last question in this topic.
The next two questions in this topic put subqueries to work.