pbPassingBI
/
sql/flashcards/sql-cards-subqueries

Subqueries & CTEs — flashcards

One card per lesson — 4 in this module. Recall the key points, then check. 4 cards. Click a card to flip, or use the arrow keys and space bar.

  1. Scalar and column subqueries — what are the key points?

    An uncorrelated subquery runs once; a correlated one runs per outer row · A correlated subquery in SELECT is often better written as a join · EXISTS short-circuits, so it stays cheap even when correlated Read the lesson

  2. Derived tables — what are the key points?

    A derived table is a subquery in FROM and usually needs an alias · Pre-aggregating in a derived table prevents join fan-out · Beyond two levels of nesting, use CTEs instead Read the lesson

  3. Common table expressions — what are the key points?

    CTEs turn inside-out nesting into readable named steps · A recursive CTE needs an anchor, UNION ALL, and a terminating condition · CTEs are for readability — they are not inherently faster Read the lesson

  4. EXISTS versus IN — what are the key points?

    NOT IN returns nothing if the subquery yields a NULL — use NOT EXISTS · EXISTS short-circuits on the first match and never multiplies rows · Use a join when you need columns from the other table Read the lesson