# WHERE

> Filter rows with comparisons, patterns, ranges, and SQL's three-valued logic.

---

LLMS index: [llms.txt](/en/llms.txt)

---

`WHERE` is how you filter rows. The clause sits between `FROM` and `ORDER BY`, and PostgreSQL keeps only rows for which the condition evaluates to true. Anything that evaluates to false or `NULL` is dropped — this three-valued logic is one of SQL's quirks and the cause of many beginner bugs. Get comfortable with it early.

## What you'll learn {#learn}

- Filtering with comparison operators (`=`, `<>`, `<`, `>`, `<=`, `>=`)
- Pattern matching with `LIKE` (and `ILIKE` for case-insensitive)
- Combining conditions with `AND`, `OR`, `NOT`
- Membership and ranges: `IN`, `BETWEEN`
- Why `= NULL` does not work and what to use instead

## Example {#example}

```sql {num="4.1" caption="WHERE"}
-- Equality and ordering
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY tablename
LIMIT 3;

-- Pattern matching: % matches any sequence, _ matches one character
SELECT tablename
FROM pg_tables
WHERE tablename LIKE 'pg_a%'
ORDER BY tablename
LIMIT 5;

-- Case-insensitive: ILIKE (PostgreSQL extension)
SELECT tablename FROM pg_tables WHERE tablename ILIKE 'PG_Class' LIMIT 1;

-- Multiple conditions
SELECT tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
  AND tablename LIKE 'pg_%'
  AND tablename NOT LIKE 'pg_stat_%'
ORDER BY tablename
LIMIT 5;

-- IN: membership test
SELECT tablename
FROM pg_tables
WHERE tablename IN ('pg_class', 'pg_database', 'pg_namespace')
ORDER BY tablename;

-- BETWEEN: inclusive range
SELECT n FROM generate_series(1, 10) AS n WHERE n BETWEEN 3 AND 7;
```

`LIKE` is the SQL standard pattern operator: `%` matches any string of characters (including empty), `_` matches exactly one character. `ILIKE` is a PostgreSQL extension for case-insensitive matching. Index use depends on the pattern, collation, and operator class. Alphabetic prefixes generally need a suitable expression or trigram index; a B-tree can support some patterns beginning with characters unaffected by case. For more powerful matching, PostgreSQL also supports `~` (regex) and `~*` (case-insensitive regex), as well as full-text search (covered later).

`AND` binds tighter than `OR`, so `A AND B OR C` means `(A AND B) OR C`. When in doubt, add parentheses — your future self reading the query will thank you. `IN (...)` is shorthand for `= ANY (ARRAY[...])` and is equivalent to a chain of `OR`s.

## Run the example {#run}

To run:

```text
$ psql -f source/where.sql postgres
 schemaname |  tablename
------------+--------------
 pg_catalog | pg_aggregate
 pg_catalog | pg_am
 pg_catalog | pg_amop
(3 rows)
 ...
```

## Common pitfalls {#pitfalls}

- `= NULL` and `<> NULL` are never true (they are `NULL`). Use `IS NULL` and `IS NOT NULL`. This trips up engineers from every other language.
- `NOT IN (subquery)` becomes mysterious if the subquery contains a `NULL` — nonmatching values yield `NULL` while matching values yield false, so the filter keeps no rows. Prefer `NOT EXISTS` for nullable subqueries.
- `LIKE 'abc'` does not match `'ABC'`. Use `ILIKE` or apply `LOWER()` to both sides — but `LOWER(col) = 'abc'` defeats a plain index on `col` (add an expression index on `LOWER(col)` if you need it often).
- Be careful with `IN ('a', 'b', NULL)` — the `NULL` in the list contributes nothing useful and silently makes `NOT IN` behave unexpectedly.

> [!TIP] Tip
> When filtering text by prefix, `WHERE name LIKE 'foo%'` can use a B-tree index with a suitable collation or `text_pattern_ops` operator class. `WHERE name LIKE '%foo'` (suffix) or `'%foo%'` (substring) cannot — consider a trigram index (`pg_trgm` extension) for those.

> [!NOTE] Try it
> Change the condition to `WHERE schemaname = 'public'` — if you have no tables in `public` yet, you will get zero rows. Then try `WHERE tablename ~ '^pg_a.*'` to see regex matching.

## Downloads {#downloads}

SQL file: [where.sql](/examples/source/where.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)