SQL rewards precision more than most languages — a query with perfectly valid syntax can still return a confidently wrong answer, and the database will never warn you. This is a complete reference for the areas where that happens most: joins, NULL, aggregates, subqueries, and the query features built on top of them. Every example in this chapter uses the same two tables the code lab's live SQL runner uses, so you can paste any query here and run it for real:
CREATE TABLE Departments (
dept_id INTEGER PRIMARY KEY,
dept TEXT NOT NULL
);
CREATE TABLE Employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL,
salary INTEGER
);
INSERT INTO Departments (dept_id, dept) VALUES
(1, 'Sales'), (2, 'IT'), (3, 'HR'), (4, 'Finance');
INSERT INTO Employees (id, name, dept, salary) VALUES
(1, 'Alice', 'Sales', 50000),
(2, 'Bob', 'Sales', 60000),
(3, 'Carol', 'IT', 70000),
(4, 'Dave', 'IT', NULL),
(5, 'Eve', 'HR', 55000);
Notice two deliberate details baked into this data: Finance has zero employees, and Dave's salary is NULL. Nearly every subtle bug in this reference traces back to one of those two facts — work through it in order, or jump to the section you need from the sidebar.