Filtering & sorting

Real questions are rarely about every row. You want the fantasy books under £12, the orders from February, the customers with no city on file. WHERE filters rows and ORDER BY sorts them. Both look simple, but NULL makes filtering subtler than it first appears, and careless sorting produces pages that shuffle between requests. This lesson covers both properly.

#WHERE: keeping the rows you want

WHERE takes a condition and keeps only the rows for which it is true:

SELECT title, price
FROM books
WHERE genre = 'fantasy';

        title         | price
----------------------+-------
 A Wizard of Earthsea | 12.99
 Guards! Guards!      | 10.99
 Small Gods           | 10.99
 Going Postal         | 11.50
(4 rows)

The comparison operators are the ones you expect:

Strings compare case-sensitively ('Fantasy' = 'fantasy' is false) and must be in single quotes. Dates are written as ISO strings like '2025-02-01', which PostgreSQL converts to the column's date type.

#Combining conditions: AND, OR, NOT

AND requires both sides to be true; OR requires at least one; NOT inverts. The trap is precedence: AND binds tighter than OR, just as multiplication binds tighter than addition. Suppose you want sci-fi or fantasy books under £12:

SELECT title, genre, price
FROM books
WHERE genre = 'sci-fi' OR genre = 'fantasy' AND price < 12;

           title           |  genre  | price
---------------------------+---------+-------
 The Left Hand of Darkness | sci-fi  | 15.50
 The Dispossessed          | sci-fi  | 16.00
 Guards! Guards!           | fantasy | 10.99
 Small Gods                | fantasy | 10.99
 Going Postal              | fantasy | 11.50
 Kindred                   | sci-fi  | 14.00
 Parable of the Sower      | sci-fi  | 13.25
(7 rows)

That is wrong: it was parsed as genre = 'sci-fi' OR (genre = 'fantasy' AND price < 12), so every sci-fi book came back regardless of price. Parentheses fix it:

SELECT title, genre, price
FROM books
WHERE (genre = 'sci-fi' OR genre = 'fantasy') AND price < 12;

      title      |  genre  | price
-----------------+---------+-------
 Guards! Guards! | fantasy | 10.99
 Small Gods      | fantasy | 10.99
 Going Postal    | fantasy | 11.50
(3 rows)

#Shorthand operators: IN, BETWEEN, LIKE

#IN

status IN ('paid', 'cancelled') is shorthand for status = 'paid' OR status = 'cancelled'. It reads better and removes the precedence problem. NOT IN negates it — with a serious NULL caveat you will see shortly.

#BETWEEN

x BETWEEN a AND b means x >= a AND x <= b. Both ends are inclusive:

SELECT title, published
FROM books
WHERE published BETWEEN 1969 AND 1979;

           title           | published
---------------------------+-----------
 The Left Hand of Darkness |      1969
 The Dispossessed          |      1974
 Kindred                   |      1979
(3 rows)

For date ranges, prefer a half-open interval — inclusive start, exclusive end:

SELECT order_id, ordered_on
FROM orders
WHERE ordered_on >= '2025-02-01' AND ordered_on < '2025-03-01';

 order_id | ordered_on
----------+------------
        3 | 2025-02-02
        4 | 2025-02-14
        5 | 2025-02-20
(3 rows)

With a date column, BETWEEN '2025-02-01' AND '2025-02-28' gives the same answer. But on a timestamp column, '2025-02-28' means midnight at the start of the 28th, so BETWEEN would silently drop everything that happened later that day. The half-open form is correct for both types and you never have to remember how many days a month has.

#LIKE and ILIKE

LIKE matches text against a pattern in which % means "any sequence of characters, including none" and _ means "exactly one character". ILIKE is PostgreSQL's case-insensitive version:

SELECT title FROM books WHERE title LIKE '%the%';

        title
----------------------
 Parable of the Sower
 Kafka on the Shore
(2 rows)

