oracle03--Sub-query

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