Oracle (4) --) Advanced subquery ~)
This section will discuss the more complex application of instant queries, mainly to simplify SQL and improve performance from the aspect, of course .. There are many ways to improve performance .. Today I attended a lecture on Oracle performance tuning and learned about all aspects of Oracle performance and optimization. Although we may not be able to deal with such complex problems, we can still optimize SQL statements for simplified SQL statements and continue to work hard. Let's not talk about it. Let's discuss it through several examples ~ (Of course .. It is still difficult for me to be a cainiao. Thank you for your understanding ~) I hope to give you some useful ideas or references ~
I. subquery Review
A subquery is another SELECT statement nested in an SQL statement.
Example: 1. query the employee information whose salary is greater than the employee's salary on the 1949th employee (only available in a single column; otherwise, an error is reported)
The result is as follows:
2. query the manager whose Last_name is grant.
The result is as follows:
Ii. Multi-column subquery (multiple results will be returned using in) 1. For example, query the emloyee_id, manager_id, and manager_id of other employees with the same employee_id as employee 149 or 176, department_id. Non-paired comparison: The following table lists the data of employee_id, department_id, and manager_id In the employees table:
Analysis: 1) First, write the content in
-- Query emloyee_id, manager_id, and department_id -- The where condition limits manager_id and department_id -- the value of employee_id cannot be 149 or 176. 2) then fill in the SQL statements of manager_id and department_id. Of course, you 'd better change "" To in.
The query result is as follows:
We found that two parts of the above query appear multiple times, so we should use multi-column subqueries to make the SQL statement more concise. Paired comparison: SQL is as follows:
2. Use the subquery example in the from clause: Return last_name, department_id, salary, and average wage Analysis for employees with higher average salaries than the Department: 1. First, let's discuss how to write a statement without an average salary.
-- Note that the Department number must be consistent (the Department number). 2. Add the average salary.
Make sure that you join a select query to query the average salary. The query result is as follows:
So how to use subqueries in the from clause? First, we use a method similar to multi-table join. How to use multi-table join? Take a look at the following example:
What is the relationship between subquery and from clause? In fact, orders is a real table. However, in the average wage query, we can use SQL statements to create a table and then establish the connection between employees and the table, SQL statement redundancy can be reduced. The SQL statement is as follows and the result is as follows:
It can be found that the query results are consistent with the previous results, but the redundancy is greatly reduced, but the idea should be clear and difficult.
3. Single-Column subquery expression: A subquery expression that returns only one column in a row. Example: displays the employee's employee_id, last_name, and location. If the department_id of an employee is the same as the department_id of the location_id 1800, the location is 'Canada ', and the rest are 'usa '. Analysis: If... then... Condition Statement: case... when... then... end, SQL and returned results are as follows:
Employees table information
Parameters table information (only one ID corresponds)
Example: query the employee's employee_id and last_name, which must be sorted by the employee's department_name
Department_name does not exist in the employees table. Each time an employee is queried, the corresponding name will be found and compared with the name already in the sorted order. If the front row is in the front row, the back row will be in the back row, therefore, the connection conditions must exist.
