Oracle Foundation Note 14

14th Chapter Advanced Sub-query

1. Sub-query

Subquery (inner query) executes before the main query executes

Main query (outer query) uses the results of a subquery

SELECTselect_list

Fromtable

WHEREexpr operator (SELECT select_list

From table);

Question: Information about employees who pay more than 149th employees

SELECT last_name

From Employees

WHERE Salary >

(SELECT Salary

From Employees

WHERE employee_id = 149);

First, Dolez query

Column comparisons: Paired comparisons and unpaired comparisons

Examples of paired comparisons:

Question: Query the employee_id of other employees with the same manager_id and department_id as employee No. 141th or 174th, manager_id, department_id

SELECTemployee_id, manager_id, department_id

FromEmployees

WHERE (manager_id, department_id) in

(SELECT manager_id, department_id

From Employees

WHERE employee_id in (141,174))

andemployee_id not in (141,174);

Examples of 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))

andemployee_id not in (174,141);

Second, using subqueries in the FROM clause

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

Law One:

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 >

(select AVG (Salary)

From Employees E2

where e1.department_id = e2.department_id

GROUP BY department_id

)

Law II:

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;

Three, single-row sub-query expressions

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

Example of single row subquery application

Use a single-column subquery in a CASE expression

Problem: Explicit employee's employee_id,last_name and location. Among them, if the employee department_id is the same as the department_id of location_id 1800,

The location is ' Canada ' and the rest is ' USA '.

SELECT employee_id, last_name,

(case

When department_id =

(SELECT department_id from departments

WHERE location_id = 1800)

Then ' Canada ' ELSE ' USA ' END

From employees;

Using a single-column subquery in an ORDER by clause

Question: Query employee's employee_id,last_name, ask to sort according to employee's Department_name

SELECT employee_id, last_name

From Employees E

ORDER by(SELECT department_name

from Departments D

WHERE e.department_id = d.department_id);

Iv. related sub-queries

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);

Question: Inquire about the last_name,salary and department_id of employees whose wages are higher than the average wage of the department

SELECT last_name, salary, department_id

From Employees outer

WHERE Salary >(SELECT AVG (Salary)

from Employees

WHERE department_id =

outer.department_id);

Problem: 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 (*)

From Job_history

WHERE employee_id = e.employee_id);

V. EXISTS operator

The EXISTS operator checks if there are rows in the subquery that meet the criteria

If there are rows in the subquery that meet the criteria:

Do not continue to find in subqueries

Condition returns TRUE

If there are no rows in the subquery that meet the criteria:

Conditional return FALSE

Continue to find in subqueries

Question: 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);

Problem: Querying the Departments table, the department_id and department_name of departments that do not exist in the Employees table

SELECT department_id, Department_name

From Departments D

WHERE not EXISTS (SELECT ' X '

From Employees

WHERE department_id

= d.department_id);

Vi. 1. Related updates: Update data for another table using related subqueries based on data from one table

UPDATE table1 ALIAS1

SET column = (SELECT expression

From Table2 ALIAS2

WHERE Alias1.column =

Alias2.column);

Example:

ALTER TABLE Employees

ADD (Department_name VARCHAR2 (14));

UPDATE Employees E

SET Department_name =

(SELECT Department_name

From Departments D

WHERE e.department_id = d.department_id);

2. Related deletions: Delete data from another table using related subqueries based on data from one table

Problem: Delete data from table employees and Emp_history table

DELETE from Employees E

WHERE employee_id =

(SELECT employee_id

From Emp_history

WHERE employee_id = e.employee_id);

Vii. 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

Question: The department that inquires the total wages of the departments in the company is greater than the average total wages of the departments 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;

This article from the "Ah Cheng Blog" blog, reproduced please contact the author!

Oracle Foundation Note 14