SELECT, WHERE, ORDER BY and LIMIT

ConceptFoundation3 min read10 min hands-on#fundamentals#sql

The idea in 30 seconds

A query says which table to read, which rows to keep, which columns to return, how to sort them and how many to take. You write those parts in one order, but PostgreSQL evaluates them in another: FROM, then WHERE, then SELECT, then ORDER BY, then LIMIT. Knowing that order explains several errors that otherwise look arbitrary.

Helps to know first: Install PostgreSQL and learn the environment

Every query on this page runs against the practice database. Connect with psql shop and type them as you read.

The shape of a query

SELECT name, category, price        -- which columns
FROM products                       -- which table
WHERE category = 'Accessories'      -- which rows
ORDER BY price DESC;                -- in what order

        name         |  category   |  price
---------------------+-------------+---------
 Mechanical keyboard | Accessories | 4500.00
 USB-C hub           | Accessories | 2499.00
 Laptop stand        | Accessories | 1899.00
 Wireless mouse      | Accessories | 1200.00
 Cable organiser     | Accessories |  499.00
(5 rows)

SELECT lists the columns you want, or expressions computed from them. SELECT * means every column, which is handy while exploring but best avoided in application code, where a new column would silently change the result.
FROM names the table.
WHERE keeps only the rows for which the condition is true.
ORDER BY sorts: ascending by default, DESC for descending.

Add LIMIT to take only the first few rows of the sorted result:

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

            name             |  price
-----------------------------+----------
 27-inch monitor             | 18999.00
 Office chair                | 14500.00
 Noise-cancelling headphones | 12999.00

LIMIT without ORDER BY is almost always a bug: you get some three rows, not the top three. Rows have no order until you give them one.

Written order versus evaluated order

You write SELECT … FROM … WHERE … ORDER BY … LIMIT. PostgreSQL evaluates it in a different order:

Step	Clause	What happens
1	`FROM`	Gather the rows of the table (or tables)
2	`WHERE`	Throw away rows whose condition is not true
3	`SELECT`	Compute the output columns for the rows that are left
4	`ORDER BY`	Sort the output
5	`LIMIT`	Keep only the first n rows

Grouping steps fit between WHERE and SELECT; they arrive in Aggregation and grouping.

PredictUsing an alias in WHERE

You compute a price including 18% tax, name it, and filter on the name:

SELECT name, price * 1.18 AS price_with_tax
FROM products
WHERE price_with_tax > 10000;

Does it work?

No. PostgreSQL replies:

ERROR:  column "price_with_tax" does not exist

WHERE runs at step 2; the alias is only created at step 3, in SELECT. At the moment WHERE runs, price_with_tax does not exist yet. Repeat the expression instead: WHERE price * 1.18 > 10000.

ORDER BY runs at step 4, after SELECT, so there the alias works:

SELECT name, price * 1.18 AS price_with_tax FROM products ORDER BY price_with_tax DESC LIMIT 3;

            name             | price_with_tax
-----------------------------+----------------
 27-inch monitor             |     22418.8200
 Office chair                |     17110.0000
 Noise-cancelling headphones |     15338.8200

Sorting and empty values

When the column you sort by has NULLs, they go last in ascending order and first in descending order:

SELECT name, city FROM customers ORDER BY city;

     name      |   city
---------------+----------
 Farhan Ali    | Karachi
 Ben Okafor    | Lagos
 Asha Rao      | Pune
 Divya Iyer    | Pune
 Grace Kim     | Seoul
 Chen Wei      | Shanghai
 Hugo Martin   |
 Elena Petrova |

Asha and Divya share a city, and so do the two customers with no city. Rows that tie on every sort column can come back in any order. If the order matters, add another column to break the tie: ORDER BY city, name.

Check yourself

In what order does PostgreSQL evaluate FROM, WHERE, SELECT, ORDER BY and LIMIT?
Show answer
FROM, then WHERE, then SELECT, then ORDER BY, then LIMIT.
I could explain this without looking
Why can ORDER BY use a column alias defined in SELECT when WHERE cannot?
Show answer
ORDER BY is evaluated after SELECT has computed the output columns, so the alias exists. WHERE is evaluated before SELECT, when it does not.
I could explain this without looking
What does SELECT * FROM orders LIMIT 5 return?
Show answer
Five rows of the table, but no particular five: without ORDER BY the order is unspecified, so “the first five” means nothing. Add ORDER BY ordered_at DESC to get the five newest.
I could explain this without looking

Sources

Where each claim on this page comes from.

PostgreSQL documentation: SELECT

Primary· Description, and WHERE Clause

The order in which SELECT is processed; WHERE keeps only rows for which the condition returns true; without ORDER BY, rows come back in whatever order is fastest.

PostgreSQL documentation: 7.5 Sorting Rows (ORDER BY)

Primary· §7.5 Sorting Rows (ORDER BY)

Without ORDER BY the order is unspecified and must not be relied on; ORDER BY can use an output column's name; NULLs sort last in ascending order and first in descending order by default.

The shape of a query

Written order versus evaluated order

Sorting and empty values

Check yourself

Sources

Go further