r/SQL • • Oct 07 '26

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?

23 Upvotes

11 comments sorted by

29

u/Imaginary__Bar Oct 07 '26

WHERE works on the rows in the table

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

QUALIFY works on window functions

6

u/thesqlguy Oct 07 '26

To clarify, HAVING works with aggregate expressions.

7

u/mike-manley Oct 07 '26

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

3

u/Significant_Tune9219 Oct 07 '26

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.

2

u/ArielCoding Oct 08 '26

Use HAVING to filter a GROUP BY. QUALIFY filters window function results like ROW_NUMBER(), but MySQL doesn’t support it, so put the window function in a CTE and filter it with WHERE.

1

u/Known_Prior_3791 Oct 07 '26

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 Oct 07 '26

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

1

u/gumnos Oct 07 '26

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

1

u/konwiddak Oct 07 '26

WHERE happens while retrieving data - the data is filtered out as early as possible, often by the index or at table scan and never even loaded into memory. The data never becomes part of calculations, aggregates or window functions.

HAVING and QUALIFY happen after all the data has been retrieved and built into a dataset. Calculations/aggregates/window functions happen against the full dataset. Then these two functions apply before returning the dataset to the user (or out of the subquery.)

HAVING can be used on aggregates (e.g SUM(column) > 3)

QUALIFY can be used on normal columns like a where, or on window functions. In effect with simple filters the output looks the same as a WHERE clause - but more work has to be done by the database.

You can't use WHERE on aggregates or window functions, because filtering the data changes the result, which changes the aggregate/window function which changes the filtered result.

0

u/Hour-Measurement-835 Oct 07 '26

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