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