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

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)

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;

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 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.)

Which databases support FILTER?

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.

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

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, and the full syntax list is in our 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.