Foundations reference

SQL Reference

Querying, joining, grouping and designing relational data, with the evaluation order that explains most of SQL's error messages.

Start SQL & DatabasesPractise with today's challenge

The relational model

Four ideas carry most of SQL. Everything else is syntax on top of them.

Tables, rows and columns

A table holds one kind of thing. A row is one of them. A column is one fact about it, of one type, for every row.

CREATE TABLE books (
  id     INTEGER PRIMARY KEY,
  title  TEXT    NOT NULL,
  year   INTEGER,
  author_id INTEGER REFERENCES authors(id)
);
  • One table, one kind of thing. A table holding both books and authors is the first thing to fix.
  • A column has one type and one meaning. A column that holds a date for some rows and a note for others will hurt later.
  • Rows have no inherent order. Without ORDER BY the database may return them in any order it likes, and may change its mind.

Keys

A primary key identifies a row. A foreign key points at one.

-- identifies a book
id INTEGER PRIMARY KEY

-- points at an author
author_id INTEGER REFERENCES authors(id)
  • A primary key is unique and never null. That is what makes a row addressable.
  • Prefer a key with no meaning, such as an auto-assigned id. Anything meaningful eventually changes.
  • A foreign key is what makes a join possible and what stops a book pointing at an author who does not exist.

NULL is not a value

NULL means unknown. It is not zero, not an empty string, and it is not equal to anything, including itself.

SELECT NULL = NULL;      -- NULL, not true
SELECT NULL <> 'x';      -- NULL, not true

-- so comparisons need IS
WHERE year IS NULL
WHERE year IS NOT NULL

-- and arithmetic spreads it
SELECT 10 + NULL;        -- NULL
  • WHERE year = NULL matches nothing, ever. It is the single most common SQL mistake.
  • COUNT(*) counts rows. COUNT(column) skips NULLs, and the difference is often the bug.
  • AVG and SUM ignore NULLs, which means an average can be over fewer rows than you think.
  • COALESCE(value, fallback) substitutes something when a value is NULL.

Set thinking

SQL describes the rows you want, not the steps to collect them. This is the hardest adjustment for someone arriving from loops.

  • There is no loop. A WHERE clause applies to every row at once.
  • Do not think 'for each book, look up the author'. Think 'the set of books joined to their authors'.
  • That difference is also the performance difference: the database can run a set operation in one pass.

SELECT

The shape of a query

Six clauses, always written in this order.

SELECT   title, year
FROM     books
WHERE    year > 1970
GROUP BY author_id
HAVING   COUNT(*) > 1
ORDER BY year DESC
LIMIT    10;
  • Written in that order, always. Swapping WHERE and ORDER BY is a syntax error, not a preference.
  • Only SELECT and FROM are required.

The order it actually runs

Not the order it is written. Knowing this explains most of SQL's more confusing errors.

FROM      which tables
WHERE     which rows          <- before grouping
GROUP BY  collapse into groups
HAVING    which groups        <- after grouping
SELECT    which columns       <- aliases created HERE
ORDER BY  arrange
LIMIT     cut
  • WHERE runs before grouping, so it cannot see an aggregate. That is what HAVING is for.
  • SELECT runs near the end, so a WHERE clause cannot use a column alias you defined in SELECT.
  • ORDER BY runs after SELECT, so it CAN use an alias. That asymmetry surprises everyone once.

Filtering

WHERE keeps rows that make the condition true.

WHERE year > 1970
WHERE author = 'Butler'
WHERE year BETWEEN 1970 AND 1989
WHERE author IN ('Butler', 'Morrison')
WHERE title LIKE 'The %'
WHERE year IS NULL
WHERE year > 1970 AND published = 1
  • Strings use single quotes. Double quotes mean an identifier in standard SQL.
  • LIKE uses % for any run of characters and _ for exactly one.
  • Rows where the condition is NULL are not kept: only true survives, not unknown.
  • AND binds tighter than OR. Use brackets when you mix them, or the result will not be what you meant.

