Re: Proposal: QUALIFY clause

On Mon Jul 21, 2025 at 11:29 AM -03, Isaac Morland wrote:
> Is this different from using the window functions in a subquery and then
> applying a WHERE clause on the outer query?
>
> SELECT … FROM (SELECT … [including window functions] FROM …) WHERE [stuff
> that would be in QUALIFY]
>
> I'll be honest, I'm skeptical that we need another keyword that basically
> means “WHERE, but applied at a different point in the query processing”.
> I'm not even convinced that HAVING was a good idea (although obviously I
> would not propose removal).
>
Thanks for sharing your thoughts!

You're right — semantically, using QUALIFY is equivalent to wrapping the
query in a subquery and applying a WHERE clause to the result. The main
motivation here is to provide a more ergonomic and readable syntax.

While I understand the hesitation around introducing another keyword
that effectively acts like WHERE at a different stage, I believe QUALIFY
improves clarity in many use cases, by avoiding the boilerplate and
visual noise of nested subqueries making it easier to write and reason
about.

--
Matheus Alcantara