How QUALIFY Works - With Examples

A quick introduction to "qualify" which can improve your query readability by a large margin if used correctly

Let’s assume that you have a table of employees with their salaries called tbl_employee and another table with departments, let’s call it tbl_department. Let’s have a quick look at both of these tables:

Table A: tbl_employee

Table B: tbl_department

Now if we come from the tbl_employee and left join it on department_id with tbl_department, it will look something like this:

select te.employee_id, te.department_id, te.salary, td.department_name
from tbl_employee as te
left join tbl_department as td on te.department_id = td.department_id

Now, if we want to find out our top earners from each deparment, we have usually do something like this:

select *
from (
select te.employee_id, te.department_id, te.salary, td.department_name,
 dense_rank() over (partition by department_id order by salary) as top_earners
from tbl_employee as te
left join tbl_department as td on te.department_id = td.department_id
) sub_query
where top_earners = 1

This query ranks the employees within each department based on their salary and selects those with the highest rank.

How to Use QUALIFY?

Instead of using a subquery (or CTE), you can simply use QUALIFY to select the top earners in one go. Here’s how:

select te.employee_id, te.department_id, te.salary, td.department_name
from tbl_employee as te
left join tbl_department as td on te.department_id = td.department_id
qualify dense_rank() over (partition by department_id order by salary) = 1

The QUALIFY clause directly uses the DENSE_RANK() function to filter the results based on the given condition. Remember, you cannot use such window functions in the WHERE clause. This is exactly where QUALIFY helps you write more readable and concise queries.

Using QUALIFY not only makes your query more readable but also makes it easier for your colleagues to understand and maintain, provided they are familiar with the QUALIFY clause. If they aren’t, this is a great opportunity to share this useful technique with them.

By using QUALIFY, you eliminate the need for a subquery, thus simplifying the overall structure of your SQL query. This can significantly improve the readability and maintainability of your SQL scripts, especially in complex reporting and data analysis scenarios.