SQL — Confusions, Labs, Gotchas & Mock Interview
💡 Interview Tip
Goal: After this page, you should NEVER struggle with SQL concepts or interview questions again.
Approach: Clear up top confusions → run labs → see animations → know gotchas → practice mock interview.
Where to run labs: db-fiddle.com (PostgreSQL), DuckDB, or SQLite.
Memory Map
🧠 SQL MASTERY → CLEAR-JOIN-WINDOW
SQL MASTERYCLEAR-JOIN-WINDOW
──────────────────────────────
CConfusions (HAVING vs WHERE, JOIN types, UNION, RANK variants)
LLabs (run queries, see intermediate results)
EErrors / Gotchas (the ones that break pipelines)
AAnimations (visualize JOINs, window frames, GROUP BY)
RReadiness (mock interview + final checklist)
SECTION 0: TOP 8 SQL CONFUSIONS — Cleared Forever
💡 Interview Tip
Interviewers LOVE these. Get them right and you sound senior.
Confusion 1: HAVING vs WHERE
The Simple Answer:
WHEREfilters ROWS (before grouping)
HAVINGfilters GROUPS (after aggregation)
Why it matters: You CANNOT use aggregates in WHERE. You CANNOT filter non-aggregated columns in HAVING (well, you can, but WHERE is faster).
Visual:
📐 Architecture Diagram
┌────────────────────────────────────────────────────┐
│ Raw Table (orders) │
│ ───────────── │
│ cust_id │ amount │ status │
│ ────────────────────────── │
│ 1 │ 100 │ paid │
│ 1 │ 200 │ paid │
│ 2 │ 50 │ cancelled ← filter out (WHERE) │
│ 2 │ 300 │ paid │
└────────────────────────────────────────────────────┘
│
│ 1. WHERE status = 'paid' ← removes row-by-row
▼
┌────────────────────────────────────────────────────┐
│ cust_id │ amount │ status │
│ 1 │ 100 │ paid │
│ 1 │ 200 │ pa