Oracle (4) --> advanced subquery (it is difficult to explain multiple examples, but I really don't believe it ~), Oracle Introduction
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 information of employees whose salaries are greater than 148 (only query 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:
2. Multi-column subquery (in is used to return multiple results)
1. For example, query the emloyee_id, manager_id, and department_id of other employees whose employee_id is 149 or 176.
Non-paired comparison method:
The data of employee_id, department_id, and manager_id In the employees table is as follows:
Analysis:
1) first, you should write the content in"
-- Query emloyee_id, manager_id, 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 subquery in the from clause
Example: return last_name, department_id, salary, and average salary of employees with higher average salaries than the Department
Analysis:
1. First, let's discuss how to write a statement without an average salary.
-- Note that the Department number must be consistent (department)
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 that returns only one column in a row
Bytes -----------------------------------------------------------------------------------------
For example, the employee's employee_id, last_name, and location are displayed. 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)
Bytes -----------------------------------------------------------------------------------------------------
For example, to query the employee's employee_id and last_name, sort by 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 then compared with the existing sorted name. If the front row is in the front row, the back row is in the back row. Therefore, the connection condition must exist.
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.
