What can legally appear in a SELECT list, and why is a subquery there useful?
Four kinds of thing can go in the select list. Columns, plain values, expressions built from them, and calculations that need the other rows.
a column c.name
a plain value 'unknown'
an expression c.name || ' (' || c.country || ')'
a calculation SUM(o.amount) with a group by
a scalar subquery (SELECT COUNT(*) ...)
a case expression CASE WHEN ... END
a window function ROW_NUMBER() OVER (...)
So why is a subquery in there useful? Because it is the way to show a number that depends on the row you are looking at, without joining the other table into the answer and adding all of its columns.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
-- The count of the orders, beside each customer's own columns.
SELECT
c.id,
c.name,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c
ORDER BY c.id;
So what is the trade-off? The subquery is run once for every row of the outer query, so on a table with a million customers the engine does a million small lookups. A good planner often turns it into a join and does it in one pass, so it is worth looking at the plan before you worry about it.
The next two questions in this topic put it to work.