QUALIFY Clause in SQL

The QUALIFY clause filters the results of window functions.
This filtering of results is similar to how a HAVING clause filters the results of aggregate functions applied based on the GROUP BY clause.
A window function is required to be present either in the QUALIFY clause or the SELECT list.
The QUALIFY clause avoids the need for a subquery or WITH clause to perform this filtering (much like HAVING avoids a subquery).

The evaluation of a query with a QUALIFY clause is typically completed in this order:

FROM
WHERE
GROUP BY and aggregation
HAVING (filters the results of aggregation)
WINDOW
QUALIFY (filters the results of window functions)
DISTINCT
ORDER BY
LIMIT

Example

Lets create a table employee with the following data:

CREATE TABLE employee (
    emp_name STRING, 
    job_title STRING, 
    city STRING, 
    salary INT
);

INSERT INTO employee VALUES
    ('Amit', 'Software Engineer', 'Mumbai', 600000),
    ('Raja', 'Data Scientist', 'Mumbai', 850000),
    ('Suresh', 'HR Manager', 'Mumbai', 450000),
    ('Vikram', 'Software Engineer', 'Bangalore', 550000),
    ('Megha', 'Data Scientist', 'Bangalore', 800000),
    ('Anjali', 'HR Manager', 'Bangalore', 400000),
    ('Rohit', 'Software Engineer', 'Delhi', 500000),
    ('Priya', 'Data Scientist', 'Delhi', 750000),
    ('Kavita', 'HR Manager', 'Delhi', 420000);

Find Employees with Lowest Salary for Each Job Title

Using CTE:

WITH ranked_employees AS (
    SELECT
        emp_name,
        job_title,
        city,
        salary,
        RANK() OVER (PARTITION BY job_title ORDER BY salary) AS rank
    FROM employee
)
SELECT emp_name, job_title, city, salary
FROM ranked_employees
WHERE rank = 1;

Output:

emp_name	job_title	city	salary
Rohit	Software Engineer	Delhi	500000
Megha	Data Scientist	Bangalore	800000
Anjali	HR Manager	Bangalore	400000

Using Sub-query:

SELECT emp_name, job_title, city, salary
FROM (
    SELECT
        emp_name,
        job_title,
        city,
        salary,
        RANK() OVER (PARTITION BY job_title ORDER BY salary) AS rank
    FROM employee
) AS ranked_employees
WHERE rank = 1;

Output:

emp_name	job_title	city	salary
Rohit	Software Engineer	Delhi	500000
Megha	Data Scientist	Bangalore	800000
Anjali	HR Manager	Bangalore	400000

Using QUALIFY clause:

/* Window function in QUALIFY clause*/
SELECT emp_name, job_title, city, salary
FROM employee
QUALIFY RANK() OVER (PARTITION BY job_title ORDER BY salary) = 1;

Output:

emp_name	job_title	city	salary
Rohit	Software Engineer	Delhi	500000
Megha	Data Scientist	Bangalore	800000
Anjali	HR Manager	Bangalore	400000

Different ways to write the same query using QUALFIY clause to get the same result.

Window function in SELECT column list

SELECT emp_name, job_title, city, salary,
RANK() OVER (PARTITION BY job_title ORDER BY salary) AS emp_rank
FROM employee
QUALIFY emp_rank = 1;

Window function in the QUALIFY clause but using WINDOW clause

SELECT emp_name, job_title, city, salary
FROM employee
WINDOW w AS (PARTITION BY job_title ORDER BY salary)
QUALIFY RANK() OVER(w) = 1;

Window function in the SELECT clause but using WINDOW clause

SELECT emp_name, job_title, city, salary,
RANK() OVER (w) AS emp_rank
FROM employee
WINDOW w AS (PARTITION BY job_title ORDER BY salary)
QUALIFY emp_rank = 1;

QUALIFY is not part of the ANSI SQL standard. So it is not supported in all databases.

QUALFIY clause is a powerful feature in SQL that can be used to filter the results of window functions without the need for a subquery or WITH clause. It is a great way to simplify the SQL query and make it more readable. Since it is not part of the ANSI SQL standard, it is not supported in many databases like SQL server, spark. Currently supported in databricks, snowflake, bigquery, teradata and duckdb.

​Example

​Find Employees with Lowest Salary for Each Job Title

Example

Find Employees with Lowest Salary for Each Job Title