Cheat sheet
SQL
Joins, aggregation, indexing, and the query questions that show up in most data rounds.
Joins
INNER
Only matching rows in both tables.
LEFT
All left rows + matches; NULLs where no match.
FULL OUTER
All rows from both; NULLs on either side where unmatched.
CROSS
Cartesian product — every combination.
Aggregation
GROUP BY
SELECT dept, COUNT(*) FROM emp GROUP BY deptHAVING vs WHERE
WHERE filters rows before grouping; HAVING filters groups after.
Window fn
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY pay DESC)Aggregate without collapsing rows.
Performance
Index
Speeds reads on filtered/joined columns; slows writes.
EXPLAIN
Read the query plan — watch for full table scans.
N+1
Avoid per-row queries in a loop; join or batch instead.
SELECT *
Fetch only needed columns — less I/O, index-only scans possible.
Query order
Logical order
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMITWhy you can't use a SELECT alias in WHERE.
NULL
NULL = NULL is unknown; use IS NULL / IS NOT NULL.
DISTINCT
De-dupes rows; can be costly on large sets.
Aevrofy · aevrofy.com — free interview prep