Scalar & IN Subqueries
Overview
A subquery is a SELECT nested inside another SQL statement. It runs first and feeds its result into the outer query. Three common shapes: 1. **Scalar subquery** — returns exactly ONE value (one row, one column). You can use it anywhere a single value is expected: in SELECT, WHERE, or even HAVING. SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products) ORDER BY price; The inner SELECT AVG(price) returns 85.82 (the average of all 6 products). The outer query then keeps only products whose price exceeds that single number: Monitor (199.99), Desk (149.99), Chair (89.99). 2. **IN subquery** — returns a list of values (one column, many rows). Used with WHERE col IN (subquery) to filter the outer query against that list. SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE status = 'paid') ORDER BY name; The inner query finds customer_ids with paid orders: {1, 2, 3, 5, 6}. The outer query returns the matching names: Alice, Bob, Carlos, Emma, Farah. 3. **Derived Table** — a subquery in the FROM clause that acts as a temporary table. It returns a full grid of rows and columns and MUST be given an alias. SELECT AVG(total) FROM (SELECT SUM(price) as total FROM orders GROUP BY customer_id) AS sub; Think of subqueries as 'compute a value or a list first, then plug it in'. The inner query produces a row-set; the outer query consumes it.
Subqueries let you express multi-step logic in a single statement — 'find products above the average price' or 'find customers who ordered at least once' — without needing temporary tables or multiple round-trips. They are the bridge between simple single-table queries and the powerful compositional SQL that real dashboards and APIs rely on.
Where used: filtering rows against a derived threshold (e.g. above-average price), checking membership in a dynamic list (e.g. customers who placed orders), embedding computed values in SELECT for inline comparisons
Why learn this
- Write single-statement queries that compare rows against aggregates — 'products priced above average' — instead of hard-coding values
- Filter against dynamic lists — 'customers who placed a paid order' — without needing a JOIN
Query walkthrough
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price;Focus: Watch the scalar subquery resolve to a single number (85.82), then see WHERE filter the products row-set down to only those above that threshold.
Common mistakes
- Scalar subquery returns more than one row: If you write WHERE price > (SELECT price FROM products WHERE category = 'Electronics'), Postgres raises an error because the inner query returns 3 rows, but '>' expects exactly one value. Use IN, ANY, or add LIMIT 1 / an aggregate to guarantee a single row.
- Using IN when a JOIN would be clearer and faster: WHERE id IN (SELECT customer_id FROM orders) works but on large tables a JOIN can be faster and makes the relationship explicit. Use subqueries when the logic is genuinely 'compute a value/list, then filter', not when you need columns from both tables.
- Forgetting that IN ignores NULLs in the list: If the inner query returns {1, NULL, 3}, WHERE id IN (...) will match 1 and 3 but not NULL — NULL = NULL is unknown, not true. This can silently drop expected matches if the subquery column is nullable.
Glossary
- nested
- Placed or hidden inside something else of the same type, like one query sitting inside another.
- compositional
- Relating to the ability to combine simple parts to build more complex structures or logic.
- genuinely
- Truly or authentically; used when the logic actually requires a specific approach rather than just seeming to.
Recall questions
- What is the difference between a scalar subquery and an IN subquery?
- What happens if a scalar subquery returns more than one row?
- Can you use a subquery in the SELECT clause? Give an example.
Understanding checks
Which products appear and in what order?
Chair (89.99), Desk (149.99), Monitor (199.99)
AVG(price) of all 6 products = (49.99+19.99+199.99+149.99+89.99+4.99)/6 = 85.82. Only Chair, Desk, and Monitor exceed 85.82, ordered by ascending price.
Which customers does this return?
Greg, Hana
The inner query returns all customer_ids that have orders: {1,2,3,4,5,6}. NOT IN keeps customers whose id is not in that set — Greg (7) and Hana (8). This is the 'anti-semi-join' pattern for finding rows with no related records.
Practice tasks
Switch from scalar to IN subquery
This query finds products priced above the average. Modify it to instead find products whose id appears in any order_item (i.e. products that have been ordered at least once). Use an IN subquery on the order_items table and keep the ORDER BY.
Challenge
Customers who spent above average
Write a query that lists the names of customers whose total spending (SUM of product price × quantity across their orders and order_items) exceeds the average customer total spending. Order by name. Hint: you will need JOINs inside the subqueries and the outer query.
Continue learning
Previous: WHERE & Operators
Previous: Aggregate Functions
Next: Correlated Subqueries
Next: CTEs (WITH)