This section will discuss all of a sudden query for more complex applications, mainly to simplify SQL, improve performance from the side, of course. There are many more places to improve performance. Today I listened to a lecture on Oracle's performance tuning to understand all aspects of Oracle performance and optimization. While it may not be possible to meet the complexity of the problem, it is now possible and a constant effort to streamline SQL. Not much to say, through a few examples first to discuss ~ (of course. For me this rookie still has the difficulty, the big God many understanding, many forgive ~) hoped to give everybody some beneficial ponder or the reference ~
First, sub-query review
a subquery is another SELECT statement that is nested in an SQL statement
Example:
1, query wages more than 148th employees salary staff information (only for single-row query, otherwise error)
The
results are as follows
2. Query the manager's information last_name for Grant
The
results are as follows
Second, Dolez query (will return multiple results with in)
1, Example: Query employee_id for 149th or 176th employees manager_id and department_id the same other employees emloyee_id, manager_id, department_id.
unpaired comparison mode:
the data for employee_id, department_id, and manager_id in the Employees table are as follows
Analysis:
1) First write the content in
--Query emloyee_id, manager_id, department_id
--Where condition will manager_id, department_id limit
--The employee_id cannot be 149 or 176.
2) then fill in the SQL statements of manager_id and department_id, but it is better to change "" to in.
The
query results are displayed as follows:
we found that two parts of the above query appeared multiple times, so we should make the SQL statement more concise through Multi-column subqueries .
Paired comparison mode:SQL as follows
2. using subqueries in the FROM clause
Example: Return of last_name,department_id,salary and average salary of employees with higher average salary than the Department
Analysis:
1, first of all, we discuss how to write if there is no average salary.
-Note that the department number must be consistent (this department)
2. Plus the average salary
by adding a select query to the average salary of the query, but also to ensure that the Department, the results are as follows
So how do you use subqueries in the FROM clause?
first, it uses a similar approach to multi-table joins. How do I use a multi-table connection ? Take a look at the following example:
What
does that have to do with the FROM clause using subqueries? in fact, departments is a real table, but in the average wage query, we can use SQL statements to establish a table, and then establish a employees with the table connection, you can reduce the redundancy of the SQL statement. the SQL statements are as follows and the results are as follows:
It can be found that such query results are consistent with the previous results, but the redundancy is greatly reduced, but the idea should be clear and difficult.
three, single-row subquery expression: is a subquery that returns only one column in a row
-----------------------------------------------------------------------------------------
Example: Displays the employee's employee_id,last_name, and location. Among them, if the employee's department_id and location_id is 1800 department_id Same, then location is ' CANADA ', the rest is ' USA '.
Analysis: If. Then: the conditional statement. When: Then.. End,sql and return results are as follows
employees information in tables
Departments information in table (only one ID corresponds)
-----------------------------------------------------------------------------------------------------
Example: Query the employee's employee_id and last_name, request according to employee's department_name sort
Department_name does not exist in the Employees table,
each time an employee is queried, the corresponding name is found, and then compared with the already ordered name, if the front to the front row, back to the rear, so the connection conditions must exist.
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Oracle (four)--Advanced Sub-query (multiple examples of difficult, really do not believe in to see ~)