ILIKE '%the%' also returns The Left Hand of Darkness and The Dispossessed, because it ignores case. Other patterns: title LIKE 'The %' finds titles starting with "The ", and email LIKE '___@%' finds emails with exactly three characters before the @ (Ada, Ben and Dev). To match a literal % or _, escape it with a backslash: LIKE '100\%'.

#NULL and three-valued logic

Here is the part that trips up experienced developers. Try finding customers with no city:

SELECT name, city FROM customers WHERE city = NULL;

 name | city
------+------
(0 rows)

No error, and no rows. NULL means "unknown", and comparing anything to an unknown value gives an unknown result. SQL therefore uses three-valued logic: every condition evaluates to true, false or unknown (represented as a NULL boolean). WHERE keeps a row only when the condition is true; both false and unknown are discarded.

To test for NULL, use the dedicated operators IS NULL and IS NOT NULL, which always return true or false:

SELECT name, city FROM customers WHERE city IS NULL;

     name     |  city
--------------+--------
 Chloe Martin | (null)
 Farid Haddad | (null)
(2 rows)

#Negative conditions silently drop NULLs

"Customers not in Leeds" sounds like it should return four people — two in London and two with no city. It returns two:

SELECT name, city FROM customers WHERE city <> 'Leeds';

    name     |  city
-------------+--------
 Ben Okafor  | London
 Elena Rossi | London
(2 rows)

For Chloe and Farid, NULL <> 'Leeds' is unknown, so they are filtered out. If you meant to include them, say so explicitly with city <> 'Leeds' OR city IS NULL, or use the null-safe comparison IS DISTINCT FROM, which treats NULL as a regular, comparable value:

SELECT name, city FROM customers WHERE city IS DISTINCT FROM 'Leeds';

     name     |  city
--------------+--------
 Ben Okafor   | London
 Chloe Martin | (null)
 Elena Rossi  | London
 Farid Haddad | (null)
(4 rows)

IS NOT DISTINCT FROM is the null-safe version of =: NULL IS NOT DISTINCT FROM NULL is true.

#Replacing NULL with coalesce

coalesce(a, b, ...) returns its first non-NULL argument. It is the standard way to substitute a default for display or calculation:

SELECT name, coalesce(city, 'unknown') AS city FROM customers;

     name     |  city
--------------+---------
 Ada Park     | Leeds
 Ben Okafor   | London
 Chloe Martin | unknown
 Dev Raman    | Leeds
 Elena Rossi  | London
 Farid Haddad | unknown
(6 rows)

#WHERE runs before SELECT

Recall the logical order of a query. WHERE is evaluated right after FROM and before the select list, so it cannot see column aliases:

SELECT title, price * stock AS stock_value
FROM books
WHERE stock_value > 100;

ERROR:  column "stock_value" does not exist

Repeat the expression instead (WHERE price * stock > 100). The full logical order so far is: FROM → WHERE → SELECT → DISTINCT → ORDER BY → LIMIT.

#ORDER BY: sorting results

ORDER BY sorts the result by one or more expressions. ASC (ascending) is the default; DESC reverses it. With several keys, later keys break ties in earlier ones:

SELECT title, genre, price
FROM books
ORDER BY genre, price DESC;

           title           |  genre   | price
---------------------------+----------+-------
 A Wizard of Earthsea      | fantasy  | 12.99
 Going Postal              | fantasy  | 11.50
 Guards! Guards!           | fantasy  | 10.99
 Small Gods                | fantasy  | 10.99
 Kafka on the Shore        | literary | 18.50
 Norwegian Wood            | literary | 17.00
 The Dispossessed          | sci-fi   | 16.00
 The Left Hand of Darkness | sci-fi   | 15.50
 Kindred                   | sci-fi   | 14.00
 Parable of the Sower      | sci-fi   | 13.25
(10 rows)

Genres sort alphabetically, and within each genre prices go from high to low. Note that Guards! Guards! and Small Gods tie on both keys, so their relative order is not guaranteed.

You can sort by an expression (ORDER BY price * stock), by a select-list alias (ORDER BY stock_value — allowed because ORDER BY runs after SELECT), or by a column you do not even select.

