Oracle (5) --) Advanced subquery (2)

Oracle (5) --) Advanced subquery (2)
1. Related subqueries the subqueries are executed in the order of one row after another, and each row of the primary query executes a subquery.
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. Let the table employees enter the subquery, and establish a connection with job_history through employee_id. The SQL statement and query result are as follows:
Ii. EXISTS keyword. The EXISTS operator checks whether a row that meets the condition EXISTS in the subquery. If a row that meets the condition EXISTS in the subquery: -- does not continue searching in the subquery -- returns TRUE if the row that meets the condition does not exist in the subquery: -- returns FALSE if the condition is returned -- continues searching in the subquery for example:
The query result is as follows:

Iii. Related XX
1. Related updates-use related subqueries to update the data of another table based on the data in one table. 1) create a new table.
2) Insert a column of new records

3) insert data into the new column. The data must correspond to each other.
-- This operation allows the information of different tables to be centralized into one table
2. Related deletion-use the relevant subquery to delete data from another table based on the data in one table. For example, delete the employees table and all the data in the emp_history table. 1) Create the emp022 table, add data in the department_id of the employees table to 80 and 90.
2) Create the emp033 table and add the department_id of the employees table to 90.
3) Delete the data that already exists in Table emp022 and table emp033.

4. WITH clause 1) using the WITH clause can avoid repeated statement blocks in the SELECT statement; 2) the WITH clause executes the statement blocks in the clause once and stores them in the user's temporary tablespace. 3) using the WITH clause improves the query efficiency.
For example, the with clause is not used to query the information of employees with higher salaries than Fay in the company:
Use the with clause:
This example is not obvious, but when the data volume is large and the same part is queried, using the WITH clause will greatly improve the query efficiency.
Example 2: query the information of the department whose total salary is greater than the average total salary of each department in the company
If you use the previous query method, there will be many nested queries. Using the WITH clause will greatly simplify the query and improve the efficiency.