SQL fundamentals

Sources: DDIA Ch. 2 (query languages) · Kimball (SQL for analytics)

Why SQL persists (DDIA)

SQL is a declarative language — you describe what you want, not how to scan or join. The optimizer picks an execution plan. This separation is why SQL survived decades of storage engine change.

Core topics

• Joins (inner, left, full), CTEs, subqueries vs temp tables
• Aggregations with edge cases (NULLs, empty groups)
• Query optimization: EXPLAIN, partition pruning, avoid functions on join keys

SQL order of execution

FROM → JOIN → WHERE → GROUP BY → HAVING → WINDOW → SELECT → DISTINCT → ORDER BY → LIMIT

Why it matters: You cannot filter on a window function alias in the same SELECT — wrap in a CTE first.

Relational vs other models (DDIA Ch. 2)

• Relational — best when data is structured, many-to-many joins, ad-hoc queries
• Document — nested one-to-many without joins; schema flexibility; harder cross-document joins
• Graph — many-to-many relationships are first-class; traversal queries

For analytics (Kimball), SQL over star schemas remains the default — BI tools expect it.

Optimization checklist

• Run EXPLAIN ANALYZE — seq scans, nested loops, disk spills
• Filter early on partition/cluster keys
• Broadcast small dimension tables when supported
• Avoid TRIM(col) in JOIN conditions — kills index use