Sorting and limiting

ORDER BY arranges; LIMIT cuts.

ORDER BY year DESC, title ASC
LIMIT 10
LIMIT 10 OFFSET 20
  • Without ORDER BY there is no guaranteed order, so LIMIT without it returns an arbitrary ten rows.
  • That is also why paging with LIMIT and OFFSET needs a stable, unique sort, or rows repeat between pages.
  • NULLs sort together, at one end. Which end differs by database.

DISTINCT

Removes duplicate rows from the result, considering every selected column.

SELECT DISTINCT author FROM books;

-- careful: this is distinct PAIRS, so it may not reduce anything
SELECT DISTINCT author, title FROM books;
  • Reaching for DISTINCT to fix unexpected duplicates usually hides a join problem rather than solving it.
  • If a join multiplied your rows, DISTINCT makes the count look right while the sums stay wrong.

Joins

Joining is where relational databases earn their name, and where most of the real mistakes happen.

INNER JOIN

Rows that have a match on both sides. The default, and what JOIN alone means.

SELECT b.title, a.name
FROM   books b
JOIN   authors a ON a.id = b.author_id;
  • A book with no author, or an author_id pointing nowhere, disappears from the result entirely.
  • That silent disappearance is the most common cause of a count that is lower than expected.
  • Aliases (b, a) keep the query readable once there is more than one table.

LEFT JOIN

Every row from the left table, with the right side filled in where it matches and NULL where it does not.

SELECT a.name, COUNT(b.id) AS books
FROM   authors a
LEFT JOIN books b ON b.author_id = a.id
GROUP BY a.id;
  • Use it when the absence is part of the answer: authors with no books, orders with no payment.
  • COUNT(b.id) counts matches and gives 0 for none. COUNT(*) would count the NULL row and give 1.
  • A condition on the right table in WHERE turns a LEFT JOIN back into an inner one. Put it in the ON clause instead.

ON versus WHERE

For an inner join they behave the same. For an outer join they absolutely do not, and this is the classic trap.

-- keeps every author
LEFT JOIN books b
  ON b.author_id = a.id AND b.year > 1980

-- silently drops authors with no 1980s book
LEFT JOIN books b ON b.author_id = a.id
WHERE b.year > 1980
  • ON decides what counts as a match. WHERE filters the rows that came out.
  • A NULL from an unmatched row fails any WHERE comparison, which is what removes it.

Joins multiply rows

If one row on the left matches three on the right, you get three rows. Counts and sums change accordingly.

  • This is correct behaviour and it surprises people every time.
  • A book with three tags appears three times, so SUM(price) is now triple the real total.
  • Aggregate before joining, or count DISTINCT the thing you actually mean to count.
  • Missing an ON clause entirely gives you every combination of both tables, which for two large tables can hang the query.

Grouping and aggregates

The aggregate functions

Collapse many rows into one value.

COUNT(*)          rows, including those with NULLs
COUNT(column)     rows where that column is not NULL
COUNT(DISTINCT c) distinct non-null values
SUM(column)
AVG(column)
MIN(column) / MAX(column)
  • COUNT(*) and COUNT(column) differ exactly when the column has NULLs, and that difference is frequently the bug.
  • AVG ignores NULLs, so it averages over fewer rows than the table has. Sometimes right, sometimes badly wrong.

GROUP BY

Collapse rows into one row per distinct value, then aggregate each group.

SELECT   author_id, COUNT(*) AS n, MIN(year) AS first
FROM     books
GROUP BY author_id;
  • Every selected column must either be grouped by or wrapped in an aggregate.
  • PostgreSQL enforces that strictly. SQLite and older MySQL will silently pick an arbitrary row, which is worse: a plausible wrong answer instead of an error.
  • Group by the id rather than the name where you can. Two people can share a name.

