Filtering Rows in Databricks SQL: WHERE, HAVING, QUALIFY and Query Filters

1.WHERE: keeping only the rows you need

Filtering means passing rows through a condition and keeping the ones that match. The WHERE clause limits the rows coming out of a query's FROM clause (or a subquery's) to those that meet the condition you give it. That condition is any expression that returns a BOOLEAN. You can combine several conditions with AND or OR. The documented examples cover the usual tools: comparison operators (id > 200), logical combinations (id = 200 OR id = 300), IS NULL tests, function calls on a column (length(name) > 3), BETWEEN ranges, and even subqueries.

-- `BETWEEN` expression in `WHERE` clause.
SELECT * FROM person WHERE id BETWEEN 200 AND 300 ORDER BY id;

Missing values need care. In the documented example, WHERE id > 300 OR age IS NULL returns Dan (id 400) and Mary, whose age is NULL. The IS NULL test is what keeps Mary's row. A subquery works too, in two forms. A scalar subquery, as in age > (SELECT avg(age) FROM person), compares each row with a value computed over the whole table. A correlated EXISTS subquery checks a condition row by row against a related query.

> SELECT * FROM person WHERE id > 300 OR age  ?  NULL ORDER BY id;

Sources1

2.When WHERE refuses: filtering aggregates with HAVING

WHERE runs before any grouping, so it sees individual rows and can't test an aggregate, a window function or a generator function. Filtering on a total, count or maximum is the job of HAVING. It filters the rows that GROUP BY produces, after the grouping is done. A HAVING expression may refer only to constants, expressions that appear in GROUP BY, and aggregate functions. Any other column raises MISSING_AGGREGATION. In those limits it's flexible. It can use an aggregate's alias from the SELECT list. It can use a different aggregate from the ones being selected, as in HAVING max(quantity) > 15. You can even write HAVING with no GROUP BY, which treats the whole table as one group (a global aggregate).

-- Aggregate functions are not allowed in `WHERE`.
> SELECT * FROM person WHERE max(age) > 10;

-- `HAVING` clause referring to aggregate function by its alias.
> SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum > 15;

Sources23

3.Where each filter sits in a query, and QUALIFY

The SELECT grammar lists the clauses in a fixed order: FROM, then WHERE, GROUP BY, HAVING and QUALIFY. That order follows the data. WHERE filters the result of the FROM clause, GROUP BY groups the rows that survive, and HAVING filters those groups. QUALIFY is the last filter. It filters the results of window functions, and it needs at least one window function in either the SELECT list or the QUALIFY clause. It can't contain aggregate functions, and it's available in Databricks SQL and Databricks Runtime 10.4 LTS and above.

SELECT city, car_model
FROM dealer
QUALIFY RANK() OVER (PARTITION BY car_model ORDER BY quantity) = 1;

Sources43

4.Query filters in the results panel

The Databricks SQL editor also lets you filter without changing the SQL. After running a query, in the Results panel, click + and then select Filter. Choose a column (strings, numbers and dates are supported) and a filter type: Single Select, Multi Select, Text Input (Contains, Exact Match or Starts With), or a date picker. Unlike WHERE, a query filter limits data after the query has run. That makes it a good fit when running the query again is slow or costly. Two limits follow from this. A filter can be applied only to columns the query returned. And its list of options comes only from the returned rows, so a LIMIT 1000 query offers only the values in those 1000 rows. The dropdown is capped at 64k unique values. Above that, Databricks recommends a Text parameter.

Sources5

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.You can filter on an aggregate in WHERE, for example WHERE max(age) > 10.Why is that wrong?

   Aggregate, window and generator functions in WHERE raise INVALID_WHERE_CONDITION. Use HAVING after GROUP BY, or compare against a scalar subquery.

   Covered in When WHERE refuses: filtering aggregates with HAVING

2. 2.A results-panel query filter can filter on any column of the underlying table, like WHERE.Why is that wrong?

   Query filters apply after the query has run, and only to the columns it returned.

   Covered in Query filters in the results panel

Practise it for real

In the SQL editor, filter and sort the documented person table, and see each clause change the result.

1. 1.Run: CREATE TABLE person (id INT, name STRING, age INT); then INSERT INTO person VALUES (100, 'John', 30), (200, 'Mary', NULL), (300, 'Mike', 80), (400, 'Dan', 50);

   Why: This gives you a small table with one NULL age to filter and sort.

   You should see: The table is created with four rows.

2. 2.Run: SELECT * FROM person WHERE id > 200 ORDER BY id;

   Why: A comparison in WHERE keeps only matching rows, and ORDER BY fixes their order.

   You should see: Two rows: 300 Mike 80, then 400 Dan 50.

3. 3.Run: SELECT * FROM person WHERE id > 300 OR age IS NULL ORDER BY id;

   Why: OR combined with IS NULL keeps rows that have no age.

   You should see: Two rows: 200 Mary NULL, then 400 Dan 50.

4. 4.Run: SELECT * FROM person WHERE max(age) > 10;

   Why: This confirms that WHERE rejects aggregates.

   You should see: The query fails with INVALID_WHERE_CONDITION.