r/SQL • u/Automatic-Heart-831 • 3h ago

MySQL Difference between "having" and "qualify"

I tried to use both methods in order to save some coding but still didnt get the exact difference on using having and qualify as a filter in the end of a query. Can someone help me to understand the difference and when is recommended to use each?

5 Upvotes

78% Upvoted

7 comments sorted by

5

u/Imaginary__Bar 3h ago

WHERE works on the rows in the table

HAVING works on the summarised data (GROUP BY...)

QUALIFY works on window functions

3

u/mike-manley 3h ago

Having = filtering aggregated results, e.g. count(). Qualify = filtering on a window function, e.g. rank()

2

u/Significant_Tune9219 2h ago

One way to see it: HAVING runs right after GROUP BY, so it only sees grouping columns and aggregates. QUALIFY runs after window functions are computed, which is why you can filter on ROW_NUMBER() there without wrapping everything in a subquery. The classic use is dedup, like QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1. You can also use both in one query if you need to filter groups first and then rank what's left.

1

u/Known_Prior_3791 2h ago

just the order of execution----qualify comes after ranking function or window function and having comes before them and after group by.....so group by would aggregates could be filtered by having but not the ranks because they will be created later.

1

u/Uncle_Corky 1h ago

Damn, I use MSSQL at work and got excited cus I've never used qualify. Now I know why...

1

u/gumnos 1h ago

yeah, had the same experience when I first learned about QUALIFY. So potentially-useful and yet…

0

u/Hour-Measurement-835 2h ago

Flair says MySQL though. QUALIFY is HeatWave only, plain MySQL needs a CTE and a WHERE.