SQL dictionary

Important words in SQL, grouped by topic.

The relational model

Relational database

A database that stores data in tables and is queried with SQL. The idea comes from Edgar F. Codd's relational model (1970): store data as plain tables and let people describe what they want, not how to find it. PostgreSQL, MySQL, SQLite and DuckDB are all relational databases.

Relation

The formal name for a table, and the origin of the word "relational". A common misunderstanding is that "relational" refers to relationships between tables. It comes from the mathematical term relation: a set of rows that all have the same columns.

Schema

The structure of a database: which tables exist, what columns they have, their types and how they connect. The schema describes the shape of the data, not the data itself.

In PostgreSQL, "schema" also has a second meaning: a named group of tables inside a database, such as the default public schema.

Normalization

Organizing data into tables so that each fact is stored in one place. Instead of writing an author's name into every one of their books, books stores author_id, and the name lives once in authors. A typo is then fixed in one place. There are formal levels of normalization, called normal forms (first, second, third and so on), but the core idea is "store each fact once".

Denormalization

Storing the same data in more than one place on purpose, usually to make reads faster or simpler. A books table with an author_name column is denormalized. The trade-off is that changes must be made in every place the data appears. Downloaded datasets, such as CSV files, are often denormalized.

Structure

Data type

The kind of value a column holds, such as INTEGER, TEXT, DATE or BOOLEAN. Types decide which operations make sense: you can add integers and compare dates. The available types vary between databases, and SQLite is unusually lenient about them.

Primary key

A column (or set of columns) that identifies exactly one row. Two books can have the same title, but never the same id.

