1.
Sub-query
Subqueries are also called nested clause queries.
1.1.
Grammar
Syntax for running usage rules:
L subqueries ( inner query, nested clauses ) are executed one time before the main query. (Sub-query executes first)
the results of the subquery are used by the main query ( outer query ).
L subquery to be included in parentheses .
L PLACE the subquery on the right side of the comparison condition.
1.2.
Why use subqueries?
"Demand" whose wages are higher than Scott's ?
Write in a connected way (here is self-connection, see):
-"Demand" whose wages are higher than Scott's? --* from emp t1,emp T2 WHERE t2.ename= ' SCOTT ' and t1.sal>t2.sal
Write in the form of a subquery:
-- Sub-query --analysis: whose wages are higher than Scott? --->1, Scott's salary is 2, who pays 3000 higher select Sal from emp where ename= ' Scott '* from EMP where Sal & gt;3000* from emp where sal > (SELECT sal from emp where ename= ' SCOTT ');
In some businesses, subqueries are easier to understand than connection queries.
1.3.
classification of sub-queries
Note: The difference between a single-line statement and a multiline statement:
The single-line operator (>, =, <) corresponds to a single-line subquery, and the multiline operator (in, not) corresponds to a multiline subquery.
A single-line statement can also use a multiline statement such asin, but a multiline statement must not use a single-line operator such as =;
1.4.
single-line subquery1.4.1.
Syntax Requirements:
1. Returns only one row of records.
2. Use a single-line comparison operator.
Where <> can also be replaced with! =, meaning the same.
--* from EMP where deptno= (SELECT deptno from DEPT where dname = ' SALES ')
Understanding: A subquery can be a table of data, or it can be a different table of data.
1.4.2.
Null value problem
--demand: find work and ' Rose ' = ' Rose' * from emp where job = (SELECT job from emp where ename = ' Rose' *
from EMP; --demand: find work and ' Rose
' * from emp where job! = (SELECT job from emp where ename = ' Rose '
); ---conclusion: As long as the subquery returns NULL, the result of the primary query must be NULL
Note: When using subqueries, make sure that the subquery cannot be empty , otherwise the data will be abnormal .
1.4.3.
illegal use of single-line subqueries
"Sample" requirements : find jobs and ' SMITH ' ALLEN ' two people who work the same employee information
NOTE: When a clause query returns multiple rows of data, the main sentence must never be received with a single-line operator
1.5.
multi-row subqueries1.5.1.
Syntax Requirements:
L returns multiple rows of records.
L Use multi-line comparison operators.
1.5.2.
in operator
--demand: Find jobs and ' Smith ' ALLEN ' two people work like employee Information select Job from emp WHERE ename in (' Smith ', ' ALLEN '* from EMP WH ERE job in (the SELECT job from the EMP WHERE ename in (' SMITH ', ' ALLEN ')); --demand: Find work and ' Smith ' ' Allen' * from EMP where job is not in (SELECT job from EMP where ename in (' Smith ', ' Allen '));
1.5.3.
any and all operators
--Demand: Query salary than 30th Department of any one employee's salary high employee information. --* from emp WHERE deptno =30; --* from emp where sal > (SELECT MIN (SAL) from EMP where deptno=30); --* from EMP where Sal >any (SELECT sal from EMP where deptno=30); --* from EMP where sal> (SELECT MAX (SAL) from EMP where deptno=30); --all (multiple return records)--from the EMP where Sal>all (SELECT sal from EMP where deptno=30);
Analysis results:
1.6.
Sub-query considerations
L About Format: sub-query to be included in parentheses, it is advisable to have a reasonable writing style.
L Sub-query location: can be placed in the main query where,Select,have, from the back . Cannot be placed behind the group by of the main query .
L subqueries and main queries can be the same table or not a different table, as long as the results returned by the subquery can be used in the main query.
L about the use of operators: single-line operators corresponding to single-row subqueries, multiline operators corresponding to multi-row subqueries.
L Execution Order: The general sub-query executes first, then executes the main query;
L about sort: You generally do not use order by in subqueries,but in top-n analysis problems, you must use ORDER by in a subquery .
multi-row subqueries are typically used from behind, as a new virtual temporary table.
A virtual temp table is a temporary table that is a temporary table that is virtual in memory during the run, and is used for SQL operations.
--* from ( * from emp WHERE deptno=30 - virtual table: Use the query results again as a table. ) Twhere Sal>2000;
1.7.
selection of subqueries and multi-table associated queries
theoretically, in situations where demand can be achieved, try to select multiple table queries .
Cause: The subquery operates two times, and the multi-table query operates only once. The efficiency of multiple tables is high.
However, it is important to note that if a multi-table query produces a Cartesian set (the statement should be aware of the use of the condition), there will be a serious efficiency problem.
sorting is not generally used in subqueries (ORDER by), but it must be used in subqueries in top-n parsing problems.
oracle03--Sub-query