SELECT basics

You've got a small users table to play with. The seed loaded 10 rows — some active, some not. We'll start with the most fundamental SQL statement: SELECT.

Read every row

The dumbest, most useful query: ask for everything.

SELECT * FROM users;

You'll see ten rows, all five columns. The * is a wildcard for "every column."

Pick the columns you want

* is convenient but wasteful — and in real queries you almost always know exactly which columns you need. Name them.

SELECT full_name, email FROM users;

Order matters: the result columns come back in the order you ask for them.

Filter rows with WHERE

A WHERE clause restricts which rows are returned. Boolean expressions on column values are the bread and butter.

SELECT full_name, signed_up_at
FROM users
WHERE is_active = true;

There are seven active users in the seed. Try flipping the predicate to is_active = false to see the three who aren't.

Sort with ORDER BY

The order of rows coming out of a query is not guaranteed unless you ask for it. ORDER BY makes it explicit.

SELECT full_name, signed_up_at
FROM users
ORDER BY signed_up_at DESC
LIMIT 3;

DESC reverses the default ascending order, and LIMIT 3 caps the result at three rows. Useful for "top N" style queries.

What you learned

• SELECT * vs. SELECT col1, col2 — projection
• WHERE — filter rows by predicate
• ORDER BY ... DESC LIMIT N — sort and cap

Up next: the WHERE clause has a lot more in it than =.