WHERE vs HAVING vs QUALIFY: SQL filter clauses explained

The order SQL evaluates the three filters

You write SELECT first, but the database does not run it first. BigQuery’s query syntax reference gives the typical evaluation order (checked October 2026):

1. FROM
2. WHERE
3. GROUP BY and aggregation
4. HAVING
5. WINDOW
6. QUALIFY
7. DISTINCT
8. ORDER BY
9. LIMIT

This list answers most “why does my filter not work” questions. WHERE runs at step 2, when no sum or count exists yet, so it cannot filter on an aggregate. HAVING runs at step 4, before window functions are computed, so it cannot filter on a rank or a row number. QUALIFY runs at step 6 and can see both.

One dataset through all three

Every query below uses the same six orders, declared inline so you can paste and run it anywhere:

WITH orders AS (
  SELECT 1 AS order_id, 'ana' AS customer,
    DATE '2026-09-01' AS order_date,
    'paid' AS status, 120 AS amount
  UNION ALL SELECT 2, 'ana',
    DATE '2026-09-14', 'paid', 80
  UNION ALL SELECT 3, 'ana',
    DATE '2026-09-20', 'refunded', 200
  UNION ALL SELECT 4, 'ben',
    DATE '2026-09-03', 'paid', 40
  UNION ALL SELECT 5, 'ben',
    DATE '2026-09-25', 'paid', 30
  UNION ALL SELECT 6, 'cy',
    DATE '2026-09-09', 'paid', 500
)
SELECT * FROM orders;

Keep the WITH orders AS (...) block and replace the last line with each query that follows.

WHERE: filter rows before anything else

WHERE keeps the rows for which the condition is true and discards the rest, including rows where the condition is NULL.

SELECT order_id, customer, amount
FROM orders
WHERE status = 'paid';

Result: five rows. Ana’s refunded order is gone before any grouping happens, which is what you want: a refund should not be counted and then filtered out.

Two limits to remember:

• WHERE can only reference columns from the FROM clause. In BigQuery it cannot use an alias defined in the SELECT list.
• WHERE SUM(amount) > 100 is an error. The sum does not exist at that step.

WHERE is also the filter that saves money. It reduces the rows every later step has to process, and on a partitioned table a filter on the partition column reduces the data scanned.

HAVING: filter groups after aggregation

HAVING filters the output of GROUP BY. It is evaluated once per aggregated row.

SELECT customer, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) > 100;

Result: two rows, ana with 200 and cy with 500. Ben’s paid orders add up to 70, so his group is removed.

Notice that both filters are in the query and each does a different job: WHERE removed the refunded order, HAVING removed the small customer. In BigQuery you can also write HAVING total > 100, because HAVING can reference SELECT aliases.

If you came here from a quiz: the clause that filters a result set on a condition applied to an aggregate function such as COUNT is HAVING. The clause that filters individual records is WHERE.

QUALIFY: filter on a window function

A window function such as ROW_NUMBER() or RANK() computes a value for each row across a set of related rows, without collapsing them. QUALIFY filters on that value. Snowflake’s documentation puts it in one line: QUALIFY does with window functions what HAVING does with aggregate functions.

The most common use is the latest row per group:

SELECT customer, order_id, order_date, amount
FROM orders
WHERE status = 'paid'
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer
  ORDER BY order_date DESC
) = 1;

Result: three rows, one per customer: order 2 for ana, order 5 for ben, order 6 for cy.

The window function can sit in the QUALIFY clause, as above, or in the SELECT list with an alias that QUALIFY then references. BigQuery requires a window function in one of the two places.

The same query without QUALIFY

Without QUALIFY you compute the window function in a subquery and filter outside it:

SELECT customer, order_id, order_date, amount
FROM (
  SELECT
    customer, order_id, order_date, amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer
      ORDER BY order_date DESC
    ) AS rn
  FROM orders
  WHERE status = 'paid'
)
WHERE rn = 1;

