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 customer and orders tables.

SELECT and FROM: what and from where

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

live example

SELECT * FROM orders;
Run

Running examples is part of paid access. There the same code runs inside the article: editor, run and check next to the paragraph. Three free days →

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

live example

SELECT id, status, total_amount FROM orders;
Run

Running examples is part of paid access. There the same code runs inside the article: editor, run and check next to the paragraph. Three free days →

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.

live example

SELECT id, total_amount FROM orders WHERE status = 'PAID';
Run

Running examples is part of paid access. There the same code runs inside the article: editor, run and check next to the paragraph. Three free days →

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

Conditions combine with AND and OR:

live example

SELECT id FROM orders
WHERE status = 'PAID' AND total_amount > 1000;
Run

Running examples is part of paid access. There the same code runs inside the article: editor, run and check next to the paragraph. Three free days →

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

Three useful filters beyond comparisons:

  • IN — value from a list: WHERE status IN ('PENDING_PAYMENT', 'PAID') — shorter than a chain of ORs.
  • BETWEEN — an inclusive range: WHERE total_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.

Practice here: Unpaid orders · Anna's orders in two statuses · Products within a price range · Customers whose surname ends in "-ova"

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:

live example

SELECT id, total_amount FROM orders
ORDER BY total_amount DESC
LIMIT 10;
Run

Running examples is part of paid access. There the same code runs inside the article: editor, run and check next to the paragraph. Three free days →

ORDER BY total_amount DESC — by total_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, total_amount DESC — grouped by status first, by descending total_amount within.

Practice here: The storefront from cheapest to priciest · A customer's order feed: by status, newest on top

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.