Oracle Database Advanced subquery

Oracle Database Advanced subquery

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

For example, query the employee ID, last_name, job_id, and department_id information of a company manager.
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
Where exists (SELECT 'x'
FROM employees
WHERE manager_id =
Outer. employee_id );

For example, department_id and department_name of a department in the employees table are queried.
SELECT department_id, department_name
FROM orders ments d
Where not exists (SELECT 'x'
FROM employees
WHERE department_id
= D. department_id );

7. Related updates
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column =
Alias2.column );
Use related subqueries to update the data of another table based on the data in one table

Shard-related Deletion
Delete from table1 alias1
WHERE column operator
(SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column );
Starts WITH clause
Use the WITH clause to avoid repeated statement blocks in the SELECT statement.
The WITH clause executes the statement blocks in the clause once and stores them in the user's temporary tablespace.
The WITH clause can improve the query efficiency.

For example, query the information of the department whose total salary is greater than the average total salary of each department in the company.
WITH dept_costs (
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 (
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;

Advanced Oracle Database Query