Nine out of ten queries you'll ever write are reads: find an order, check a status, confirm a record appeared. All reading is done with one command — SELECT. It's safe: run it as many times as you like, the data won't change. We continue with the store database and its customers and orders tables.

SELECT and FROM: what and from where

The minimal query — "show me everything in the table":

SELECT * FROM orders;

FROM names the table, SELECT — which columns to show; the asterisk means "all of them." In practice you more often list the columns explicitly:

SELECT id, status, amount FROM orders;

The result is easier to read, and on wide tables (40 columns each) it's the only way not to drown. The semicolon ends the query; case doesn't matter (select = SELECT), but keywords are conventionally written in capitals — it helps the eye separate them from names.

WHERE: keep only what you need

The real power of queries is the WHERE filter: it keeps the rows for which the condition is true.

SELECT id, amount FROM orders WHERE status = 'paid';

Comparisons: =, <> (not equal), >, <, >=, <=. Text values go in single quotes: 'paid'. Numbers — without quotes: amount > 1000.

Conditions combine with AND and OR:

SELECT id FROM orders
WHERE status = 'paid' AND amount > 1000;

Be careful with OR: AND binds tighter than OR, so put parentheses around mixed conditions — WHERE (status = 'created' OR status = 'paid') AND amount > 500 and WHERE status = 'created' OR (status = 'paid' AND amount > 500) are two different queries.

Three useful filters beyond comparisons:

  • IN — value from a list: WHERE status IN ('created', 'paid') — shorter than a chain of ORs.
  • BETWEEN — an inclusive range: WHERE amount BETWEEN 500 AND 1500.
  • LIKE — pattern search in strings: WHERE email LIKE '%@gmail.com'. The percent sign means "any characters": 'anna%' — starts with "anna," '%anna%' — contains "anna." PostgreSQL also has the case-insensitive ILIKE — for searching names and emails it's usually the one you want.

ORDER BY and LIMIT: order and portion

Without explicit sorting the database returns rows in unpredictable order — one way today, another tomorrow. Want order — say so:

SELECT id, amount FROM orders
ORDER BY amount DESC
LIMIT 10;

ORDER BY amount DESC — by amount descending (ASC — ascending, the default). LIMIT 10 — only the first ten rows. Together it's "top 10 most expensive orders" — and a safety measure at the same time: without LIMIT, a query on a table of millions will drag them all.

You can sort by several columns: ORDER BY status, amount DESC — grouped by status first, by descending amount within.

Where this applies

SELECT … FROM … WHERE … is the everyday workhorse: confirm the order from the UI really got created; verify the status changed after cancellation; find a user by an email fragment with LIKE; peek at the latest records with ORDER BY created_at DESC LIMIT 20. The moment you stop asking a developer "could you check the database?" and check yourself, your work speed changes in a leap.

Where beginners stumble:

  • Double quotes instead of single. WHERE status = "paid" is an error in many DBMSes: double quotes are for column names, single quotes are for values.
  • Comparing without minding case and spaces. 'Paid', 'paid' and 'paid ' are three different values. Can't find the obvious — check case and trailing spaces.
  • Relying on the "natural" row order. Without ORDER BY the order isn't guaranteed — "the first row was different yesterday" is not a database bug but a missing sort in the query.

What to learn next. Real data is spread across several tables: the customer's name in one, their orders in another. Putting them together is what JOIN teaches.