Narrowing down with WHERE

01 / 06

You want fewer rows

SELECT was handing back every row. But more often you want only the rows that match — "dishes at 600 or under", "just the noodles".

The job that if does in JavaScript is done here by WHERE.

02 / 06

How WHERE looks

Write SELECT ... FROM ... WHERE condition; — the condition goes on after FROM.

A text value is wrapped in ' (single quotes). That is the SQL rule, and it differs from JavaScript's ", so watch out.

SELECT * FROM menus WHERE category = 'noodles';

Result

only the two rows, tofu udon and soy ramen

03 / 06

Comparing numbers

For number conditions you have the comparison operators.

price < 600 … under 600
price <= 600 … 600 or under
price > 600 … over 600

Much like JavaScript, except that "equals" is a single =, not ===. That is the big difference.

SELECT name, price FROM menus WHERE price <= 600;

04 / 06

"Not equal" is <>

"Is not equal" is written <> (!= works too).

WHERE narrows the rows, SELECT narrows the columns. You can now choose both.

SELECT name FROM menus WHERE category <> 'noodles';

05 / 06

Text always needs quotes

Leave the quotes off — WHERE category = noodles; — and SQL reads noodles as a column name, not a value, and errors.

Numbers can be written bare, but text always needs ' around it. Keep that distinction in mind.

-- wrong (missing quotes)SELECT * FROM menus WHERE category = noodles;-- correctSELECT * FROM menus WHERE category = 'noodles';

06 / 06

Mentally, WHERE runs first

You read SELECT name FROM menus WHERE ... left to right, but that is not quite the order it actually runs in.

SQLite settles on the table with FROM first, then narrows the rows with WHERE, and only at the end picks which columns to show with SELECT. Thinking "narrow first, then pick what to show" keeps you from getting lost once conditions get more complex.