Queries nested inside other queries.
7 notes tagged
GROUP BY rules, HAVING versus WHERE, the NULL behaviour of aggregates, and the subquery questions that separate confident answers from vague ones.
Query a hierarchy of unknown depth, generate a series of rows, or follow a chain of references - all with one self referencing CTE.
The same question answered four ways, with a straight comparison of readability, NULL safety and cost - and a rule for choosing between them.
Subqueries that reference the outer row and run once per row. Learn EXISTS and NOT EXISTS, why they are NULL safe, and when correlation costs too much...
Subqueries that return a list of values, and the three operators that consume them - including the NULL trap that makes NOT IN return nothing.
A query inside a query. Learn where a subquery may appear, what each position requires it to return, and the error a scalar subquery raises when it re...
Why a join multiplies rows, how to detect it, and the anti join patterns that answer "which rows have no match at all".