---
title: "Filter in SQL: WHERE, HAVING and FILTER Reference"
description: "To filter in SQL, use WHERE to drop rows before grouping, HAVING to drop groups after aggregation, and FILTER (WHERE ...) to restrict the rows one aggregate…"
canonical: "https://www.aifordatabase.com/blog/filter-in-sql/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-10-26
updated: 2026-10-26
---

# Filter in SQL: WHERE, HAVING and FILTER Reference

**TL;DR:** To filter in SQL, use `WHERE` to drop rows before grouping, `HAVING` to drop groups after aggregation, and `FILTER (WHERE ...)` to restrict the rows one aggregate sees. FILTER works in PostgreSQL, SQLite and DuckDB; in MySQL and SQL Server you write the same thing with `CASE`.

Filtering in SQL means telling the database which rows (or groups) belong in the answer. There are three places to do it, and they run at different moments of the query: `WHERE` is evaluated before `GROUP BY`, `HAVING` after aggregates are computed, and the `FILTER` clause inside a single aggregate call, so one query can count all orders and only paid orders side by side. Pick by asking "am I removing rows, removing groups, or splitting one metric?"

## Filter in SQL: the lookup table

| You want to | Use | Runs | Example |
| --- | --- | --- | --- |
| Keep only some rows | `WHERE` | Before grouping | `WHERE status = 'paid'` |
| Keep only some groups | `HAVING` | After aggregation | `HAVING COUNT(*) > 5` |
| Restrict one aggregate | `FILTER (WHERE ...)` | Per aggregate | `COUNT(*) FILTER (WHERE status = 'paid')` |
| Same, portable | `CASE` inside aggregate | Per aggregate | `SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)` |
| Match a list | `IN` / `NOT IN` | In WHERE | `WHERE country IN ('IN', 'US')` |
| Match a range | `BETWEEN` | In WHERE | `WHERE amount BETWEEN 10 AND 100` |
| Match a pattern | `LIKE` / `ILIKE` | In WHERE | `WHERE email LIKE '%@gmail.com'` |
| Find missing values | `IS NULL` | In WHERE | `WHERE refunded_at IS NULL` |
| Cap the output | `LIMIT` / `TOP` | Last | `LIMIT 10` |

