When should I write a JOIN and when should I write a subquery?
Use a join when you want columns from the other table beside the columns of this one. Use a subquery when you want a value that the query treats as a single number or a single list.
So what is the difference in practice? A join adds columns to your rows. A subquery gives you one value, and you can use it as a filter, as a value in the select list, or as a table.
-- A join: the order columns come beside the customer columns.
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;
-- A subquery: the number of orders comes beside the customer columns.
SELECT
c.name,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;
There is one practical difference that matters more than the theory. A subquery in the select list is run once for every row of the outer query, unless the engine is clever enough to work it out otherwise. That means it can be much slower than the join, even when the answer is the same.
There are two more things to know. A subquery that returns many rows can only be used where a list is allowed, such as the IN operator or the FROM clause. And a subquery that returns one row and one column can be used almost anywhere a value can go.
So the rule to remember is this. If you want to show columns from two tables, write a join. If you want one number that depends on the current row, a subquery is the natural way to write it, and then check that it is not slow.
The next two questions in this topic show the same query both ways.