Asking narrow questions

WHERE: the rows you mean

A SELECT from one table with a WHERE price < 20 keeps only the rows whose condition is true. The operators are the ones you expect — =, <>, <, >, <=, >= — with one Postgres gift on the text side: ILIKE is LIKE without the case-sensitivity, so title ILIKE '%desk%' finds "Desk" and "desk" alike.

Conditions combine with AND, OR and NOT, and parentheses decide how they group. a AND b OR c is read as (a AND b) OR c, which is a rule rather than a suggestion — when you meant a different grouping, the parentheses are the only way to say it.

Suppose the catalogue holds a desk at 249 and a lamp at 24.50. WHERE price < 20 keeps neither, and WHERE price < 30 keeps the lamp alone; the condition is tested on every row and only the true ones survive. The column list has no say in which rows come back — SELECT title and SELECT * with the same WHERE return the same rows, one column narrower.

Sets and ranges, briefly

IN tests a value against a list — WHERE category IN ('desk', 'lamp') — and reads far better than a chain of ORs. BETWEEN a AND b tests a range with both ends included, which is exactly what you want for prices and dates, and exactly what you must remember when a boundary is meant to be exclusive.

A range test on dates is safer when written as two comparisons — ts >= '2026-01-01' AND ts < '2026-02-01' — because the exclusive top end cannot double-count a midnight. It is not a style preference; it is the difference between correct and almost correct.

Order and cut

SQL does not promise an order until you ask for one: ORDER BY price DESC sorts, ASC is the default, and a second key breaks ties — ORDER BY category, price DESC sorts by category and, inside each, by price. LIMIT 3 then keeps the top of that order, and together they are how "the three most expensive lamps" is written.

An alias made with AS names a column or an expression for the rest of the query — and it is available to ORDER BY, which is why SELECT price / 2 AS half_price ... ORDER BY half_price DESC works. Arithmetic in a SELECT is ordinary, and Postgres will not round it for you: that is chapter three's subject.

What WHERE actually sees

A query is evaluated in a different order than it is written: the table is found first, then WHERE tests each row, then the SELECT list builds the output, and only after that does ORDER BY sort and LIMIT cut. That is why a column alias from the SELECT cannot be used in WHERE — at that point the alias does not exist yet — while ORDER BY can use it.

The test itself is three-valued. A row is kept only when the condition is true; false and unknown are both dropped, and the second one is easy to mistake for "no matching data". A condition that mentions NULL is unknown unless it uses IS NULL or IS DISTINCT FROM.

Where NULLs land in a sort

PostgreSQL treats NULL as larger than every real value for sorting. So ORDER BY price ASC puts missing prices last, and ORDER BY price DESC puts them first, unless you say NULLS LAST or NULLS FIRST explicitly. Making that choice in the query is kinder than making the reader guess.

Sorting is also not stable in the sense people expect: two rows with equal keys may swap between runs. Add a second key — an id, a date — when the order has to be the same every time, especially before a LIMIT.

Try these next

• Sort the shop by price descending, then add NULLS LAST and compare where the missing prices went.
• Write a query with LIMIT 2 but no ORDER BY, run it twice, and see whether the rows move.
• Find every product whose title matches _esk% and explain why the underscore is not the same as %.

Narrowing a catalogue

-- One condition.
SELECT title, price FROM products WHERE price < 20;

-- Two conditions, and a case-insensitive match.
SELECT title FROM products
WHERE category = 'lamp' AND title ILIKE '%desk%';

-- A list, and a range with both ends included.
SELECT title, price FROM products
WHERE category IN ('desk', 'lamp') AND price BETWEEN 10 AND 100;

-- Sort, then keep the top of the order.
SELECT title, price FROM products
ORDER BY price DESC
LIMIT 2;

-- Aliases name expressions for the rest of the query.
SELECT title, round(price * 1.2, 2) AS with_tax
FROM products
ORDER BY with_tax DESC;