Choosing Rows with WHERE

So far, your queries have shown every row of a table. Usually, though, you want only some of them: the games for small children, the orders of one customer, the reviews with five stars. To choose rows, add WHERE and a condition after FROM:

SELECT title, age_rating
FROM games
WHERE age_rating = 3;

The database goes through the table row by row and checks the condition for each of them: is the age_rating of this row 3? If it is, the row goes into the result; if it isn't, the row is left out. So the result has only the 7 games that are suitable from the age of 3.

= compares two values, here the value in the column age_rating with the number 3. In SQL, a single = means “equals”, not == as in other programming languages. (SQLite understands == too, but other databases don't, so stick to =.)

The column in the condition doesn't have to be among the columns you show. This query shows only the title, and it chooses the row by its primary key, which is the surest way to find one particular row:

SELECT title
FROM games
WHERE id = 7;

WHERE has its fixed place in a query: after FROM, and before ORDER BY and LIMIT. The parts of a query always come in this order:

SELECT columns
FROM table
WHERE condition
ORDER BY columns
LIMIT number;

Only SELECT and FROM are always needed. You add the others when you need them, but always in this order. The last three also work in this order: WHERE first chooses the rows, ORDER BY sorts the ones that are left, and LIMIT takes the first few of them. The two most expensive games for small children:

SELECT title, price
FROM games
WHERE age_rating = 3
ORDER BY price DESC
LIMIT 2;

Typical mistake: WHERE after ORDER BY. The query SELECT title FROM games ORDER BY title WHERE age_rating = 3; ends with an error:

Error on line 1: near "WHERE": syntax error

SQLite didn't expect WHERE after the sorting. Move the condition in front of ORDER BY.