What is the difference between a scalar subquery and a table subquery?
The difference is what the subquery gives back. A scalar subquery gives back one value, and a table subquery gives back a set of rows.
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
);
-- A scalar subquery. One row and one column.
SELECT name,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;
-- A table subquery. A whole set of rows.
SELECT totals.customer_id, totals.total
FROM (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) AS totals;
So where can each one go? A scalar subquery can go anywhere a single value can go, so in the select list, in the where clause, in the having clause, and in the values of an insert. A table subquery can only go in two places, which are the FROM clause as a derived table, and beside the IN, ANY, ALL and EXISTS operators.
There is one more difference that is worth knowing. A scalar subquery is run once for every row of the outer query, unless the engine is clever enough to work it out otherwise. A derived table is worked out once and then read as a table, so it is usually the faster of the two when the outer query has many rows.
The next question in this topic looks at what happens when a scalar subquery gives back more than it should.