Your first SQL query: ask a table of pets a question

Almost every app you use keeps its data in a database, and the language people use to ask a database questions is SQL (say it "S-Q-L" or "sequel"; both are fine). The good news: you can do a surprising amount with five words. This page teaches SELECT, FROM, WHERE, ORDER BY and LIMIT on a tiny table of pets, with a query builder you can play with, and then shows you the one thing that confuses almost everyone: the database doesn't run your query in the order you wrote it.

A table is a spreadsheet with rules

A database stores data in tables. A table looks a lot like a spreadsheet: each row is one thing (here, one pet), and each column is one fact about that thing (its name, its age). The difference from a spreadsheet is that the rules are strict: every row has exactly the same columns, and each column holds one type of value, like whole numbers or text.

Here is the whole table we'll use. It's called pets, and it has 10 rows and 5 columns.

The pets table. id = a unique number for each row · weight_kg = weight in kilograms

The id column is a common habit: a number that's different for every row, so you can always point at exactly one pet, even if two pets were both called Max. name and species hold text; age holds whole numbers; weight_kg holds numbers with a decimal point.

Your first query

A query is a question you send to the database. The database answers with a new, temporary table called the result. Here's the simplest useful query:

SELECT name, age FROM pets;

Read it almost like English: "select the name and age columns from the pets table". You get all 10 pets back, but only those two columns. A few things to notice:

SELECT and FROM are keywords: words with a fixed meaning in SQL. Each keyword plus what follows it is called a clause: SELECT name, age is the SELECT clause.
Columns are separated by commas. Write SELECT * (a star) to mean "every column".
Keywords don't care about case: select works just as well as SELECT. Writing them in capitals is a convention that makes queries easier to scan.
The semicolon ; marks the end of the query, like a full stop. Many tools let you leave it off for a single query, but it's a good habit.
Line breaks and extra spaces don't matter. Longer queries are usually written one clause per line.

Build a query

Now the other three words. Use the builder below: pick columns, add a filter, sort, and limit. The SQL text and the result update as you go. Every result here is exactly what a real database (SQLite) returns for that query.

Query builder · table pets

SELECT · which columns

WHERE · keep only rows where…

ORDER BY · sort by

LIMIT · at most this many rows

Your SQL

Result

WHERE: keep only some rows

WHERE is a filter. The database checks every row against your condition and keeps only the rows where it's true. The condition usually compares a column with a value using one of these operators:

= equal to, and != not equal to (<> means the same and is the official spelling).
> greater than, < less than, >= and <= for "or equal".

Text values go inside single quotes: WHERE species = 'cat'. Numbers don't: WHERE age > 5. Notice you can filter on a column you didn't select. Filtering and choosing columns are separate jobs.

You can join conditions with AND (both must be true) and OR (at least one must be true): WHERE species = 'dog' AND age > 5 gives Max, Pepper and Tofu. The builder keeps to one condition so you can see each one clearly.

ORDER BY: sort the result

ORDER BY age sorts the rows from smallest to largest age. That's called ascending and you can write it out as ASC, but it's the default. Add DESC (descending) to go largest first. Text sorts alphabetically.

Without ORDER BY, the database is allowed to return rows in any order. On a tiny table they usually come back in the order they were added, but that's luck, not a promise. If the order matters to you, say so with ORDER BY.

LIMIT: only the first few

LIMIT 3 keeps at most 3 rows of the result and drops the rest. On its own that's just "some 3 rows". Combined with ORDER BY, it answers "top 3" questions: ORDER BY weight_kg DESC LIMIT 3 is "the three heaviest pets" (Max, Tofu, Biscuit). If fewer rows are left than the limit, you simply get all of them.

LIMIT works in SQLite, PostgreSQL and MySQL, the three databases beginners meet most. Microsoft SQL Server spells it differently (SELECT TOP 3 …); the idea is the same.

You write it in one order, it runs in another

SQL makes you write the clauses in a fixed order: SELECT, FROM, WHERE, ORDER BY, LIMIT. Swap them and you get an error. But the database thinks about them in a different order. It can't pick columns before it knows which table to read, so logically it goes:

