Subqueries, IN and EXISTS

A subquery is a SELECT inside another. It can return a value (scalar), a list (for IN) or just "exists or not" (for EXISTS).

Scalar: a single value

SELECT id, amount,
       ROUND(amount - (SELECT AVG(amount) FROM orders), 1) AS above_average
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders)
ORDER BY amount DESC, id;

id  | amount | above_average
--- | ------ | -------------
105 | 1050   | 650
102 | 900    | 500
110 | 900    | 500

If the scalar subquery returns more than one row, PostgreSQL and Spark raise an error; SQLite takes the first one, silently. One more reason not to rely on SQLite as a reference for the standard.

Correlated EXISTS

It is "correlated" because it uses the outer row (c.id). Read it as: customers for whom there exists some order above 500.

SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.amount > 500)
ORDER BY c.name;

name
-----
Ana
Bruno
Diego

IN with a computed list

SELECT name
FROM products
WHERE id IN (SELECT product_id FROM order_items)
ORDER BY name;

name
----------
HDMI cable
Keyboard
Monitor
Mouse
Webcam

• IN (subquery) and EXISTS give the same result; NOT IN and NOT EXISTS do not, if there is a NULL (see NULL and three-valued logic).
• The optimizer usually turns EXISTS and IN into a semi-join. In Spark, it is the same plan as a LEFT SEMI JOIN.