HAVING

Filters groups, after grouping. WHERE filters rows, before it.

SELECT   author_id, COUNT(*) AS n
FROM     books
WHERE    year > 1970        -- which rows go into the groups
GROUP BY author_id
HAVING   COUNT(*) > 2;      -- which groups survive
  • WHERE cannot use an aggregate, because it runs before there are any groups to aggregate.
  • Filtering with WHERE where you can is cheaper: fewer rows enter the grouping.

Subqueries and CTEs

Subqueries

A query used inside another, as a value, a list, or a table.

-- as a single value
WHERE year > (SELECT AVG(year) FROM books)

-- as a list
WHERE author_id IN (SELECT id FROM authors WHERE country = 'US')

-- as a table
FROM (SELECT author_id, COUNT(*) AS n FROM books GROUP BY author_id) t
  • NOT IN with a subquery that can return NULL returns nothing at all. Use NOT EXISTS instead.
  • A subquery referring to the outer query runs once per row, which is the SQL version of the N+1 problem.

Common table expressions

A named subquery written before the query, which usually reads far better.

WITH counts AS (
  SELECT author_id, COUNT(*) AS n
  FROM   books
  GROUP BY author_id
)
SELECT a.name, c.n
FROM   counts c
JOIN   authors a ON a.id = c.author_id
WHERE  c.n > 2;
  • Same result as a nested subquery, read top to bottom instead of inside out.
  • Several CTEs can be chained with commas, each able to use the ones before it.
  • A recursive CTE with no stopping condition runs forever. Always give it a termination.

Changing data

INSERT, UPDATE, DELETE

The three statements that change rows.

INSERT INTO books (title, year) VALUES ('Kindred', 1979);

INSERT INTO books (title, year)
VALUES ('Wild Seed', 1980), ('Beloved', 1987);

UPDATE books SET published = 1 WHERE year < 1990;

DELETE FROM books WHERE year IS NULL;
  • UPDATE and DELETE without a WHERE clause change every row in the table. There is no confirmation.
  • Run the WHERE as a SELECT first and look at what comes back. Every experienced person does this.
  • Name your columns in an INSERT. Relying on column order breaks the day someone adds a column.

Transactions

Several statements that either all happen or none do.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- or, if something is wrong
ROLLBACK;
  • The classic example is a transfer: taking money out and putting it in must not be separable.
  • Anything that would leave the data half-changed belongs in a transaction.
  • A transaction left open holds locks and blocks other work. Commit or roll back promptly.

Schema design

Normalisation, practically

Store each fact once. Most of the theory reduces to that, and most real damage comes from ignoring it.

-- author name repeated on every book
books(id, title, author_name, author_country)

-- stored once, referenced
authors(id, name, country)
books(id, title, author_id)
  • Repeated data becomes inconsistent data. Correct a spelling in one row and the others are now wrong.
  • A comma-separated list in a column cannot be joined, indexed or counted properly. Use a second table.
  • Deliberate denormalisation for speed is legitimate, once you have measured. Doing it by accident is not.

Many-to-many needs a third table

A book has many tags and a tag has many books. Neither side can hold the relationship, so it gets its own table.

CREATE TABLE book_tags (
  book_id INTEGER REFERENCES books(id),
  tag_id  INTEGER REFERENCES tags(id),
  PRIMARY KEY (book_id, tag_id)
);
  • The pair of columns is the primary key, which also prevents the same tag being added twice.
  • This is what an ORM's ManyToManyField creates for you.

Constraints

Rules the database enforces, so bad data cannot get in even by accident.

NOT NULL
UNIQUE
PRIMARY KEY
REFERENCES other(id)
CHECK (year > 0)
DEFAULT 0
  • A constraint in the database holds regardless of which application, script or person is writing.
  • Validation in your application is for a helpful error message. The constraint is what makes it true.
  • SQLite does not enforce foreign keys unless you switch it on: PRAGMA foreign_keys = ON.

