How to Use the QUALIFY Clause in Databricks SQL (Syntax & Examples)?

If you frequently write data transformation queries in Databricks, you probably use window functions to rank, row-number, or aggregate data. The QUALIFY clause in Databricks SQL is a massive time-saver that allows you to filter the results of those window functions directly without needing to wrap your query in a clunky subquery or Common Table Expression (CTE).

Page Contents

Introduction

What is the QUALIFY Clause in Databricks SQL?

Databricks QUALIFY Clause Syntax

Using QUALIFY with a Window Function in the SELECT List

Using QUALIFY with a Window Function Directly

QUALIFY vs WHERE vs HAVING

Conclusion

Introduction

Filtering data based on window functions used to be a frustrating multi-step process in SQL. Because window functions are evaluated after the WHERE and HAVING clauses, you couldn’t directly filter on a RANK() or ROW_NUMBER() in the same SELECT block. You had to create a CTE or subquery first, and then apply a WHERE clause to the outer query.

Fortunately, the Databricks QUALIFY clause solves this problem perfectly. Available in Databricks Runtime 10.4 LTS and above, this powerful clause filters the output of window functions seamlessly within a single query block, making your code cleaner and much easier to read.

What is the QUALIFY Clause in Databricks SQL?

The QUALIFY clause acts just like a HAVING clause, but it is specifically designed to filter the results of window functions.

To successfully use QUALIFY in Databricks SQL, you must have at least one window function present in either your SELECT list or directly within the QUALIFY statement itself. The clause accepts any boolean expression (an expression that evaluates to true or false) and supports logical operators like AND and OR to combine multiple conditions.

Databricks QUALIFY Clause Syntax

The syntax for using QUALIFY is straightforward. It is typically placed at the very end of your core query, right after your WHERE, GROUP BY, and HAVING clauses, but before ORDER BY or LIMIT.

SELECT column_name(s)
FROM table_name
[WHERE condition]
[GROUP BY column_name(s)]
[HAVING condition]
QUALIFY boolean_expression
[ORDER BY column_name(s)];

Important Note: The expressions you specify in the QUALIFY clause cannot contain standard aggregate functions unless they are part of a window function definition.

Setting Up Sample Data

To see how the Databricks window function filter works in practice, let’s create a quick sample table containing some car dealership inventory data.

CREATE TABLE dealer (id INT, city STRING, car_model STRING, quantity INT);

INSERT INTO dealer VALUES
    (100, 'Fremont', 'Honda Civic', 10),
    (100, 'Fremont', 'Honda Accord', 15),
    (100, 'Fremont', 'Honda CRV', 7),
    (200, 'Dublin', 'Honda Civic', 20),
    (200, 'Dublin', 'Honda Accord', 10),
    (200, 'Dublin', 'Honda CRV', 3),
    (300, 'San Jose', 'Honda Civic', 5),
    (300, 'San Jose', 'Honda Accord', 8);

Using QUALIFY with a Window Function in the SELECT List

The most common way to use QUALIFY is by referencing an alias that you have already defined in your SELECT statement.

In this SQL QUALIFY example, we are using the RANK() window function to rank car models based on their available quantity. We then immediately filter the query to only show the car model with the lowest quantity for each model type across all cities by setting QUALIFY rank = 1.

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

-- Returns:
-- city       car_model      rank
-- --------   ------------   ----
-- San Jose   Honda Accord   1
-- Dublin     Honda CRV      1
-- San Jose   Honda Civic    1

Using QUALIFY with a Window Function Directly

You don’t always have to clutter your output by including the window function in your SELECT list. If you just want to filter the data without seeing the actual rank or row number in your final table, you can declare the window function entirely inside the QUALIFY clause.

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

-- Returns:
-- city       car_model
-- --------   ------------
-- San Jose   Honda Accord
-- Dublin     Honda CRV
-- San Jose   Honda Civic

QUALIFY vs WHERE vs HAVING

Understanding when to use which filtering clause is crucial for mastering Databricks SQL syntax:

WHERE: Filters standard row-level data before aggregations or window functions take place.
HAVING: Filters grouped data after a GROUP BY aggregation.
QUALIFY: Filters data after window functions are evaluated.

Conclusion

The QUALIFY clause in Databricks SQL is an incredibly helpful feature that cuts down on redundant code, eliminates the need for unnecessary subqueries, and makes data engineering pipelines much easier to maintain. Whether you are deduplicating data with a ROW_NUMBER() or finding top performers with RANK(), leveraging QUALIFY is a modern SQL best practice every developer should adopt.