Oracle AI database 26 ai introduced a new SQL verb "QUALIFY".
With this addition , the SQL can be concise , clear and readable.
I have seen it in Snowflake documentation and was hoping it will make it to newer release of Oracle and it did.
As it can be seen from the examples below , the QUALIFY clearly wins in renderig the SQL simple , concise.
I am using freesql.com ( formally known as livesql.oracle.com).
To learn , experiment Oracle's SQL features , this is the simplest way.
Here , I can choose which version of the database , I can learn with.
Currently , we can choose from 19c , 23ai and 26ai.
Use Case :
List the employee's information , who are in "Finance" department ( ID : 10 ) and whose salary is over the average salary in that department.
Code 1:
select
employee_id ,
first_name ,
last_name ,
salary ,
department_name
from
hr.employees e
inner join hr.departments d on
( d.department_id = e.department_id )
where
d.department_id = 100
qualify salary > avg (salary) over();
Prior to 26ai , the SQL would have been as below.
Code 2:
With v_avg_sal_emp as
( select
employee_id ,
first_name ,
last_name ,
salary ,
department_name,
avg(salary) over() as avg_salary
from
hr.employees e
inner join hr.departments d on
( d.department_id = e.department_id )
where
d.department_id = 100
)
Select
employee_id ,
first_name ,
last_name ,
salary ,
department_name
from v_avg_sal_emp
Where salary > avg_salary;
Welcome your comments!