#Where do NULLs go?

PostgreSQL treats NULL as larger than every value: with ASC they come last, with DESC they come first. Override that with NULLS FIRST or NULLS LAST:

SELECT name, city
FROM customers
ORDER BY city NULLS FIRST, name;

     name     |  city
--------------+--------
 Chloe Martin | (null)
 Farid Haddad | (null)
 Ada Park     | Leeds
 Dev Raman    | Leeds
 Ben Okafor   | London
 Elena Rossi  | London
(6 rows)

Other databases differ (MySQL and SQL Server put NULL first in ascending order), so state it explicitly when it matters.

#Deterministic order and pagination

If the sort keys can tie, the database may return tied rows in any order — and a different order each time. With LIMIT/OFFSET pagination, that means a row can appear on two pages or on none. The fix is to always end ORDER BY with a unique column, typically the primary key:

SELECT book_id, title, price
FROM books
ORDER BY price, book_id
LIMIT 3;

 book_id |      title      | price
---------+-----------------+-------
       4 | Guards! Guards! | 10.99
       5 | Small Gods      | 10.99
       6 | Going Postal    | 11.50
(3 rows)

For the next page, OFFSET 3 works, but the database still has to produce and discard the skipped rows, which gets slow on deep pages. Keyset pagination instead remembers the last row you showed and asks for rows after it, using a row comparison:

SELECT book_id, title, price
FROM books
WHERE (price, book_id) > (11.50, 6)
ORDER BY price, book_id
LIMIT 3;

 book_id |        title         | price
---------+----------------------+-------
       1 | A Wizard of Earthsea | 12.99
       8 | Parable of the Sower | 13.25
       7 | Kindred              | 14.00
(3 rows)

(price, book_id) > (11.50, 6) compares left to right, like sorting words alphabetically: a higher price wins, and on equal price a higher book_id wins. It stays fast at any depth when backed by an index, as the indexes lesson will show.

#Practice

Write two queries against the bookstore database.

1. List books that are in stock, cost less than £15, and are fantasy or sci-fi. Show title, genre, price and stock, ordered from most to least expensive, with title as the tie-breaker.
2. List every customer who was not referred by Ben Okafor (customer_id 2), including customers who were not referred by anyone. Show name and referred_by, sorted by name. First try referred_by <> 2 and explain why its result is wrong.

SELECT title, genre, price, stock
FROM books
WHERE stock > 0
  AND price < 15
  AND genre IN ('fantasy', 'sci-fi')
ORDER BY price DESC, title;

        title         |  genre  | price | stock
----------------------+---------+-------+-------
 Kindred              | sci-fi  | 14.00 |    11
 Parable of the Sower | sci-fi  | 13.25 |     5
 A Wizard of Earthsea | fantasy | 12.99 |    14
 Going Postal         | fantasy | 11.50 |     3
 Guards! Guards!      | fantasy | 10.99 |    22
 Small Gods           | fantasy | 10.99 |     9
(6 rows)

SELECT name, referred_by
FROM customers
WHERE referred_by IS DISTINCT FROM 2
ORDER BY name;

     name     | referred_by
--------------+-------------
 Ada Park     |      (null)
 Ben Okafor   |           1
 Chloe Martin |      (null)
 Elena Rossi  |           4
 Farid Haddad |           1
(5 rows)

#Key takeaways

• WHERE keeps rows whose condition is true; AND binds tighter than OR, so parenthesise mixed conditions.
• BETWEEN is inclusive on both ends; use half-open ranges (>= start AND < end) for dates and timestamps.
• Comparisons with NULL are unknown, never true: use IS NULL, IS DISTINCT FROM and coalesce, and remember that <> and NOT IN silently drop or break on NULLs.
• WHERE runs before the select list, so it cannot use aliases; ORDER BY runs after and can.
• PostgreSQL sorts NULLs last in ascending order and first in descending order; override with NULLS FIRST/NULLS LAST.
• Always end ORDER BY with a unique key when paginating, and prefer keyset pagination for deep pages.