Lessons
SELECT, FROM, and basic filtering
SELECT columns, FROM tables, WHERE conditions, comparison operators, and DISTINCT.
What to look for:
- SELECT specific columns vs SELECT * (avoid * in production)
- WHERE with =, >, <, >=, <=, <>
- DISTINCT to remove duplicate rows
- LIMIT and OFFSET for pagination
NULL handling and string matching
IS NULL, IS NOT NULL, LIKE, BETWEEN, IN, and why NULL = NULL is NULL not TRUE.
Prerequisite: Lesson 1
What to look for:
- NEVER use = NULL — always IS NULL
- LIKE: % is wildcard, _ is one character
- BETWEEN: inclusive on both ends
- IN: cleaner than multiple OR conditions
INNER JOIN and LEFT JOIN
Combining two tables with JOIN, understanding why rows disappear with INNER JOIN.
Prerequisite: Lessons 1-2
What to look for:
- INNER JOIN: only matching rows from BOTH tables
- LEFT JOIN: all rows from left table, NULL for unmatched right
- Table aliases: employees e JOIN departments d ON e.dept_id = d.id
- The confused-box: JOIN without qualifier = INNER JOIN
ORDER BY, sorting, and CASE
Sort results with ORDER BY, multiple sort keys, ASC/DESC, and conditional CASE expressions.
Prerequisite: Lesson 3
What to look for:
- ORDER BY multiple columns: salary DESC, name ASC
- CASE WHEN ... THEN ... ELSE ... END as a computed column
- COALESCE for NULL defaults
- NULLIF to avoid division by zero
Aggregate functions: COUNT, SUM, AVG
COUNT(*), COUNT(col), SUM, AVG, MIN, MAX — both with and without GROUP BY.
Prerequisite: Lesson 4
What to look for:
- COUNT(*) vs COUNT(column) — COUNT(*) includes NULLs
- AVG of an empty set returns NULL not 0
- Without GROUP BY: aggregate over all rows
- GROUP BY reduces rows to one per group
How to use this track: Each lesson links to the Codex SQL reference page. Use the reading level tabs. Use the "What to look for" bullets. Practice each query in a PostgreSQL session — try db-fiddle.com for a free browser SQL environment.
Track quiz
4 questions to check your understanding.
How do you correctly check if a column is NULL in SQL?
What does a LEFT JOIN return that an INNER JOIN does not?
What is the difference between WHERE and HAVING?
What does COUNT(*) count?