• 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:

  1. FROM
  2. WHERE
  3. GROUP BY and aggregation
  4. HAVING (filters the results of aggregation)
  5. WINDOW
  6. QUALIFY (filters the results of window functions)
  7. DISTINCT
  8. ORDER BY
  9. 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_namejob_titlecitysalary
RohitSoftware EngineerDelhi500000
MeghaData ScientistBangalore800000
AnjaliHR ManagerBangalore400000

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_namejob_titlecitysalary
RohitSoftware EngineerDelhi500000
MeghaData ScientistBangalore800000
AnjaliHR ManagerBangalore400000

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_namejob_titlecitysalary
RohitSoftware EngineerDelhi500000
MeghaData ScientistBangalore800000
AnjaliHR ManagerBangalore400000

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

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.