4.Digging Deeper Without Repeating Yourself

A scalar subquery returns a single value, usable anywhere a single value is expected, like inside a WHERE comparison.

SELECT Name, BalanceDue
FROM Customers
WHERE BalanceDue > (SELECT AVG(BalanceDue) FROM Customers);

The subquery in parentheses runs first, producing one number — the average. The outer query then compares every customer's balance against that already-computed value.

IN / NOT IN compares a value against a whole list of results returned by a subquery.

SELECT Name
FROM Products
WHERE ProductID NOT IN (SELECT ProductID FROM OrderItems);

Here, the subquery returns every ProductID that has ever appeared in an online order, and NOT IN keeps only the products whose ID is missing from that entire list.

EXISTS checks whether a subquery returns any rows at all — it doesn't care what those rows contain, only whether at least one exists. Notice this subquery references c.CustomerID from the outer query — a subquery that does that is called a correlated subquery, and it re-runs once for every row the outer query is checking, not just once.

SELECT Name
FROM Customers c
WHERE EXISTS (
  SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID
);

Priya, Maria, and Raj come back — the exact three customers with a real row in Orders.

NOT EXISTS is the mirror of EXISTS — true only when the correlated subquery finds no matching row at all.

SELECT Name
FROM Customers c
WHERE NOT EXISTS (
  SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID
);

This returns the exact same eight customers as the LEFT JOIN ... WHERE ... IS NULL query back in the JOIN chapter — a third way to ask the identical question, alongside NOT IN and a LEFT JOIN.

Subquery vs. JOIN: the EXISTS query above and this JOIN answer the exact same question, two different ways. A JOIN naturally returns one row per match — a customer with two orders would show up twice — while EXISTS only ever checks whether a match exists at all.

SELECT DISTINCT c.Name
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID;

Same three names — Priya, Maria, Raj — once DISTINCT removes Priya's duplicate row (she has two orders). Rule of thumb: reach for a JOIN when the result needs actual columns from both tables; reach for EXISTS when it's purely a filter, with nothing from the other table in the final output.

The same correlated idea works inside SELECT too, not just WHERE — a subquery computed fresh for every single row, using that row's own values.

SELECT Name,
  (SELECT COUNT(*) FROM Orders o WHERE o.CustomerID = c.CustomerID) AS OrderCount
FROM Customers c;

Priya shows 2, Maria and Raj each show 1, and everyone else shows 0 — one full scan of Orders per customer, recomputed for every row in Customers.

This is the classic correlated-subquery pattern: comparing each row to the average of its own group — here, each sale against the average price of everything sold on that exact same day, correlated on s2.SaleDate = s.SaleDate.

SELECT s.SaleDate, p.Name, p.Price
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID
WHERE p.Price > (
  SELECT AVG(p2.Price)
  FROM Sales s2
  JOIN Products p2 ON s2.ProductID = p2.ProductID
  WHERE s2.SaleDate = s.SaleDate
);

Only Apples (twice) and Rice (twice) show up — the only sales priced above whatever else sold on their particular day.

ANY (sometimes written SOME) makes a comparison true if it holds against at least one value a subquery returns — > ANY (list) always means the same thing as > MIN(list). SQLite, and this Playground, doesn't support the ANY keyword directly, so this is written as the equivalent MIN comparison — same idea, syntax that actually runs.

SELECT Name, BalanceDue
FROM Customers
WHERE BalanceDue > (
  SELECT MIN(BalanceDue) FROM Customers WHERE BalanceDue > 0
);

Four customers beat the smallest actual debtor (Leah, at 15) — Priya, Maria, Farah, and Raj.

ALL requires the comparison to hold against every value a subquery returns — > ALL (list) always means the same thing as > MAX(list). Same SQLite limitation as ANY; the MAX rewrite is the portable, always-correct equivalent.

SELECT Name, BalanceDue
FROM Customers
WHERE BalanceDue > (
  SELECT MAX(BalanceDue) FROM Customers WHERE BalanceDue > 0
);

Zero rows — nobody currently owes more than Priya's own 140, the largest balance in the list. Zero is the honest, correct answer here, not a bug.

A subquery can also sit inside FROM, acting as a temporary, unnamed table the rest of the query selects from — often called a derived table.

SELECT * FROM (
  SELECT p.Name, COUNT(*) AS UnitsSold
  FROM Sales s
  JOIN Products p ON s.ProductID = p.ProductID
  GROUP BY p.Name
) AS ProductSales
WHERE UnitsSold > 1
ORDER BY UnitsSold DESC;

This is the exact same result as chapter 3's HAVING query — the inner subquery does the grouping, and the outer WHERE filters the already-grouped rows, instead of HAVING filtering them in one step.

A subquery can sit inside HAVING too, filtering groups against a computed value instead of a literal number. This one is also a nested subquery — a subquery (the AVG) built directly on top of another subquery (the derived table counting units per product).

SELECT p.Name, COUNT(*) AS UnitsSold
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID
GROUP BY p.Name
HAVING COUNT(*) > (
  SELECT AVG(UnitsSold) FROM (
    SELECT COUNT(*) AS UnitsSold FROM Sales GROUP BY ProductID
  )
);

Every product sells 2 units on average; only Apples sells more (3), so only Apples survives HAVING.

A CTE (Common Table Expression, written as WITH ... AS) names a subquery upfront so the main query can read cleanly, especially useful when the same logic would otherwise be repeated.

WITH ProductSales AS (
  SELECT p.Name, COUNT(*) AS UnitsSold
  FROM Sales s
  JOIN Products p ON s.ProductID = p.ProductID
  GROUP BY p.Name
)
SELECT * FROM ProductSales ORDER BY UnitsSold DESC;

This is the identical derived table from two snippets ago, just given a real name — ProductSales — instead of sitting anonymously inside FROM.

WITH ProductSales AS (
  SELECT p.Name, COUNT(*) AS UnitsSold
  FROM Sales s
  JOIN Products p ON s.ProductID = p.ProductID
  GROUP BY p.Name
),
TopSeller AS (
  SELECT Name FROM ProductSales ORDER BY UnitsSold DESC LIMIT 1
)
SELECT * FROM TopSeller;

TopSeller is built directly on top of ProductSales, defined just above it in the same WITH clause — a multi-step question, answered one readable, named piece at a time. A derived table can't do this; it has no name for a later part of the query to build on.