SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

Part 11 of 18 · ~2 min

Subqueries: Scalar, Column, and Table

Slide three is top earners, and building it means nesting one SELECT statement inside another — a subquery — in three genuinely different shapes. Where the SQL parser lets you drop a subquery in depends entirely on how much the inner query hands back: a single number can go almost anywhere, a whole column of them only where a list makes sense, and a full result set only somewhere that's willing to treat it as a table.

-- Shape 1, a single number: drops in anywhere SQL expects one value
SELECT name, salary FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
-- Bob (60000), Carol (70000) — everyone clearing the company-wide average; Dave's NULL never entered that average

-- Shape 2, a column of values: pairs naturally with IN / ANY / ALL
SELECT dept FROM Departments
WHERE dept IN (SELECT dept FROM Employees);
-- Sales, IT, HR — every department with at least one person in it; Finance doesn't make the list

-- Shape 3, an entire result set: stand it up as a table and query it
SELECT sub.dept, sub.avg_salary
FROM (
  SELECT dept, AVG(salary) AS avg_salary
  FROM Employees
  GROUP BY dept
) AS sub
WHERE sub.avg_salary >= 55000;

Shape three — a subquery dropped straight into FROM, often called a derived table — comes with one non-negotiable rule attached: it needs a name (sub, in the query above). This isn't a matter of taste. Once that subquery is sitting inside FROM, the outer query has to be able to say sub.dept and sub.avg_salary, and there's no way to write .dept after a bare, unnamed parenthesized query — the alias is the only handle the rest of the statement has to grab onto.

One thing worth flagging before you go looking for it: standard SQL also defines ANY and ALL as quantifiers you can drop in front of a column subquery — salary > ALL (...), salary = ANY (...), that family. Postgres, SQL Server, and MySQL all support them. SQLite does not — paste salary > ALL (SELECT ...) into the code lab and you'll get a flat syntax error, not a wrong answer. The workaround is the same wherever you'd reach for one: = ANY (...) is just IN (...) under a different name, and > ALL (...) is the same question as > (SELECT MAX(...) ...) — compare against the aggregate directly instead of quantifying the comparison.