Scalar Subqueries

Scalar Subqueries

A scalar subquery returns one value and can stand wherever a literal could: in the select list, a comparison, ORDER BY or SET @avg = (...). WHERE price > AVG(price) is illegal, since an aggregate cannot filter single rows, so the average needs a query of its own:

Scalar subqueries in WHERE and in the select list, and one that breaksSQL

SELECT sku, price, ROUND(price - (SELECT AVG(price) FROM products), 2) AS vs_avg
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC LIMIT 2;
SELECT (SELECT id FROM orders WHERE customer_id = 1) AS ana_order;

Output

+-----------+-------+--------+
| sku       | price | vs_avg |
+-----------+-------+--------+
| BK-LAR-01 | 49.00 |  17.01 |
| BK-SQL-01 | 44.50 |  12.51 |
+-----------+-------+--------+
2 rows in set (0.001 sec)
ERROR 1242 (21000): Subquery returns more than 1 row

EXPLAIN FORMAT=TREE marks the average "run only once". No row gives NULL, as for Gus, who never ordered. Two rows give error 1242 at run time, so guarantee one row by shape: an ungrouped aggregate, a primary-key lookup, or ORDER BY ... LIMIT 1. Two columns are error 1241.

MENU