The three-word skeleton of every SQL query. SELECT = what columns, FROM = which table, WHERE = which rows. The key insight: column names are structural references — they don't get quotes. Values like 'Open' do get quotes because they're actual data.

Chain multiple conditions together. AND = both must be true. OR = at least one must be true. != = not equal. Critical edge case learned: OR with two conditions covering all possible values always returns all rows — verified against TCS-style ticket data.

Summarise rows instead of retrieving them. COUNT(*) counts rows. AVG() averages a numeric column. NULL values are excluded from AVG automatically — NULL is not zero, it's the absence of a value, which breaks sorting and math differently.

GROUP BY splits your aggregation into buckets by category. HAVING then filters those buckets — you can't use WHERE to filter on aggregated results because WHERE runs before the groups exist. HAVING runs after grouping.

ORDER BY sorts results ASC (smallest first) or DESC (largest first). LIMIT caps the number of rows returned. Combined, they answer "top N" questions — but with a critical flaw discovered independently.

Two real-world data bugs caught without prompting: (1) NULL values float unpredictably when sorted — NULL is not zero, it's the absence of a value. (2) Sorting text priority levels ('High','Medium','Low') alphabetically gives wrong ordering — 'Low' sorts before 'Medium' alphabetically. Fix: assign numeric values via CASE before sorting.

Returns only rows where the key exists in BOTH tables. If a row has no match on either side, it is excluded entirely. The strictest join — no orphans allowed.

Returns ALL rows from the left table, plus matching rows from the right. Where there's no match on the right, NULL fills the gap. Most common join in analytics — you want to keep all your base records even when some have no match.

RIGHT JOIN mirrors LEFT JOIN but keeps all right-table rows. FULL OUTER JOIN keeps everything from both sides, filling NULLs where matches are missing. Both are rare in practice — most teams rewrite as LEFT JOINs for readability.

All four JOIN types documented as visual diagrams showing which rows survive each join. Committed to the analytics-engineer-journey repo with explanatory README collapsibles.

Filter rows based on a calculation that requires a separate query. Classic use case: find all tickets with resolution time above average — you can't use WHERE resolution_hours > AVG(resolution_hours) directly because aggregate functions can't live in WHERE. The subquery runs the AVG first, then WHERE compares against it.

Treat the result of a query as a temporary table and query it. Useful when you need to aggregate already-aggregated data — something a single GROUP BY can't do. The inner query runs first, produces a virtual table, and the outer query reads from it.

Simple CASE compares one column to fixed values. Searched CASE evaluates full conditions. The fix for the text-sort flaw from Lesson 01 — assign numeric values to priority levels before sorting so High=1, Medium=2, Low=3 and ORDER BY works correctly.

Returns the first non-NULL value from a list of arguments. Standard way to replace NULLs with a default value in output — critical for clean reporting and AI training data where NULLs can corrupt downstream processes.