Oracle Database Advanced subquery ① subquery A subquery is another SELECT statement nested in an SQL statement subquery (internal query) that executes the primary query (external
Oracle Database Advanced subquery ① subquery A subquery is another SELECT statement nested in an SQL statement subquery (internal query) that executes the primary query (external
Oracle Database Advanced subquery
① Subquery
A subquery is another SELECT statement nested in an SQL statement.
Subquery (internal query) is executed before the master query is executed.
Main query (external query) uses the subquery results
② Multi-column subquery
Compares multiple columns returned by a primary query with a subquery.
Where (column1, column2) in (subquery)
For example, query the employee_id, manager_id, and department_id of other employees with the same manager_id and department_id of employees 141 or 174.
There are two types of comparisons in Multi-column subqueries:
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) Non-paired comparison
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 );
③ Use subquery in the from clause
Example: return last_name, department_id, salary, and average salary of employees with higher average salaries 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>
(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-column subquery expression returns only one column of subquery in one row.
Oracle8i can only be used in the following scenarios:
SELECT Statement (FROM and WHERE clause)
List of VALUES in the INSERT statement
The single-column subquery expression in Oracle9i can be used in the following situations:
DECODE and CASE
In SELECT, all clauses except the group by clause
1) use a single-column subquery in a CASE expression
For example, the explicit employee's employee_id, last_name, and location. Where,
If the employee department_id is the same as the employee department_id with the location_id of 1800, the location is 'Canada ', and the other is 'USA '.
SELECT employee_id, last_name,
(Case when department_id = (SELECT department_id FROM orders ments WHERE location_id = 1800) THEN 'Canada'
ELSE 'usa' END
) Location
FROM employees;
2) use a single-column subquery in the order by clause
For example, to query the employee's employee_id and last_name, sort by employee's department_name
SELECT employee_id, last_name
FROM employees e
Order by (SELECT department_name
FROM orders ments d
WHERE e. department_id = d. department_id );
⑤ Related subqueries
Related subqueries are executed in the order of one row and one row. each row of the primary query executes a subquery.
SELECT column1, column2 ,...
FROM table1 outer
WHERE column1 operator (SELECT colum1, column2
FROM table2
WHERE expr1 = outer. expr2 );
For example, query the last_name, salary, and department_id of employees whose salaries are higher than the average salaries of their respective departments.
SELECT last_name, salary, department_id
FROM employees outer
WHERE salary> (select avg (salary)
FROM employees
WHERE department_id = outer. department_id)
For example, if the number of employees with the same id is not less than 2 in the employee id and job_history table in the employees table, last_name and job_id are output.
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 );
⑥ EXISTS Operator
The EXISTS operator checks whether any row that meets the condition EXISTS in the subquery.
1. If the subquery contains rows that meet the conditions:
Continue searching in subquery
Returns TRUE.
2. If no row meets the conditions in the subquery:
The return value is FALSE.
Continue searching in subquery