Understanding SQL as a language

You describe the result, not the steps

Most programming languages are imperative: you write the steps, and the computer follows them. Here's how you'd find books published before 2015 in a list using Python:

result = []
for book in books:
    if book.year < 2015:
        result.append((book.title, book.year))

SQL is declarative. You describe the result you want and leave out the steps:

SELECT title, year
FROM books
WHERE year < 2015;

There's no loop, no result variable and no append. The database decides how to produce the answer. This is the division of labor at the heart of SQL: the language describes what you want, and the database is the engine that works out how. That's why the same query can run on very different databases. PostgreSQL and MySQL store data row by row, which suits applications that read and write a few rows at a time. DuckDB stores data column by column, which suits analysis that reads a few columns across millions of rows. Each produces the same answer to our query in its own way.

The databases don't speak exactly the same SQL, though. The core (SELECT, FROM, WHERE, JOIN) is shared, but each database adds its own extras and has small differences in the details. These variants are called dialects.

Tables in, a table out

SQL works on tables, and every query returns a table. Our query above takes the books table and produces a new, smaller table with two columns:

Because the result is a table, you can use a query anywhere SQL expects a table, including inside another query, where it's called a subquery:

SELECT title
FROM (SELECT title, year FROM books WHERE year < 2015) AS before_2015
WHERE year > 1970;

The inner query builds a table called before_2015, and the outer query reads from it as if it were stored in the database. This is how you build large SQL queries: by composing small ones, much like you compose functions in other languages.

Nested queries get hard to read as they grow, because you have to read them from the inside out. There's a way to write the same query from top to bottom instead. Use a CTE (common table expression) to give each step a name with WITH:

WITH before_2015 AS (
  SELECT title, year FROM books WHERE year < 2015
)
SELECT title
FROM before_2015
WHERE year > 1970;

The result is the same as before. The difference is in how you read it: first define before_2015, then use it, much like a variable.

One more property of tables: their rows have no built-in order. If you run the same query twice without ORDER BY, the database is allowed to return the rows in a different order. When order matters, you have to ask for it.

Clauses run in a different order than you write them

You write a query in the order SELECT, FROM, WHERE, ORDER BY. The database evaluates the clauses in a different order:

1. FROM: pick the table
2. WHERE: keep only the matching rows
3. SELECT: choose and compute the columns
4. ORDER BY: sort the result

This order can be tricky, because it explains errors that otherwise look strange. A query can refer to something that, at that step, doesn't exist yet. For example, a name you give a column in SELECT doesn't exist yet when WHERE runs.

How strict a database is about this varies. SQLite and DuckDB are lenient and often work out what you meant, while PostgreSQL follows the standard more strictly and gives you an error. The evaluation order is still worth knowing: it's how standard SQL works, and it explains those errors when you meet them. When you later learn GROUP BY and HAVING, they fit into the same sequence, between WHERE and SELECT.

NULL means "unknown"

Say we add a book whose publication year we don't know. Its year is NULL. NULL isn't zero or an empty string. It means "no known value", and SQL treats it differently from every other value:

SELECT title FROM books WHERE year = NULL;   -- returns no rows
SELECT title FROM books WHERE year IS NULL;  -- returns the book

The first query returns nothing because of how WHERE works. The condition after WHERE is called a predicate, and for each row it's true, false or unknown. Comparing anything to an unknown value gives an unknown result: is an unknown year equal to another unknown year? SQL's answer is "unknown". WHERE only keeps the rows where the predicate is true, so unknown rows are dropped. That's why NULL has its own operator, IS NULL.

The same rule has a less obvious consequence: WHERE year <> 1969 (not equal to 1969) also leaves out the book with the unknown year. This three-valued logic (true, false, unknown) is one of the features that makes SQL different from other languages, and it's worth understanding well before you write larger queries.