FROMwhich table?

WHEREthrow away rows

SELECTchoose columns

ORDER BYsort what's left

LIMITcut to size

This is the logical order: the meaning of the query is as if it ran in these steps. (Behind the scenes a real database may take shortcuts, like using an index to jump straight to matching rows, but it always gives you the same answer these steps would.) Step through whatever query you built above and watch the highlighted line jump around.

Run it step by step · uses your query from the builder

Knowing this order explains some things that otherwise look random:

You can filter and sort by columns you didn't select. The rows still have all their columns when WHERE runs, and the database keeps the sort column around for ORDER BY.
LIMIT happens last, after sorting. That's why "ORDER BY then LIMIT" gives you a real top 3, not 3 random rows that then get sorted.
A nickname from SELECT can't always be used in WHERE. You can rename a column or calculation with AS, like SELECT name, age * 12 AS age_months. You can use age_months in ORDER BY, which runs after SELECT. But in PostgreSQL or MySQL, WHERE age_months > 100 fails with an error saying the column doesn't exist, because WHERE runs before SELECT has made it. (SQLite is relaxed and lets it through, which is handy but won't teach you good habits.) Repeat the calculation instead: WHERE age * 12 > 100.

The mistakes everyone makes first

Each of these was run against the pets table in SQLite; the messages in other databases are worded differently but mean the same thing.

Forgetting the quotes around text. Without quotes, SQL thinks dog is the name of a column:

SELECT name FROM pets WHERE species = dog;
no such column: dog

Clauses in the wrong order. The written order is fixed, even though the running order isn't:

SELECT name FROM pets LIMIT 3 ORDER BY age;
near "ORDER": syntax error

Getting the case of text wrong. Keywords ignore case, but the data doesn't always: in SQLite and PostgreSQL, WHERE species = 'Dog' finds nothing, because every species in our table is stored in lowercase. You get an empty result, not an error, which is easy to miss. (MySQL ignores case in comparisons by default, so there it would match.)

A missing or extra comma in the column list, like SELECT name age or SELECT name, age, FROM pets. The first one quietly renames name to age in the result; the second is a syntax error. Commas go between columns only.

Check yourself

Use the pets table at the top. Pick an answer to see why.

SELECT name FROM pets
WHERE species = 'cat'
ORDER BY age DESC
LIMIT 1;

WHERE keeps the three cats (Luna 5, Shadow 12, Mochi 4). ORDER BY age DESC puts the oldest first, and LIMIT 1 keeps only that one: Shadow. Sunny is older, but Sunny is a parrot and was thrown out by WHERE before sorting happened.

SELECT name FROM pets WHERE age > 20;

What comes back?

No pet is older than 14, so no row passes the filter. That isn't a mistake in the query: the database happily returns a result with zero rows.

In which logical order does the database handle these clauses?

The first option is the order you write them in. The database needs the table first (FROM), filters rows (WHERE), picks columns (SELECT), sorts (ORDER BY) and finally cuts the list (LIMIT).

SELECT name, weight_kg FROM pets
WHERE weight_kg < 5;

How many rows come back?

Luna (4.2), Pickles (1.8), Nibbles (0.12), Mochi (3.9) and Sunny (0.4). Shadow, at 5.6, just misses out. Try it in the builder to check.

The short version

A table has rows (things) and columns (facts about them). A query asks a question and gets a result table back.
SELECT picks columns (* for all), FROM names the table.
WHERE keeps only rows that match a condition. Text goes in single quotes, numbers don't.
ORDER BY sorts, ascending by default, DESC for reverse. No ORDER BY means no promised order.
LIMIT n keeps at most n rows. With ORDER BY, it gives you a "top n".
You write SELECT, FROM, WHERE, ORDER BY, LIMIT. The database thinks FROM, WHERE, SELECT, ORDER BY, LIMIT.

That's genuinely enough to answer real questions about real data. Next time you see a "top 10" list in an app, there's a good chance an ORDER BY … LIMIT 10 is behind it.