CREATE TABLE authors (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

Foreign key

A column that holds the primary key of a row in another table. books.author_id is a foreign key to authors.id: the value 3 means "this book was written by author 3, N.K. Jemisin". Foreign keys are how tables connect, and they're what you follow when you write a join.

One-to-one relationship

Each row in a table is connected to at most one row in another table, and the other way around. Say each author can have one biography, stored in its own table:

CREATE TABLE author_bios (
  author_id INTEGER UNIQUE REFERENCES authors(id),
  bio       TEXT
);

It's built like a one-to-many relationship, with a foreign key, plus a UNIQUE constraint so that no author can have two rows. One-to-one relationships are less common, because the columns could usually live in the same table. A separate table makes sense when the data is optional, large and rarely used, or needs different access rights.

One-to-many relationship

One row in a table is connected to many rows in another. One author has many books, but each book has one author. This is the most common kind of relationship, and it's built with a foreign key on the "many" side (books.author_id).

The other kinds are one-to-one and many-to-many.

Many-to-many relationship

Rows on both sides can be connected to many rows on the other side. Say books can have genres: one book can have several genres, and one genre covers many books. A single foreign key can't express that. Instead, you add a table in between, often called a junction table, where each row connects one book to one genre:

CREATE TABLE book_genres (
  book_id  INTEGER REFERENCES books(id),
  genre_id INTEGER REFERENCES genres(id)
);

Constraint

A rule the database enforces on the data, such as NOT NULL, UNIQUE, PRIMARY KEY or a foreign key. Constraints stop bad data from getting in, no matter which program or person writes it.

title TEXT NOT NULL   -- every book must have a title

Index

An extra data structure that lets the database find rows without reading the whole table, like the index at the back of a book. Indexes speed up reads, but they take up space and slow down writes slightly.

CREATE INDEX books_year_idx ON books (year);

Queries

Declarative vs. imperative

Two ways of programming. In imperative code, you write the steps the computer should follow, such as a loop that checks each book. In declarative code, you describe the result you want and leave the steps to the system. SQL is declarative:

SELECT title FROM books WHERE year < 2015;

There's no loop. The database decides how to produce the answer.

Clause

One part of a query, starting with a keyword: the SELECT clause, the FROM clause, the WHERE clause and so on. Knowing the word makes it easier to talk about queries and read documentation.

Predicate

A condition that is true, false or unknown for each row, such as year < 2015. WHERE keeps the rows where the predicate is true.

Execution order

The order in which the database logically evaluates a query's clauses. It differs from the order you write them in:

  1. FROM and joins
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
  7. LIMIT

This explains why standard SQL doesn't let WHERE use a column name defined in SELECT. The order is logical: the database may do the actual work in another order, as long as the result is the same.

Join

Combining rows from two tables based on a condition, usually a foreign key:

SELECT books.title, authors.name
FROM books
JOIN authors ON authors.id = books.author_id;

An inner join (JOIN) keeps only rows that match on both sides. An outer join, such as LEFT JOIN, also keeps rows with no match. For example, authors LEFT JOIN books would include an author with no books.

Aggregate function

A function that turns many rows into one value, such as count(), sum(), avg(), min() and max(). Used with GROUP BY, it gives one value per group:

SELECT author_id, count(*) AS books
FROM books
GROUP BY author_id;

This returns 2 books for author 1, 1 for author 2 and 3 for author 3.

Subquery

A query inside another query. It works because every query returns a table. Which authors published something before 2015?

SELECT name
FROM authors
WHERE id IN (SELECT author_id FROM books WHERE year < 2015);

This returns Ursula K. Le Guin and Emily St. John Mandel.

CTE (common table expression)

A named subquery written at the start of a query with WITH. CTEs make large queries readable, because you can build them step by step with names, much like variables:

WITH before_2015 AS (
  SELECT * FROM books WHERE year < 2015
)
SELECT title FROM before_2015;

View

A saved query that you can use as if it were a table. The view doesn't store data. It runs its query each time you use it.

CREATE VIEW books_with_authors AS
SELECT books.title, books.year, authors.name
FROM books
JOIN authors ON authors.id = books.author_id;

After this, SELECT * FROM books_with_authors works like a query on a table.

Window function

A function that calculates something across related rows without collapsing them into one, unlike an aggregate function. For example, numbering each author's books in order of publication:

SELECT title, year,
       row_number() OVER (PARTITION BY author_id ORDER BY year) AS nr
FROM books;

The Fifth Season gets 1, The Obelisk Gate 2 and The Stone Sky 3. Window functions are often seen as the step from intermediate to advanced SQL.

Values

NULL

A marker for a missing or unknown value. NULL isn't zero or an empty string. Comparing anything to NULL gives "unknown" instead of true or false, which is called three-valued logic. That's why you test for it with IS NULL, not = NULL. Aggregate functions like avg() skip NULL values.

Changing data

Transaction

A group of changes that the database treats as one unit: either all of them happen, or none of them do.

This matters because some tasks need more than one change. Say the database also keeps track of loans. Lending out a book then takes two changes: adding a row to loans, and marking the book as lent out. If something goes wrong between the two, for example the program crashes, you end up with a book marked as lent out and no record of who has it.

A transaction prevents that:

BEGIN;
INSERT INTO loans (book_id, borrower) VALUES (3, 'Kari');
UPDATE books SET lent_out = true WHERE id = 3;
COMMIT;

BEGIN starts the transaction, and COMMIT makes both changes permanent at the same time. If anything fails before COMMIT, the database undoes the changes already made, so you never end up with only one of them. You can also undo everything yourself with ROLLBACK instead of COMMIT.

The guarantees a transaction gives are often summed up as ACID.

SQL as a language

DDL and DML

Two parts of SQL. Data Definition Language defines structure: CREATE, ALTER, DROP. Data Manipulation Language works with the data itself: SELECT, INSERT, UPDATE, DELETE. Documentation and tools often use these terms.

Dialect

A database's own version of SQL. The core (SELECT, FROM, WHERE, JOIN) is shared, but each database adds extras and differs in the details. One example is ILIKE (case-insensitive text matching), which DuckDB and PostgreSQL support but SQLite doesn't.

Query plan

The concrete sequence of steps the database chooses to run a query, worked out by the query planner (also called the optimizer). The planner considers available indexes and what it knows about the data, and picks the cheapest plan it can find. In PostgreSQL, MySQL and DuckDB, EXPLAIN shows the plan. In SQLite, the command is EXPLAIN QUERY PLAN.