Oracle_ Advanced Sub-query



Oracle_ Advanced Sub-query

① Sub-query
A subquery is another SELECT statement that is nested in an SQL statement
Subquery (inner query) executes before the main query executes
Main query (outer query) uses the results of a subquery

② Multi-column subquery
The main query is compared to multiple columns returned by the subquery

where (COLUMN1,COLUMN2) in (subquery)

Example: Query the employee_id of other employees who are identical to the manager_id and department_id of Employee No. 141th or 174th, manager_id, department_id

The comparison in the

multicolumn subquery is divided into two types:
1) Paired comparison
select employee_id, manager_id, department_id
From employees
where  (manager_id, department_id) in
                       (SELECT manager_id, department_id
                         from   Employees
                        where  employee_id in (141,174))
and  employee_id not in (141,174);

2) unpaired Comparisons
SELECT employee_id, manager_id, department_id
From Employees
WHERE manager_id in (SELECT manager_id
From Employees
WHERE employee_id in (174,141))
and department_id in (SELECT department_id
From Employees
WHERE employee_id in (174,141))
and employee_id not in (174,141);

③ using subqueries in the FROM clause

Example: last_name, department_id, salary and average salary of employees who return higher average wages than the department

1) Select Last_name,department_id,salary,
(select AVG (Salary) from employees e3 where e1.department_id = E3.DEPARTMENT_ID GROUP by department_id) Avg_salary
from Employees E1
where salary >
   & nbsp;     (select AVG (Salary)
          from Employees e2 
          where e1.department_id = E2.department _id
          GROUP by department_id
          )

2) SELECT A.last_name, A.salary, a.department_id, B.salavg
From employees A, (SELECT department_id,
AVG (Salary) Salavg
From Employees
GROUP by department_id) b
WHERE a.department_id = b.department_id
and A.salary > B.salavg;

④ single-column subquery expression
A single-row subquery expression is a subquery that returns only one column in a row
Oracle8i can be used only under the following conditions, for example:
SELECT statement (from and WHERE clauses)
In the values list in the INSERT statement
The single-column subquery expression in oracle9i can be used in the following cases:
DECODE and Case
In all clauses except the GROUP by clause in SELECT

1) using a single-column subquery in a CASE expression
Example: the Employee_id,last_name and location of an explicit employee. which
If the employee department_id is the same as department_id with location_id 1800, the location is ' Canada ' and the rest is ' USA '.

SELECT employee_id, last_name,
(Case if department_id = (SELECT department_id from departments WHERE location_id = 1800) Then ' Canada '
ELSE ' USA ' END
) Location
From employees;

2) using a single-column subquery in the ORDER by clause
Example: Query employee's employee_id,last_name, request according to employee's department_name sort
SELECT employee_id, last_name
From Employees E
ORDER by (SELECT department_name
From Departments D
WHERE e.department_id = d.department_id);

⑤ related subqueries
Correlated subqueries are executed in a row-by-row order, and each row of the main query executes a subquery once

SELECT column1, Column2, ...
From table1 outer
WHERE column1 operator (SELECT colum1, Column2
From table2
WHERE expr1 = OUTER.EXPR2);

Example: The Last_name,salary and department_id of employees whose wages are higher than the average wage of the department are queried
SELECT last_name, salary, department_id
From Employees outer
WHERE Salary > (SELECT AVG (Salary)
From Employees
WHERE department_id = outer.department_id)

Example: If the employee_id in the Employees table is not less than 2 employee_id the same number as the Job_history table, the Employee_id,last_name and its job_id of employees who output these same IDs
Select e.employee_id, last_name,e.job_id
from   employees e
where  2 <= (select COUNT (*)
& nbsp;            from   job_history
              where  employee_id = e.employee_id); The
⑥exists operator
EXISTS operator checks to see if there are rows in the subquery that meet the criteria
1. If there are rows in the subquery that meet the criteria:
does not continue to find in subqueries
condition returns TRUE
2. If there are no rows in the subquery that satisfy the criteria:
Condition returns FALSE
to continue looking in the subquery for

Example: Query the company manager's employee_id,last_name,job_id,department_id information
SELECT employee_id, last_name, job_id, department_id
from   Employees Outer
where  EXISTS (SELECT ' X '
                  from   Employees
                  where  manager_id =
                          outer.employee_id);

Example: Querying the Departments table, the department_id and Department_name
SELECT department_id for departments that do not exist in the Employees table, Department_name
from Departments D
WHERE not EXISTS (SELECT ' X '
                   from   Employees
                   where  department_id
                           = d.department_id);

⑦ Related Updates
UPDATE table1 ALIAS1
SET column = (SELECT expression
From Table2 ALIAS2
WHERE Alias1.column =
Alias2.column);
Update data for another table using related subqueries based on data from one table

⑧ related deletions
DELETE from table1 ALIAS1
WHERE column operator
(SELECT expression
From Table2 ALIAS2
WHERE alias1.column = alias2.column);
⑨with clause
Use the WITH clause to avoid repeating the same block of statements in a SELECT statement
The WITH clause executes the statement block in the clause once and stores it in the user's temporary table space
Use the WITH clause to improve query efficiency

Example: The department information of the total wages of each department in the company is more than the average total wage of each department in the company.
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;

Oracle_ Advanced Sub-query