# Filtering & sorting > Filter rows with WHERE, reason correctly about NULL and three-valued logic, and sort results predictably with ORDER BY. SQL & Databases roadmap · Querying · 16 min read · https://levelcode.org/roadmaps/sql/filtering-and-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**: ```sql SELECT title, price FROM books WHERE genre = 'fantasy'; ``` ```text 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: | Operator | Meaning | Example | | --- | --- | --- | | `=` | equal | `status = 'paid'` | | `<>` or `!=` | not equal | `genre <> 'literary'` | | `<`, `<=`, `>`, `>=` | ordering | `price < 12`, `ordered_on >= '2025-02-01'` | 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: ```sql SELECT title, genre, price FROM books WHERE genre = 'sci-fi' OR genre = 'fantasy' AND price < 12; ``` ```text 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: ```sql SELECT title, genre, price FROM books WHERE (genre = 'sci-fi' OR genre = 'fantasy') AND price < 12; ``` ```text title | genre | price -----------------+---------+------- Guards! Guards! | fantasy | 10.99 Small Gods | fantasy | 10.99 Going Postal | fantasy | 11.50 (3 rows) ``` > [!TIP] > Whenever a `WHERE` clause mixes `AND` and `OR`, add parentheses even if you believe precedence already does what you want. The next reader — often you — will not have to work it out. ## 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**: ```sql SELECT title, published FROM books WHERE published BETWEEN 1969 AND 1979; ``` ```text 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: ```sql SELECT order_id, ordered_on FROM orders WHERE ordered_on >= '2025-02-01' AND ordered_on < '2025-03-01'; ``` ```text 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: ```sql SELECT title FROM books WHERE title LIKE '%the%'; ``` ```text 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: ```sql SELECT name, city FROM customers WHERE city = NULL; ``` ```text 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. | Expression | Result | Why | | --- | --- | --- | | `NULL = NULL` | unknown | two unknown values might or might not be equal | | `NULL <> 1` | unknown | | | `NULL AND false` | false | false regardless of the unknown side | | `NULL AND true` | unknown | depends on the unknown side | | `NULL OR true` | true | true regardless of the unknown side | | `NOT NULL` | unknown | the opposite of unknown is unknown | To test for `NULL`, use the dedicated operators `IS NULL` and `IS NOT NULL`, which always return true or false: ```sql SELECT name, city FROM customers WHERE city IS NULL; ``` ```text 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: ```sql SELECT name, city FROM customers WHERE city <> 'Leeds'; ``` ```text 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: ```sql SELECT name, city FROM customers WHERE city IS DISTINCT FROM 'Leeds'; ``` ```text 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. > [!WARNING] > `NOT IN` with a `NULL` in the list matches **nothing**. `city NOT IN ('Leeds', NULL)` expands to `city <> 'Leeds' AND city <> NULL`; the second part is always unknown, so the whole condition can never be true and the query returns zero rows. This matters most when the list comes from a subquery, which you will meet in a later lesson. ### 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: ```sql SELECT name, coalesce(city, 'unknown') AS city FROM customers; ``` ```text 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: ```sql SELECT title, price * stock AS stock_value FROM books WHERE stock_value > 100; ``` ```text 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: ```sql SELECT title, genre, price FROM books ORDER BY genre, price DESC; ``` ```text 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`: ```sql SELECT name, city FROM customers ORDER BY city NULLS FIRST, name; ``` ```text 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: ```sql SELECT book_id, title, price FROM books ORDER BY price, book_id LIMIT 3; ``` ```text 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: ```sql SELECT book_id, title, price FROM books WHERE (price, book_id) > (11.50, 6) ORDER BY price, book_id LIMIT 3; ``` ```text 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. **Solution** ```sql SELECT title, genre, price, stock FROM books WHERE stock > 0 AND price < 15 AND genre IN ('fantasy', 'sci-fi') ORDER BY price DESC, title; ``` ```text 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) ``` Using `IN` avoids the `AND`/`OR` precedence trap entirely. *The Dispossessed* is excluded by `stock > 0`. For the second query, `WHERE referred_by <> 2` returns only Ben, Elena and Farid: Ada and Chloe have `referred_by = NULL`, and `NULL <> 2` is unknown, so they are dropped. The null-safe version: ```sql SELECT name, referred_by FROM customers WHERE referred_by IS DISTINCT FROM 2 ORDER BY name; ``` ```text name | referred_by --------------+------------- Ada Park | (null) Ben Okafor | 1 Chloe Martin | (null) Elena Rossi | 4 Farid Haddad | 1 (5 rows) ``` `WHERE referred_by <> 2 OR referred_by IS NULL` is equivalent. Dev is the only customer excluded, because Ben referred him. ## 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 `NULL`s. - `WHERE` runs before the select list, so it cannot use aliases; `ORDER BY` runs after and can. - PostgreSQL sorts `NULL`s 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. ## Quiz 1. customers has 2 rows in Leeds, 2 in London and 2 with city NULL. How many rows does WHERE city <> 'Leeds' return? A. 4 B. 2 C. 0 D. 6 **Answer: B.** For the two NULL rows, NULL <> 'Leeds' evaluates to unknown, and WHERE only keeps rows where the condition is true. Only the two London rows remain. Use IS DISTINCT FROM 'Leeds' to include the NULLs. 2. What does the condition 3 NOT IN (1, 2, NULL) evaluate to? A. true B. false C. An error D. unknown (NULL), so the row is filtered out **Answer: D.** It expands to 3 <> 1 AND 3 <> 2 AND 3 <> NULL. The last comparison is unknown, and true AND unknown is unknown. A NULL in a NOT IN list means the condition can never be true. 3. In PostgreSQL, where do NULL values appear with ORDER BY city DESC (no NULLS clause)? A. First B. Last C. In random positions D. They are excluded from the result **Answer: A.** PostgreSQL treats NULL as larger than any value. Ascending order puts NULLs last, so descending order puts them first. Add NULLS LAST to change that. 4. How does PostgreSQL parse WHERE a OR b AND c? A. (a OR b) AND c B. Left to right with equal precedence C. a OR (b AND c) D. It is a syntax error without parentheses **Answer: C.** AND has higher precedence than OR, just as multiplication binds tighter than addition. If you meant (a OR b) AND c you must write the parentheses.