SQL and how Oracle filter the resultset
While reading the very interesting Oracle AI Database 26ai New Features Guide, I got to know that there is a new “QUALIFY” filter clause (there is also a new “FILTER” clause). However, what I found more interesting in the documentation was this sentence:
The order of operations is as follows:
FROM→WHERE→GROUP BY→HAVING→WINDOW→QUALIFY→DISTINCT→ORDER BY→FETCH FIRST.
This is somehow a back to the roots and basic SQL information that I had in the back of my head, but never though really end-to-end about.
Here an example of query using all except DISTINCT elements of the operations order:
SELECT region, city, AVG(temperature), AVG(AVG(temperature)) OVER w AS avg_temp_region
FROM city, regions
WHERE city.region_id = region.region_id
GROUP BY region, city
HAVING region NOT LIKE 'LISBON'
WINDOW w AS (PARTITION BY region)
QUALIFY avg_temp_region > 10
ORDER BY region
FETCH FIRST 5 ROWS ONLY;
I don’t think I could write a SQL like that, but at least now I know how it looks like.