SQL across dialects

1. Rows, columns, and keys

A table represents one kind of thing. A row records one instance. A primary key identifies it; a name alone may not be unique. Store money as integer cents in this example to avoid binary floating-point rounding. NULL represents a missing value, not zero or an empty string.

CREATE TABLE expenses (
  id INTEGER PRIMARY KEY,
  category VARCHAR(30) NOT NULL,
  cents INTEGER NOT NULL CHECK (cents >= 0)
);
INSERT INTO expenses (id, category, cents) VALUES (1, 'food', 1200);
INSERT INTO expenses (id, category, cents) VALUES (2, 'taxi', 1800);
INSERT INTO expenses (id, category, cents) VALUES (3, 'food', 400);
SELECT id, category, cents FROM expenses ORDER BY id;

What to expect

Three rows: food 1200, taxi 1800, food 400, with IDs 1, 2, and 3.

The primary key already identifies a row. Use a different ID for a new expense; UPDATE changes an existing one.

Watch for: Database behavior depends on engine and version. This setup is tested in SQLite; identity generation differs elsewhere, so the fixture supplies IDs explicitly.

2. Select, filter, and sort

SELECT chooses columns, WHERE filters individual rows, and ORDER BY defines output order. Without ORDER BY, do not rely on the order rows happen to appear. Parameterize values in application code instead of building SQL with string concatenation.

SELECT category, cents
FROM expenses
WHERE cents >= 1000
ORDER BY cents DESC, id;

What to expect

taxi 1800, then food 1200.

SELECT id, cents FROM expenses
WHERE category = 'food' AND cents < 1000
ORDER BY id;
-- id 3, cents 400

Watch for: Use IS NULL to find missing values. = NULL evaluates to unknown rather than true.

3. Group and summarize

SUM combines numeric values. GROUP BY forms one group for each distinct category. WHERE runs before grouping; HAVING filters the resulting groups. Keep selected non-aggregate columns in GROUP BY for portable, meaningful queries.

SELECT category, SUM(cents) AS total_cents
FROM expenses
GROUP BY category
HAVING SUM(cents) >= 1500
ORDER BY total_cents DESC, category;

What to expect

taxi 1800, then food 1600.

Add COUNT(*) AS expense_count to SELECT. food has 2 rows; taxi has 1. COUNT(column) excludes NULL values, while COUNT(*) counts rows.

Watch for: A join can multiply rows before aggregation. Count and inspect the join result before trusting a total.

4. Change data and undo it

A transaction groups changes. In SQLite, BEGIN starts one; ROLLBACK discards its changes and COMMIT retains them. First SELECT the intended rows, then use the same condition in UPDATE. A transaction is not a replacement for a backup.

BEGIN;
UPDATE expenses SET cents = 1900 WHERE id = 2;
SELECT cents FROM expenses WHERE id = 2;
ROLLBACK;
SELECT cents FROM expenses WHERE id = 2;

What to expect

1900 inside the transaction, then 1800 after rollback.

Use WHERE id = 3 in both statements. The final query must return 400. Do not run an unqualified UPDATE: it changes every row.

Watch for: DDL transaction and autocommit behavior varies by engine. Do not assume an ALTER TABLE can be rolled back everywhere.

5. Join related rows without losing or multiplying the wrong things

Before this lesson: Complete rows/keys, filtering and aggregation (lessons 1–3). Use a fresh disposable SQLite database, separate from learning.db; for the CLI, sqlite3 :memory: creates one that disappears on exit. Outcome: predict join row counts, retain unmatched parents, and summarize children correctly. This fixture uses new table names and explicit IDs; it needs no prior tables.

Imagine field-note notebooks and entries as separate tables. One notebook may contain several entries; each entry has one notebook_id. Joining on that ID attaches notebook information to each matching entry. A relationship is not a promise of one output row per notebook: three entries can produce three joined rows. This number of matches is cardinality. Predict it before calculating totals.

Our fixture has three notebooks: Travel with two entries, Sound with one, and Languages with none. A fourth entry has no notebook yet (NULL). The first query is an INNER JOIN: it returns only matching pairs. The second is a LEFT JOIN: it keeps every left-side notebook, filling right-side columns with NULL when no entry matches. It does not retain an unmatched right-side entry. Aliases n and e shorten qualified column names; the condition after ON tells the database how to match the two sets.

The third query counts child IDs, not joined rows. COUNT(e.id) ignores the NULL placeholder for Languages and returns zero; COUNT(*) would count that placeholder as one row. COALESCE turns a missing SUM into zero for this report, where no entries really does mean zero recorded words. Do not generally replace unknown measurements with zero. GROUP BY includes both selected notebook fields, and ORDER BY makes the report deterministic.

The fourth query puts e.words >= 100 in ON. It asks for entries meeting the threshold while retaining every notebook. Moving that condition to WHERE asks the database to discard joined rows not meeting it, including NULL placeholders. That removes Languages and Sound. Filtering before outer-join preservation and filtering afterward answer different questions. These are conceptual semantics, not a claim about the optimizer's physical execution order.

