A subquery is a query nested inside another. A non-correlated subquery runs independently and its result feeds the outer query; a correlated subquery references the outer query's current row and is conceptually re-evaluated per outer row. Both let testers express 'rows related to the result of another question'.
Subqueries appear in several positions: in WHERE (filter by another query's result, often with IN or a comparison), in FROM (a derived table), or in SELECT (a scalar value per row). A non-correlated subquery like WHERE user_id IN (SELECT id FROM users WHERE country = 'IN') computes the inner set once. A correlated subquery references the outer row — e.g. 'orders whose total is above that customer's own average' — so it depends on each outer row and is logically evaluated for each. EXISTS is a common correlated form that checks for the existence of related rows and short-circuits, often clearer and faster than IN for 'has at least one'. Testers use subqueries to express layered validations without multiple round trips: find orders belonging to test users, items priced above their category average, users with no matching audit row. A caution: NOT IN with a subquery that can return NULL behaves unexpectedly (it can return no rows), so NOT EXISTS is usually safer for anti-checks.
To find orders larger than the placing customer's own average order, a tester uses a correlated subquery: SELECT o.id FROM orders o WHERE o.total > (SELECT AVG(o2.total) FROM orders o2 WHERE o2.user_id = o.user_id) — each order compared against its own customer's average.
-- Non-correlated: orders by users in India (inner set computed once)
SELECT id, total FROM orders
WHERE user_id IN (SELECT id FROM users WHERE country = 'IN');
-- Correlated: orders above that customer's own average
SELECT o.id, o.total
FROM orders o
WHERE o.total > (
SELECT AVG(o2.total) FROM orders o2 WHERE o2.user_id = o.user_id
);
-- EXISTS: users who have at least one confirmed order
SELECT u.id, u.email FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'confirmed'
);
-- Anti-check: users with NO orders — prefer NOT EXISTS over NOT IN
SELECT u.id FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);Scenario: A tester writes 'users who have never placed an order' as SELECT id FROM users WHERE id NOT IN (SELECT user_id FROM orders). It returns zero rows even though there are clearly users with no orders. What subtle bug is this, and how do you fix it?