Multi-row Subquery
A multi-row subquery returns multiple rows (a list of values). Use IN, NOT IN, ANY, or ALL to compare a column against this list. Multi-row subqueries are the alternative to JOINs when you only need a filtering condition.
Multi-Row Operators
- IN - col = any value in the subquery result
- NOT IN - col != any value in the subquery result (beware NULLs!)
- ANY (= ANY, > ANY) - true if comparison holds for at least one result row
- ALL (= ALL, > ALL) - true only if comparison holds for ALL result rows
- > ALL (subquery) is equivalent to > MAX(subquery)
- > ANY (subquery) is equivalent to > MIN(subquery)
Multi-Row Examples
-- Users who placed at least one order (IN subquery)
SELECT name, email FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);
-- Users who NEVER placed an order (NOT IN)
SELECT name FROM users
WHERE id NOT IN (
SELECT user_id FROM orders WHERE user_id IS NOT NULL
);
-- Note: add WHERE user_id IS NOT NULL to avoid NOT IN NULL trap!
-- Result: Bob Lee
-- Products more expensive than ALL other products?
SELECT name, price FROM products
WHERE price > ALL (SELECT price FROM products WHERE name != 'Laptop');
-- Returns: Laptop (most expensive)
-- Orders with amount > ANY payment amount
SELECT id, total_amount FROM orders
WHERE total_amount > ANY (SELECT amount FROM payments);
-- Products ordered by at least one customer
SELECT name FROM products
WHERE id IN (SELECT product_id FROM order_items); -- future order_items tableQuick Quiz
Tip
Tip
Practice Multirow Subquery in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
CTEs for readability. Scalar for simple values. Avoid correlated subqueries (N+1 perf issue).
Common Mistake
Warning
A common mistake with Multirow Subquery is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Multirow Subquery from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- A multi-row subquery returns multiple rows (a list of values).
- IN - col = any value in the subquery result
- NOT IN - col != any value in the subquery result (beware NULLs!)
- ANY (= ANY, > ANY) - true if comparison holds for at least one result row