How SQL works

Tables, rows, columns

A relational database stores data in tables, and a table is exactly what it sounds like: a fixed set of columns, and any number of rows filling them in. A row in a books table has a title, an author, a year — one book per row, the same shape for every row.

The shape is fixed on purpose. Because every row has the same columns, the database can answer questions about them quickly and honestly: which books came out after 1960, how many authors there are, what the average year is.

A question, written as a sentence

SQL is how you ask. A query says what you want, not how to find it: SELECT title FROM books; asks for one column of one table, and the answer comes back as a table of its own — the rows that match, with only the columns you asked for.

Reading a query aloud is usually enough to understand it. SELECT names the columns, FROM names the table, and the clauses after them narrow the rows: WHERE keeps some, ORDER BY sorts them, LIMIT stops early.

What the database does with the sentence

A query is not a set of instructions the way a loop is. You describe the answer — these columns, from this table, with this condition — and the database decides how to get it: which rows to read, in what order, and whether an index can skip most of the work. That is what declarative means.

The database parses the sentence into a plan, checks that every table and column exists, and only then runs it. That is why a misspelled column fails before any row is touched: the error is about the sentence, not the data.

The plan is not part of the query and may change as the data grows. The same SQL can be slow on a table with no index and fast once one exists, without a single character of the query changing.

A statement, and the table it answers with

SQL has statements that ask (SELECT) and statements that change (INSERT, UPDATE, DELETE, CREATE). This course starts with asking, because a query cannot damage anything: the worst it can do is return an answer you did not want.

Every SELECT answers with a table — rows and columns, even when there is one row and one column. That is why results can be nested inside other queries later: the answer to one question has the same shape as the table the question was asked about.

A statement ends with a semicolon. In a file with several statements, the semicolon is what separates them; leave it out and the next statement becomes part of the same broken sentence.

Rows have no natural order

A table is a set of rows, not a list. The database stores them however it finds convenient — in the order they were written, in the order an index happens to hold them, or in whatever order a query plan produces — and none of that is part of the data.

Two runs of the same query may answer in different orders, and a new version of the database can change the order. If the order matters, the query has to say so with ORDER BY; any order you notice otherwise is an accident.

What else the file holds

A database file is more than rows. It holds the table definitions, the constraints on them, the indexes built over them, and a catalogue that records all of it — the schema. CREATE TABLE writes to that catalogue as much as to the data, which is why a second connection opened on the same file sees the same tables: the definition is stored, not remembered by the program that made it.

It is also why a mistake at this level lasts. Every query written afterwards is shaped by the columns and rules declared here, so a table that forgot to say a column is required leaves every later reader guessing whether a blank means zero, unknown or not applicable.

The smallest useful query

-- One column, every row.
SELECT title FROM books;

-- Every column. The asterisk means "all of them".
SELECT * FROM books;

Try these next

• Change the first example to return only the author column.
• Add a WHERE year > 1970 and notice that the answer is smaller but still a table.
• Write SELECT 1 + 1 AS answer; — a query with no table at all, which proves the result is just a table.