Subqueries
SQL Subquery Interview Questions
A subquery is a query inside another one, used when a question needs two steps: work something out first, then use it. A CTE is the same idea written so the query can be read from top to bottom instead of inside out.
What subquery questions ask you to do
Across the questions here, these are the shapes that come up:
- Compare each row against an overall figure such as the average
- Find rows whose key does not appear in another table
- Filter on a value that has to be calculated first
- Break a two-step question into two readable steps
Common mistakes with subqueries
Asking for rows that are NOT IN a list containing a single empty value returns nothing at all. Not an error, just an empty result, because nothing can be shown to differ from an unknown. Phrasing it as NOT EXISTS instead does what was meant and is safe against empty values.
