SELECT and WHERE: ordering from the menu

In lesson 01 you learned that SQL is how you ask. Now you learn the two words that do most of the asking. SELECT chooses columns. WHERE chooses rows. Nearly every query you will ever write is built on these two, so let us get them properly into your hands.

SELECT picks your columns

Every row carries all of its facts. Most of the time you only want a couple of them. SELECT tells each row which facts to hand over:

SELECT item, qty FROM orders;

fig 1 · SELECT trims columns, every row survives

The table column did not make the trip, because nobody asked for it. The rows themselves are untouched: same three orders, just traveling lighter.

WHERE picks your rows

WHERE stands at the door and asks every row one yes/no question. Answer yes, you pass. Answer no, you are off the belt:

SELECT * FROM orders WHERE item = 'maki';

fig 2 · WHERE: each row answers the question at the gate

Two things to notice. The question is asked of every row, one at a time. And the rows that fail are not deleted, they are just not invited to the result.

Work the gate yourself

Reading about gates is one thing. Operating one is better. Click a condition and watch which rows survive:

// try it live · no database needed

SELECT * FROM orders;

item	qty	tbl
maki	2	5
nigiri	1	3
temaki	1	5
maki	1	2
gunkan	3	5
nigiri	2	1

→ 6 of 6 rows pass the gate

Notice how qty >= 2 and tbl = 5 pick completely different rows from the same table. Same data, different question, different answer.

The question toolbox

The gate understands more questions than plain equals:

= and != for is, and is not
<, <=, >, >= for numbers (and dates, later)
AND and OR to chain questions: item = 'maki' AND qty >= 2
LIKE 'ma%' for patterns: anything starting with “ma”

Try this yourself

Open your playground from lesson 01 and build the table for real:

CREATE TABLE orders (item TEXT, qty INTEGER, tbl INTEGER);
INSERT INTO orders VALUES
  ('maki', 2, 5), ('nigiri', 1, 3), ('temaki', 1, 5),
  ('maki', 1, 2), ('gunkan', 3, 5), ('nigiri', 2, 1);

SELECT item, qty FROM orders WHERE qty >= 2;

(The column is called tbl because table is a word the database keeps for itself.)

Then change the last line and answer these with queries: which orders went to table 5? Which items start with an “n”? Check your answers against the explorer above.