Subqueries
A subquery is a query nested inside another query — it runs first, and its result gets used by the outer query. They're useful whenever a filter depends on data you'd otherwise have to look up in a separate step.
A subquery in WHERE
Say you want every employee in a department based in Building B, but you only know that from the separate departments table. A subquery can look that up inline:
The inner query runs first, producing a list of department names — then the outer query keeps only rows whose department is IN that list.
Comparing to an aggregate
Subqueries are also the natural way to compare a row against a computed value, like the overall average:
Note: a subquery that returns a single value (like the
AVG example) can be used anywhere a plain value would go; one that returns a list of values needs an operator built for lists, like IN.