-- Fresh disposable database only; run this setup once.
CREATE TABLE notebooks (
  id INTEGER PRIMARY KEY,
  title VARCHAR(30) NOT NULL
);
CREATE TABLE entries (
  id INTEGER PRIMARY KEY,
  notebook_id INTEGER,
  words INTEGER NOT NULL CHECK (words >= 0)
);
INSERT INTO notebooks (id, title) VALUES (1, 'Travel');
INSERT INTO notebooks (id, title) VALUES (2, 'Sound');
INSERT INTO notebooks (id, title) VALUES (3, 'Languages');
INSERT INTO entries (id, notebook_id, words) VALUES (10, 1, 100);
INSERT INTO entries (id, notebook_id, words) VALUES (11, 1, 250);
INSERT INTO entries (id, notebook_id, words) VALUES (12, 2, 80);
INSERT INTO entries (id, notebook_id, words) VALUES (13, NULL, 40);

-- 1: Only matched pairs.
SELECT n.id, n.title, e.id AS entry_id, e.words
FROM notebooks n INNER JOIN entries e ON e.notebook_id = n.id
ORDER BY n.id, e.id;

-- 2: Retain notebooks with no entries.
SELECT n.id, n.title, e.id AS entry_id, e.words
FROM notebooks n LEFT JOIN entries e ON e.notebook_id = n.id
ORDER BY n.id, e.id;

-- 3: One summary per notebook.
SELECT n.id, n.title, COUNT(e.id) AS entry_count,
       COALESCE(SUM(e.words), 0) AS total_words
FROM notebooks n LEFT JOIN entries e ON e.notebook_id = n.id
GROUP BY n.id, n.title
ORDER BY n.id;

-- 4: Keep all notebooks, match only longer entries.
SELECT n.id, n.title, e.id AS entry_id
FROM notebooks n LEFT JOIN entries e
  ON e.notebook_id = n.id AND e.words >= 100
ORDER BY n.id, e.id;

Run it

Paste the setup and four queries into sqlite3 :memory:. A GUI can also create a temporary lab database; verify that its connection is not a work database. Some clients display NULL as a blank cell. The fixture omits a foreign-key constraint deliberately: the next unit will teach enforcing relationships and how engine settings affect it. Do not mistake a matching column name for an enforced constraint.

What to expect

Query 1: three rows, (1, Travel, 10, 100), (1, Travel, 11, 250), (2, Sound, 12, 80). Query 2 adds (3, Languages, NULL, NULL), making four rows. Entry 13 appears in neither result because it does not match a notebook. Query 3: (1, Travel, 2, 350), (2, Sound, 1, 80), (3, Languages, 0, 0). Query 4: (1, Travel, 10), (1, Travel, 11), (2, Sound, NULL), (3, Languages, NULL). The inner query's three rows happen to equal the notebook count: that coincidence does not mean one row per notebook.

-- (a) Test a right-side column that cannot be NULL on a match.
SELECT n.id, n.title
FROM notebooks n LEFT JOIN entries e ON e.notebook_id = n.id
WHERE e.id IS NULL
ORDER BY n.id;
-- 3, Languages. Testing a nullable child field could misclassify a match.

-- (b) Make entries the preserved LEFT side.
SELECT e.id, n.title, e.words
FROM entries e LEFT JOIN notebooks n ON n.id = e.notebook_id
ORDER BY e.id;
-- 10 Travel 100; 11 Travel 250; 12 Sound 80; 13 NULL 40.

-- (c) A deliberately different question:
SELECT n.id, n.title, e.id AS entry_id
FROM notebooks n LEFT JOIN entries e ON e.notebook_id = n.id
WHERE e.words >= 100
ORDER BY n.id, e.id;
-- Only Travel's two rows. Sound's 80 is rejected; Languages'
-- NULL comparison is unknown, not true, so it is also rejected.

-- COUNT(*) gives Languages 1, because LEFT JOIN produced a
-- placeholder row. COUNT(e.id) gives 0 actual entries.
-- DISTINCT removes duplicate output values, not faulty matches;
-- it can discard legitimately separate records.
-- Independent checkpoint: add notebook 4 with two new entries,
-- predict all four result sets, and explain their row counts.

Watch for: Use IDs rather than names to join entities; titles may repeat. Forgetting the relationship can produce every combination (3 notebooks × 4 entries = 12 rows here). Joining two independent one-to-many child tables can multiply both sets before SUM; inspect detail rows and aggregate each child set at the intended grain first. The fixture uses conservative INNER/LEFT JOIN, ON, explicit IDs and single-row INSERT syntax for easy porting. It is not certified across all eleven engines: DDL, constraints, clients and NULL display still need verification. RIGHT/FULL joins and vendor-specific shorthand are outside this unit.

Lesson references

• PostgreSQL join tutorial: matching and outer joins (checked 14 September 2026) ↗
• SQLite SELECT: join processing, NULL extension and WHERE ↗
• SQLite aggregates: count and sum ↗