Indexes and query plans

What an index is

A sorted structure the database can search instead of reading every row. The same idea as an index in a book.

CREATE INDEX idx_books_author ON books(author_id);
CREATE INDEX idx_books_year_title ON books(year, title);
  • Index the columns you filter and join on, not every column.
  • Every index makes writes slower and takes space, so they are a trade rather than a free win.
  • A primary key is indexed automatically. A foreign key often is not, and that is a common missing index.
  • Column order matters in a multi-column index: it helps queries filtering on the first column, or the first and second, but not the second alone.

Reading a query plan

The database will tell you how it intends to answer a query. Ask before guessing.

EXPLAIN QUERY PLAN
SELECT * FROM books WHERE author_id = 3;

-- SCAN books           <- reading every row
-- SEARCH books USING INDEX idx_books_author  <- using the index
  • SCAN on a large table in a query you run often is the thing to fix.
  • PostgreSQL and MySQL use EXPLAIN and EXPLAIN ANALYZE; the idea is the same.
  • Wrapping an indexed column in a function usually disables the index: WHERE lower(name) = 'x' cannot use an index on name.

Where the ORM leaks

An ORM writes SQL for you. Understanding what it writes is the difference between using one and being surprised by one.

The N+1 problem, in SQL terms

The ORM issues one query for the list, then one more per row when you touch a related object.

-- what you wanted
SELECT b.*, a.name
FROM   books b JOIN authors a ON a.id = b.author_id;

-- what a loop over books.author produces
SELECT * FROM books;
SELECT * FROM authors WHERE id = 1;
SELECT * FROM authors WHERE id = 2;
-- ... once per book
  • Django's select_related is a JOIN. prefetch_related is a second query matched up in Python.
  • Seeing the SQL is what makes the distinction obvious rather than something to memorise.

When to drop to SQL

The ORM covers most cases well. These are the ones where it stops helping.

  • Reporting queries with several aggregates and joins, where the ORM version becomes unreadable.
  • Window functions and set operations, where support is limited or awkward.
  • Bulk updates: one UPDATE statement beats loading ten thousand objects to save each one.
  • Anything you need to see the plan for. You cannot optimise what you cannot read.
  • Always parameterise. String-formatting a value into SQL is how injection gets reintroduced.

Common errors, and what they mean

no such column: x

A typo, a missing table in FROM, or an alias used where it is not yet visible.

  • A column alias defined in SELECT cannot be used in WHERE, because WHERE runs first.
  • It can be used in ORDER BY, which runs after SELECT.

A WHERE on NULL matches nothing

= and <> against NULL are never true.

WHERE year = NULL      -- always empty
WHERE year IS NULL     -- correct

The count is too high after a join

The join multiplied rows, which is correct behaviour.

  • One row matching three gives three rows, so SUM triples.
  • Aggregate before joining, or COUNT(DISTINCT id) the thing you actually mean.
  • Adding DISTINCT makes the count look right while leaving the sums wrong.

The count is too low after a join

An inner join dropped rows with no match.

  • Use LEFT JOIN when absence is part of the answer, and check for NULL foreign keys.

misuse of aggregate function

An aggregate in WHERE, which runs before grouping.

-- wrong
WHERE COUNT(*) > 2
-- right
HAVING COUNT(*) > 2

The query is suddenly very slow

Usually a missing index, or an index disabled by a function.

  • Run EXPLAIN QUERY PLAN and look for SCAN on a large table.
  • WHERE lower(name) = 'x' cannot use a plain index on name.

UPDATE changed every row

The WHERE clause was missing.

  • Write the WHERE as a SELECT first and look at what comes back.
  • Work inside a transaction so there is something to roll back to.

Want to learn how to build with this?

A reference tells you what exists. The course teaches you when to reach for it, and has you build something real while you learn.