It returns the same three rows. QUALIFY is shorter and keeps the filter next to the logic it belongs to, which matters when someone else has to read the query. For more on the functions themselves, see window functions in BigQuery and numbering functions.

Which databases support QUALIFY

Not every database has QUALIFY. These warehouses document it, each on its own reference page, checked October 2026:

• Google BigQuery: QUALIFY clause.
• Snowflake: QUALIFY.
• Databricks: QUALIFY clause, in Databricks SQL and Databricks Runtime 10.4 LTS and above.
• AWS Redshift: QUALIFY clause. If it follows FROM directly, the table needs an alias.

If your database’s SELECT reference has no QUALIFY, use the subquery form above. It runs everywhere window functions do.

All three in one query

Which customer with at least two paid orders spent the most?

SELECT
  customer,
  SUM(amount) AS total,
  RANK() OVER (ORDER BY SUM(amount) DESC) AS rnk
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING COUNT(*) >= 2
QUALIFY rnk = 1;

Result: one row, ana with 200. Read it in evaluation order:

1. WHERE drops the refunded order. Five rows remain.
2. GROUP BY makes three groups: ana 200, ben 70, cy 500.
3. HAVING drops cy, who has one order. Two groups remain.
4. The window function ranks what is left: ana first, ben second.
5. QUALIFY keeps rank 1.

Cy has the largest total and still does not appear, because HAVING removed that group before the ranking was computed. A filter in the wrong clause does not raise an error. It returns a different, plausible answer. That is the reason to know the order.

Common errors and what they mean

An aggregate in WHERE. WHERE COUNT(*) > 1 fails because aggregation has not happened yet. Move the condition to HAVING.

HAVING without aggregation. BigQuery requires GROUP BY or an aggregate somewhere in the query for HAVING to be valid. A row-level condition belongs in WHERE.

A window function in WHERE or HAVING. WHERE ROW_NUMBER() OVER (...) = 1 fails because window functions are computed after both. Use QUALIFY, or a subquery.

QUALIFY with no window function. BigQuery, Snowflake and Databricks all require a window function in the SELECT list or in the QUALIFY clause.

A row-level filter left in HAVING. HAVING status = 'paid' can work when status is in GROUP BY, but it filters after the database has grouped every row. Put it in WHERE so the grouping has less to do.

Forgetting what NULL does. All three clauses keep only rows where the condition is TRUE. A comparison with NULL is neither true nor false, so the row is dropped. If missing values should survive, handle them explicitly with IS NULL or COALESCE.

Two QUALIFY patterns worth keeping

Find duplicates, and keep all of their rows:

SELECT *
FROM orders
QUALIFY COUNT(*) OVER (
  PARTITION BY customer
) > 1;

This returns the five orders of ana and ben. A GROUP BY ... HAVING COUNT(*) > 1 would return only the two customer names.

Top N per group:

SELECT customer, order_id, amount
FROM orders
QUALIFY RANK() OVER (
  PARTITION BY customer
  ORDER BY amount DESC
) <= 2;

Use RANK() when ties should all be kept, ROW_NUMBER() when you need exactly N rows.

Why this matters past the query editor

The all-three query above is the kind that gets copied. Someone changes HAVING COUNT(*) >= 2 to >= 1 for one request, someone else moves the status filter, and three weeks later two reports show different “top customers” with nothing to say which is correct. The filter clauses are where a metric’s definition actually lives: paid only, at least two orders, latest row per customer.

So once a query like this feeds a report, write it down once and name its owner. In OWOX Data Marts that is a Data Mart: your SQL, saved with a description and an owner, run in your warehouse and delivered as a report to the spreadsheet or dashboard where it is read. OWOX does not rewrite the query or choose the filters. The definition stays yours, and it stays in one place.

Google BigQueryComprehensive Guide to BigQuery Aggregate Functions for 2025April 18, 2024→Google BigQueryBigQuery conversion functions: every type-to-type conversionOctober 6, 2026→Google BigQueryBigQuery DML Commands: A Complete Guide for Data AnalystsMarch 14, 2024→

See all articles →