![SQL filters run in order: WHERE on rows, then GROUP BY, then HAVING on groups, then LIMIT](https://cdn.sanity.io/images/upsr56wu/production/bfbe9be58d27880c2826ed8097e0aadb53956605-1344x326.png?w=1280)

FIG 01 — WHERE cannot see aggregates; HAVING can.

All examples below use one small table:

```
CREATE TABLE orders (
  id         int,
  customer   text,
  status     text,     -- 'paid', 'refunded', or NULL while pending
  amount     numeric
);

INSERT INTO orders VALUES
  (1, 'Ana', 'paid', 40),
  (2, 'Ana', 'refunded', 25),
  (3, 'Ben', 'paid', 90),
  (4, 'Ben', NULL, 15),
  (5, 'Cai', 'paid', 10);
```

## What does WHERE filter?

`WHERE` filters individual rows before any grouping happens. A row stays only if the condition evaluates to true; false and unknown (NULL) both drop it.

```
SELECT id, customer, amount
FROM orders
WHERE status = 'paid' AND amount >= 20;
-- returns orders 1 (Ana, 40) and 3 (Ben, 90)
```

Combine conditions with `AND`, `OR` and `NOT`, and use parentheses when you mix them: `WHERE status = 'paid' AND (customer = 'Ana' OR customer = 'Ben')`. Without the parentheses `AND` binds first and you get a different answer.

## What is the difference between WHERE and HAVING?

`WHERE` removes rows before `GROUP BY`; `HAVING` removes groups after the aggregates are computed. The PostgreSQL manual states the reason directly: an aggregate can only appear in the select list or `HAVING`, because `WHERE` is evaluated before aggregates exist. So `WHERE SUM(amount) > 50` is an error, and `HAVING SUM(amount) > 50` is correct.

```
SELECT customer, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'          -- row filter
GROUP BY customer
HAVING SUM(amount) > 30;       -- group filter
-- Ana 40, Ben 90 (Cai's 10 is dropped by HAVING)
```

![WHERE keeps paid rows, then HAVING drops Cai whose paid total is only 10](https://cdn.sanity.io/images/upsr56wu/production/e2ba85026433800e10652fd908a5c242136dafe2-1344x410.png?w=1280)

FIG 02 — Cai survives WHERE but not HAVING.

Rule of thumb: if a condition does not mention an aggregate, put it in `WHERE`. It shrinks the data before grouping and is usually cheaper.

## What is the SQL FILTER clause?

The FILTER clause restricts which input rows a single aggregate function receives: `aggregate(...) FILTER (WHERE condition)`. Rows outside the condition are discarded for that aggregate only; the rest of the query still sees them. It is part of the SQL standard and arrived in PostgreSQL 9.4.

```
SELECT
  customer,
  COUNT(*)                                   AS orders,
  COUNT(*) FILTER (WHERE status = 'paid')    AS paid_orders,
  SUM(amount) FILTER (WHERE status = 'paid') AS paid_revenue
FROM orders
GROUP BY customer;
```

| customer | orders | paid\_orders | paid\_revenue |
| --- | --- | --- | --- |
| Ana | 2 | 1 | 40 |
| Ben | 2 | 1 | 90 |
| Cai | 1 | 1 | 10 |

This is the reason FILTER exists: a `WHERE status = 'paid'` would have removed the refunded and pending rows from `orders` too, so you could not show both counts in one pass. It is the cleanest way to build pivot-style reports; see our [SQL pivot table guide](https://www.aifordatabase.com/blog/sql-pivot-table/) for wider layouts.

In PostgreSQL, FILTER also works on aggregates used as window functions:

```
SELECT id, customer,
       COUNT(*) FILTER (WHERE status = 'paid') OVER (PARTITION BY customer) AS paid_for_customer
FROM orders;
```

(More on windows in [SQL PARTITION BY](https://www.aifordatabase.com/blog/sql-partition-by/).)

## Which databases support FILTER?

| Database | `FILTER (WHERE ...)` on aggregates | What to use otherwise |
| --- | --- | --- |
| PostgreSQL | Yes, since 9.4 | n/a |
| SQLite | Yes, since 3.30.0 | n/a |
| DuckDB | Yes | n/a |
| MySQL / MariaDB | No | `SUM(CASE WHEN ... THEN 1 ELSE 0 END)` or `SUM(cond)` |
| SQL Server | No | `SUM(CASE WHEN ... THEN 1 ELSE 0 END)` |
| Oracle | No | `CASE` inside the aggregate |

Pages calling something "the MySQL FILTER clause" mean WHERE, HAVING and LIMIT; DataCamp's own MySQL page notes FILTER is not a standalone MySQL clause. The portable rewrite:

```
-- MySQL, SQL Server, Oracle: same result as the FILTER query above
SELECT
  customer,
  COUNT(*)                                             AS orders,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)     AS paid_orders,
  SUM(CASE WHEN status = 'paid' THEN amount END)       AS paid_revenue
FROM orders
GROUP BY customer;
```

`SUM(CASE WHEN ... THEN amount END)` leaves the ELSE out on purpose: non-matching rows become NULL, and SUM ignores NULLs, which matches FILTER. If no row matches, both versions return NULL, not 0; wrap in `COALESCE(..., 0)` if your report needs a zero. For more CASE patterns see [PostgreSQL CASE WHEN](https://www.aifordatabase.com/blog/postgresql-case-when/).

## How do NULLs affect filters?

NULL means unknown, and any comparison with it (`=`, `<>`, `<`) yields unknown, which `WHERE`, `HAVING` and `FILTER` all treat as "drop". That causes four classic surprises:

1.  **\`<>\` skips NULLs.** `WHERE status <> 'refunded'` returns orders 1, 3, 5, not the pending order 4. Write `WHERE status IS DISTINCT FROM 'refunded'` (PostgreSQL, SQLite via `IS NOT`) or `WHERE status <> 'refunded' OR status IS NULL`.
2.  **\`= NULL\` never matches.** Use `IS NULL` / `IS NOT NULL`.
3.  **\`NOT IN\` with a NULL in the list returns nothing.** `WHERE customer NOT IN (SELECT customer FROM blocked)` returns zero rows if `blocked` holds one NULL. Use `NOT EXISTS` instead.
4.  **\`COUNT(col)\` vs \`COUNT(\*)\`.** `COUNT(status)` counts 4 rows here, `COUNT(*)` counts 5, because COUNT of a column skips NULLs.

```
-- pending orders are invisible to <> but visible to IS DISTINCT FROM
SELECT COUNT(*) FROM orders WHERE status <> 'refunded';                 -- 3
SELECT COUNT(*) FROM orders WHERE status IS DISTINCT FROM 'refunded';   -- 4
```

![The pending order with NULL status is dropped by not-equals but kept by IS DISTINCT FROM](https://cdn.sanity.io/images/upsr56wu/production/55b8381c821285260f912aa1379f241a27f09a1c-1344x532.png?w=1280)

FIG 03 — NULL compared with anything is unknown, so <> drops it.

## Other row filters: IN, BETWEEN, LIKE, LIMIT

```
SELECT * FROM orders WHERE customer IN ('Ana', 'Cai');      -- list
SELECT * FROM orders WHERE amount BETWEEN 15 AND 40;        -- inclusive both ends
SELECT * FROM orders WHERE customer LIKE 'B%';              -- pattern; ILIKE is case-insensitive in PostgreSQL
SELECT * FROM orders ORDER BY amount DESC LIMIT 2;          -- SQL Server: SELECT TOP 2 ...
```

`BETWEEN` includes both endpoints, which bites with timestamps: `BETWEEN '2026-10-01' AND '2026-10-31'` misses everything after midnight on the 31st. Use `>= '2026-10-01' AND < '2026-11-01'`. Filtering out duplicate rows is its own topic, covered in [find duplicates in SQL](https://www.aifordatabase.com/blog/find-duplicates-sql-query/), and the full syntax list is in our [SQL cheat sheet](https://www.aifordatabase.com/blog/sql-cheat-sheet/).

## When the filter is the hard part

Most real questions are filters stacked three deep: "paid revenue by customer, last 30 days, only customers with more than five orders, excluding refunds". Getting WHERE vs HAVING vs FILTER and the NULL rules right every time is where hand-written reports go wrong. If your team asks these questions often, AI for Database lets them ask in plain English, shows the generated SQL so you can check which filter it used, and can pin the answer to a self-refreshing dashboard. Try AI for Database free at aifordatabase.com.

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### Can I use WHERE and HAVING in the same query?

Yes. WHERE filters rows first, then GROUP BY groups what is left, then HAVING filters the groups. Using both is common and usually faster than putting every condition in HAVING.

### Can I use an alias in WHERE?

No in most databases, because WHERE runs before the SELECT list is computed. Repeat the expression or wrap the query in a subquery or CTE. MySQL and SQLite allow select aliases in HAVING.

### Is FILTER faster than CASE?

They express the same logic, and the planner does similar work for both. Choose FILTER for readability where it is supported and CASE where you need portability.

### Can HAVING be used without GROUP BY?

Yes. Without GROUP BY the whole table is one group, so SELECT COUNT() FROM orders HAVING COUNT() > 3 returns one row or none.

### How do I filter on a count, like customers with more than 5 orders?

Group by the customer and put the count in HAVING: GROUP BY customer HAVING COUNT() > 5. WHERE cannot reference COUNT.

### Does LIMIT filter rows?

It caps how many rows are returned after sorting, but it does not test a condition. Always pair it with ORDER BY, or the rows you get are arbitrary. Sources: \[PostgreSQL value expressions (aggregate FILTER)\](https://www.postgresql.org/docs/current/sql-expressions.html), \[PostgreSQL 9.4 release notes\](https://www.postgresql.org/docs/9.4/release-9-4.html), \[SQLite aggregate functions\](https://sqlite.org/lang\_aggfunc.html), \[DuckDB FILTER clause\](https://duckdb.org/docs/stable/sql/query\_syntax/filter), \[MySQL 8.4 aggregate functions\](https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html).

Read next

*   [SQL CHEAT SHEET: COMMANDS, CLAUSE ORDER AND DIALECTS →](https://www.aifordatabase.com/blog/sql-cheat-sheet/)
*   [POSTGRES TIMESTAMPTZ: WHAT IT STORES AND WHEN TO USE IT →](https://www.aifordatabase.com/blog/postgres-timestamptz/)
*   [HOW TO FIND DUPLICATES IN SQL (AND DELETE THEM SAFELY) →](https://www.aifordatabase.com/blog/find-duplicates-sql-query/)

Source: https://www.aifordatabase.com/blog/filter-in-sql/