Subqueries and CTEs
Scalar, row and table subqueries, correlated versus uncorrelated evaluation, EXISTS/IN/ANY/ALL, WITH clauses including recursive CTEs, and CTE versus derived table versus view. Interviewers ask because naming the steps of a hard query — and knowing that a correlated subquery may run per row — is what separates readable SQL from slow SQL.
part ofSQLoverview, primer and where to startread it →on this pageshowhide
explore
- Subqueries18 questions
- Scalar, Row, and Table Subqueries5 questions
- Subqueries in SELECT, FROM, and WHERE3 questions
- Correlated vs Uncorrelated Subqueries5 questions
- EXISTS, IN, ANY, and ALL Semantics5 questions
- Common Table Expressions19 questions
- WITH Clause and Readability5 questions
- Recursive CTE Mechanics5 questions
- Hierarchy Traversal and Cycle Handling5 questions
- CTE vs Derived Table vs View4 questions
- AI & Data Scientistrole
- AI Engineerrole
- BI Analystrole
- Backend Developerrole
- Cyber Security Expertrole
- Data Analystrole
- Data Engineerrole
- Full Stack Developerrole
- Java Backend Developerrole
- Java SDETrole
- Kotlin Backend Developerrole
- MLOps Engineerrole
- Machine Learning Engineerrole
- PostgreSQL DBArole
- QA Engineerrole
- SQLskill
questions
page 2 of 2A nightly job suddenly fails with "more than one row returned by a subquery" — how do you fix it?
basics
~20 sThe query did not change; the data did. A lookup assumed to be unique gained a duplicate. Isolate the subquery, find the duplicated key, then pick the intended semantics — set membership, a tighter unique predicate, or a deliberate aggregate — rather than silencing the error.
Using a recursive CTE over a multi-level bill of materials, how do you total each component's required quantity?
basics
~20 sMultiply quantities down each path inside the recursive member — the child's quantity times the parent row's accumulated quantity — then GROUP BY component and SUM in the outer query, so a component reached by several paths is totalled correctly.
How would you refactor a query with four levels of nested subqueries into stacked CTEs?
basics
~20 sWork from the innermost subquery outward: lift each level into its own named CTE in the WITH list, in dependency order, each selecting from the previous one. The result set must be unchanged; only the shape and the names are new.
If a WITH clause defines a name that also exists as a table, which one does the query read?
basics
~20 sThe CTE wins inside that statement: references in the main query resolve to the WITH definition, not the base table or view of the same name. The shadowing ends with the statement, so the next statement sees the schema object again.
What do EXISTS, IN, ANY and ALL return when the subquery returns no rows?
basics
~10 sOver an empty subquery: EXISTS is FALSE and NOT EXISTS TRUE; IN and any ANY predicate are FALSE; NOT IN and any ALL predicate are TRUE, vacuously. None of them is UNKNOWN.
What is not allowed inside the recursive member of a WITH RECURSIVE query?
basics
~20 sThe recursive member may reference the CTE's own name only once, in its FROM clause, and not inside a subquery. Aggregates, window functions, GROUP BY, HAVING and DISTINCT over the recursive reference are rejected, as is putting it on the null-supplying side of an outer join.
What does the optional column list in WITH totals (mth, revenue) AS (...) do?
basics
~20 sIt renames the CTE's output columns positionally, so the CTE exposes those names instead of whatever the inner SELECT produced. The list must name exactly as many columns as the query returns, or the statement fails.
showing 31–37 of 37