---
title: "WHERE vs HAVING vs QUALIFY in SQL: which filter to use"
canonical: "https://www.owox.com/blog/articles/bigquery-sql-where-vs-having-vs-qualify"
updated: "2026-10-12"
---

# WHERE vs HAVING vs QUALIFY: SQL filter clauses explained

WHERE filters rows before grouping, HAVING filters groups after aggregation, and QUALIFY filters the results of window functions. This guide runs one small dataset through all three, shows the order SQL evaluates them in, and lists the errors each one causes.

[Google BigQuery](/blog/topics/bigquery) · Updated October 6, 2026 · [Alyona Samovar](/team/alyona-samovar) · [Vadym Kramarenko](/team/vadym-kramarenko) · 7 min read

![WHERE vs HAVING vs QUALIFY: SQL filter clauses explained](https://cdn.owox.ai/www/webflow/696a4e539823bf6001514078_WHERE-vs.-HAVING-vs.-QUALIFY.png/public)

**WHERE** filters rows before they are grouped. **HAVING** filters groups after aggregation. **QUALIFY** filters rows by the result of a window function. They are not interchangeable, because each one runs at a different step of the query.

| Clause | Filters | Runs | Can use |
| --- | --- | --- | --- |
| `WHERE` | individual rows | before `GROUP BY` | columns |
| `HAVING` | groups | after aggregation | aggregates such as `SUM()` |
| `QUALIFY` | rows, by a window result | after window functions | window functions such as `ROW_NUMBER()` |

The rest of this guide runs one small table through all three, so you can see what each clause removes and when.

**_Note:_** _Originally published in April 2024. Fully updated in October 2026: every query was re-run in BigQuery, and the QUALIFY section now lists which warehouses document the clause._

## The order SQL evaluates the three filters

You write `SELECT` first, but the database does not run it first. BigQuery’s [query syntax reference](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax) 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.

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## 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](/blog/articles/bigquery-partitioned-tables) 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](/blog/articles/bigquery-window-functions) and [numbering functions](/blog/articles/bigquery-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](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause).
*   **Snowflake**: [QUALIFY](https://docs.snowflake.com/en/sql-reference/constructs/qualify).
*   **Databricks**: [QUALIFY clause](https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-qualify), in Databricks SQL and Databricks Runtime 10.4 LTS and above.
*   **AWS Redshift**: [QUALIFY clause](https://docs.aws.amazon.com/redshift/latest/dg/r_QUALIFY_clause.html). 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](/blog/articles/coalesce-operator-bigquery).

## 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](https://www.owox.com/app-signup) 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](/product/spreadsheets) or [dashboard](/product/dashboards) where it is read. OWOX does not rewrite the query or choose the filters. The definition stays yours, and it stays in one place.

![Data Setup tab of the Unified Ad Spend Data Mart in OWOX Data Marts, defined as SQL on Google BigQuery, validated, with its scan size shown – the filters that define the metric are written once, in one query](https://cdn.owox.ai/www/content-marketing-article/bigquery-sql-where-vs-having-vs-qualify/screens/1.png/public)

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

Which SQL clause is used to filter records?

WHERE filters individual records before any grouping. To filter a result set on a condition applied to an aggregate function such as COUNT or SUM, use HAVING. To filter on the result of a window function such as ROW\_NUMBER, use QUALIFY.

When should you use WHERE instead of HAVING?

Whenever the condition is about a single row. A row-level filter in WHERE removes rows before grouping, so every later step processes less data. The same condition in HAVING is applied only after the database has grouped everything.

Which databases support the QUALIFY clause?

Google BigQuery, Snowflake, Databricks (Databricks SQL and Runtime 10.4 LTS and above) and AWS Redshift each document a QUALIFY clause, as of October 2026. Where a database has none, compute the window function in a subquery and filter it with WHERE in the outer query.

What are common errors with WHERE, HAVING and QUALIFY?

Putting an aggregate in WHERE, putting a window function in WHERE or HAVING, using HAVING with no aggregation in the query, and using QUALIFY with no window function. The quieter one is a filter in the wrong clause that raises no error and returns a different answer.

In what order does SQL evaluate WHERE, HAVING and QUALIFY?

BigQuery's reference gives the typical order as FROM, WHERE, GROUP BY and aggregation, HAVING, WINDOW, QUALIFY, DISTINCT, ORDER BY, LIMIT. WHERE therefore runs before aggregates exist, and HAVING runs before window functions are computed.

What does QUALIFY do in SQL?

QUALIFY filters rows by the result of a window function. It runs after window functions are computed, so \`QUALIFY ROW\_NUMBER() OVER (PARTITION BY customer ORDER BY order\_date DESC) = 1\` returns the latest row per customer without a subquery.

What is the difference between WHERE and HAVING in SQL?

WHERE filters rows before GROUP BY runs, so it cannot reference an aggregate. HAVING filters groups after aggregation, so it can. A query can use both: WHERE to drop rows that should never be counted, HAVING to drop groups whose total does not meet a condition.

## Who wrote this

![Alyona Samovar](https://cdn.owox.ai/www/webflow/6799099d337aa8a997b03b56_Alyona-Samovar.png/public)

[Alyona Samovar](/team/alyona-samovar) · Senior Digital Analyst

Alyona Samovar is a Senior Digital Analyst who spent years at OWOX helping enterprise clients design analytics systems, build BigQuery reporting pipelines, and optimize marketing measurement. She specializes in SQL-based analytics, data modeling, and turning raw marketing data into actionable insights. Alyona writes about practical analytics workflows, BigQuery best practices, and data-driven decision-making.

![Vadym Kramarenko](https://cdn.owox.ai/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public)

[Vadym Kramarenko](/team/vadym-kramarenko) · Growth Marketing Manager

Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks.

[LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko)

[Google BigQuery](/blog/topics/bigquery)

## Related articles

[Google BigQuery · Comprehensive Guide to BigQuery Aggregate Functions for 2025 · April 18, 2024](/blog/articles/bigquery-aggregate-functions)

[Google BigQuery · BigQuery conversion functions: every type-to-type conversion · October 6, 2026](/blog/articles/bigquery-conversion-functions)

[Google BigQuery · BigQuery DML Commands: A Complete Guide for Data Analysts · March 14, 2024](/blog/articles/bigquery-data-manipulation-language)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google BigQuery](https://www.owox.com/blog/topics/bigquery) — /blog/topics/bigquery.md
- [Alyona Samovar](https://www.owox.com/team/alyona-samovar) — /team/alyona-samovar.md
- [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [partitioned table](https://www.owox.com/blog/articles/bigquery-partitioned-tables) — /blog/articles/bigquery-partitioned-tables.md
- [window functions in BigQuery](https://www.owox.com/blog/articles/bigquery-window-functions) — /blog/articles/bigquery-window-functions.md
- [numbering functions](https://www.owox.com/blog/articles/bigquery-numbering-functions) — /blog/articles/bigquery-numbering-functions.md
- [COALESCE](https://www.owox.com/blog/articles/coalesce-operator-bigquery) — /blog/articles/coalesce-operator-bigquery.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [spreadsheet](https://www.owox.com/product/spreadsheets) — /product/spreadsheets.md
- [dashboard](https://www.owox.com/product/dashboards) — /product/dashboards.md
- [Google BigQuery · Comprehensive Guide to BigQuery Aggregate Functions for 2025 · April 18, 2024](https://www.owox.com/blog/articles/bigquery-aggregate-functions) — /blog/articles/bigquery-aggregate-functions.md
- [Google BigQuery · BigQuery conversion functions: every type-to-type conversion · October 6, 2026](https://www.owox.com/blog/articles/bigquery-conversion-functions) — /blog/articles/bigquery-conversion-functions.md
- [Google BigQuery · BigQuery DML Commands: A Complete Guide for Data Analysts · March 14, 2024](https://www.owox.com/blog/articles/bigquery-data-manipulation-language) — /blog/articles/bigquery-data-manipulation-language.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md