Unit 15: Advanced subquery

Non related subqueries are used as a table:

select e.last_name,e.salary,e.department_id,b.salavg
from employees e,(select department_id,avg(salary) salavg
                  from employees
                  group by department_id) b
where e.department_id = b.department
      and e.salary > b.salavg;


The meaning of this sql is to find out the related information of employees whose salary is greater than the average salary of their department.

Concept of related subquery: the table in the external main query is referenced in the subquery.

select last_name,salary,department_id
from employees outer
where salary > (select avg(salary)
                from employees
                where department = outer.department_id);

It means to find out the related information of employees whose salary is greater than the average salary of their department.

select e.employee_id,e.last_name,e.job_id
from employees e
where 2 <= (select count(*)
            from job_history
            where employee_id = e.employee_id);

It means to query the related information of employees who have resigned more than twice.

Use the exists operation:

select employee_id,last_name,job_id,department_id
from employees outer
where exists (select 'X'
              from employees
              where manager_id = outer.employee_id);
select employee_id,last_name,job_id,department_id
from employees outer
where employee_id in (select manager_id
                      from employees
                      where manager_id is not null);

The two SQL results are the same, but is the execution performance the same?

Use the not exists action:

select department_id,department_name
from departments d
where not exists (select 'X' 
                  from departments
                  where department_id = d.department_id);
select department_id,department_name
from departments d
where department_id not in (select department_id 
                            from departments);

Find out the Department without employees.
Are the two SQL results the same? Also, please compare their execution performance

Note: as long as there is a NULL in Not In, it doesn't work, which is easy to make mistakes. For the correct method, please add where Department ﹣ ID is not NULL in the following subquery;

select department_id,department_name
from departments d
where department_id not in (select department_id 
                            from departments
                            where department_id is not null);

Use related subqueries in the Update statement:

alter table employees
add (department_name varchar2(14));

update employees e
set e.department_name = (select d.department_name
                            from departments d
                            where e.department_id = d.department_id);

Use related subqueries in DELETE statements:

delete from job_history JH
where employee_id = (select employee_id
                     from employees E
                     where JH.employee_id = E.employee_id
                           and start_date = (select min(start_date)
                                             from job_history JH
                                             where JH.employee_id = E.employee_id)
                                                   and 5 > (select count(*)
                                                            from job_history JH
                                                            where JH.employee_id = E.employee_id
                                                            group by employee_id
                                                            having count(*) >= 4));

Use the WITH statement:

WITH dept_costs AS
 (SELECT d.department_name, SUM(e.salary) AS dept_total
    FROM employees e, departments d
   WHERE e.department_id = d.department_id
   GROUP BY d.department_name),
avg_cost AS
 (SELECT SUM(dept_total) / COUNT(*) AS dept_avg FROM dept_costs)
SELECT *
  FROM dept_costs
 WHERE dept_total > (SELECT dept_avg FROM avg_cost)
 ORDER BY department_name;

Benefits of using WITH:

  • It can be simplified if it is used many times later
  • Improve performance appropriately

Tags: SQL

Posted on Sun, 09 Feb 2020 14:42:23 -0500 by keldorn