Basic

Only the rows that match

I pulled the whole table and crossed out the rows I did not want with my finger. That is the wrong habit. WHERE makes the database do the crossing out before it sends anything.

What you will see

name Grace sqlite>

About 2 min of reading

Try It

Use the same table and pull it through four different filters, watching the row count shrink.

Filter on an exact number. One row matches id 2, so one name returns.

SELECT name FROM users WHERE id = 2;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

name Grace sqlite>

Filter on text. The value needs double quotes or the row is silently dropped.

SELECT id FROM users WHERE name = "Ada";

Before you run it, say out loud what it will print. Then open the answer.

What it prints

id 1 sqlite>

Filter with a greater-than. Two rows have an id above 1.

SELECT name FROM users WHERE id > 1;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

name Grace Alan sqlite>

Join two tests with AND. Only the middle row satisfies both bounds.

SELECT name FROM users WHERE id > 1 AND id < 3;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

name Grace sqlite>

Data Flow

FROM reads the rows, WHERE tests each one, and only the rows that pass the test are returned.

1 FROM points at the table

↓

2 WHERE holds a test

↓

3 Each row is tested

↓

4 Only passing rows return

Why this works

WHERE runs before you see it

The filter happens inside the database, not in your head. The database checks each row against the test and only sends the ones that pass. A table with a million rows and a WHERE that matches three sends three rows over, not a million.

The comparison operators

For numbers and text you have the usual set:

= equal, <> or != not equal
> greater, < less
>= at least, <= at most

There is a trap waiting for you here: = is one equals sign for a test, not two. Writing == is a syntax error in SQL.

Examples

One condition

SELECT name FROM users WHERE id = 2;

WHERE sits after FROM and holds a test. Only rows where the test is true come back. Here only the row with id 2 survives, so you get one name.

Text needs quotes

SELECT id FROM users WHERE name = "Ada";

Text values go inside double quotes. Forget the quotes and sqlite reads Ada as a column name that does not exist, and the row is dropped. The quotes are not decoration.

Combine tests

SELECT name FROM users WHERE id > 1 AND id < 3;

AND joins tests and both must hold. OR needs only one. This one returns the middle row, because its id is bigger than 1 and smaller than 3.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Asking a question

Previous Lesson

Asking a question

Next Lesson

Stitching two tables

Stitching two tables →