SQL subquery nested SELECT statement

Nested select statements are also called subqueries. The query results of one SELECT statement can be used as input values of another statement. Subqueries can appear not only in the WHERE clause, but also in the from clause as a temporary table. They can also appear in the select list and be returned as a field value.

1. Single Row subquery: a single row subquery indicates that only one row of data is returned from the subquery results. When the subquery result is referenced in the Condition Statement of the primary query statement, a single row comparison symbol (=, >,<, >=, <=, <>) can be used for comparison.

Example:
Select ename, deptno, Sal
From EMP
Where deptno = (select deptno from Dept where loc = 'New York ');

2. multi-row subquery: a multi-row subquery returns multiple rows of data. When the subquery results are referenced in the Condition Statement of the primary query statement, you must use the in, all, any symbol to compare multiple rows. The meaning of in is to match any value in the subquery result ("in" operator, which can test whether a value is in a list ), all must match all values of the subquery. Any must match any value of the subquery result. Note that the all and any operators cannot be used independently, but can only be used with single row comparison operators (=,>, <, >=, <=, <>.

Example:

1). Example of using the in operator symbol for multi-row subqueries: the query takes the Student name Rona (assuming it is unique) as an optional course.

SQL> select stname

From student

Where stid in (selectdistinct stid from Score where teid = (select teid from teacher where tename = 'rona '));

Query Information of all departments numbered:

Select ename, job, Sal

From EMP

Where deptno in (select deptno from Dept where dname like 'a % ');
2). multi-row subqueries use the "all" operator. For example, to query the names of students whose scores are higher than those of Kaka:

SQL> select stname

From student

Where stid in (select distinct stid from Score where score> All (select score from Score where stid = (select stid from student where stname = 'kaka ')));
3). Example of using the any operator symbol for multi-row subqueries: query the names of any student whose scores are higher than Kaka:

SQL> select stname

From student

Where stid in (select distinct stid from Score where score> Any (select score from Score where stid = (select stid from student where stname = 'kaka ')));

3. Multi-column subquery: when a single-row multi-column subquery is used, the single-row comparison symbol (=, >,<, >=, <=, <>, all, any) for comparison.

Example:
Select deptno, ename, job, Sal
From EMP
Where (deptno, Sal) in (select deptno, max (SAL) from EMP group by deptno );

4. inline view subquery

Example:
(1) Select ename, job, Sal, rownum
From (select ename, job, Sal from EMP order by SAL );
(2) Select ename, job, Sal, rownum
From (select ename, job, Sal from EMP order by SAL)
Where rownum <= 5;

5. Use subqueries in the having clause

Example:
Select deptno, job, AVG (SAL) from EMP group by deptno, job having AVG (SAL)> (select Sal from EMP where ename = 'martin ');

Let's take a look at some specific instances,

1. Name of a country with more population than Russia (Russia)

Select name from BBC Where population> (Select population from BBC Where name = 'Russia ')

2. Provide any information about any country in the region where 'India "and 'irance' (Iran) are located.

Select * from BBC Where region in (Select region from BBC Where name in ('India ', 'irance '))

Iii. European countries with GDP per capita exceeding 'United Kingdom '(UK.

Select name from BBC Where region = 'Europe' and GDP/population> (Select GDP/population from BBC Where name = 'United Kingdom ')

Exam materials:

Http://www.west263.com/info/html/wangluobiancheng/Mysql/20080225/32087.html

Http://blog.csdn.net/rboyxxx/archive/2009/08/17/4455757.aspx

SQL subquery summary:

Many Transact-SQL statements that contain subqueries can be expressed using join statements. In Transact-SQL, statements that contain subqueries and statements that are semantically equivalent to statements that do not contain subqueries have no performance difference. However, in some cases where you must check the existence, using a join produces better performance. Otherwise, to ensure that duplicate values are eliminated, nested queries must be processed for each result of an external query. In these cases, the connection method will produce better results.
The following example shows the select subquery and select join that return the same result set:

Select name
From adventurewor ks. Production. Product
Where listprice =
(Select listprice
From adventurewor ks. Production. Product
Where name = 'chainring bolts ')

Select prd1. name
From adventurewor ks. Production. Product as prd1
Join adventurewor ks. Production. Product as prd2
On (prd1.listprice = prd2.listprice)
Where prd2. name = 'chainring bolts'

Subqueries nested in external select statements include the following components:

● General SELECT query that includes the general selection list component.
● A regular from clause that contains one or more table or view names.
● Optional where clause.
● An optional group by clause.
● An optional having clause.

The SELECT query of A subquery is always enclosed in parentheses. It cannot contain the compute or for browse clause. If the top clause is specified at the same time, it can only contain the or der by clause.

Subqueries can be nested in the where or having clause of external select, insert, update, or delete statements, or in other subqueries. Although the nesting restriction varies depending on the complexity of other expressions in the available memory and query, it is possible to nest to 32 layers. Individual queries may not support 32-layer nesting. You can use subqueries wherever expressions are available, as long as they return a single value.

If a table appears only in a subquery but not in an external query, the columns in the table cannot be included in the output (selected list of external queries.

Statements that contain subqueries generally use one of the following formats:

● Where expression [not] In (subquery)
● Where expression comparison_operator [any | all] (subquery)
● Where [not] exists (subquery)

In some Transact-SQL statements, subqueries can be calculated as independent queries. In terms of concept, the subquery results are substituted into external queries (although this is not necessarily the way Microsoft SQL Server 2005 actually processes the transact-SQL statements with subqueries ).

There are three basic subqueries. They are:

● Operate on the list introduced by in or a comparison operator modified by any or all.
● A single value must be returned if it is introduced through an unmodified comparison operator.
● Existence test introduced by exists.

1. nested queries with in

Select EMP. empno, EMP. ename, EMP. Job, EMP. Sal from Scott. EMP where Sal in (select Sal from Scott. EMP where ename = ''ward '');
The preceding statement queries employees with the same salary as Ward, or not in.

2. nested queries with any
Use the comparison operator to compare the value or column value of an expression with each of the values in a column returned by the subquery. If the result of one comparison is true, the any test returns true.

Select EMP. empno, EMP. ename, EMP. job, EMP. sal from Scott. EMP where SAL> Any (select Sal from Scott. EMP where job = ''manager '');
The execution process is equivalent to the following two steps:
(1) Run "select Sal from Scott. EMP where job = ''manager ''"
(2) 3 salary values 2975, 2850, and 2450 are found. The parent query executes the following statements:
Select EMP. empno, EMP. ename, EMP. Job, EMP. Sal from Scott. EMP where SAL> 2975 or SAL> 2850;

3. nested queries with some

Select EMP. empno, EMP. ename, EMP. job, EMP. sal from Scott. EMP where sal = some (select Sal from Scott. EMP where job = ''manager '');
The execution process is equivalent to the following two steps:
(1) Run "select Sal from Scott. EMP where job = ''manager'" in the subquery ''".
(2) The parent query executes the following statements.
Select EMP. empno, EMP. ename, EMP. Job, EMP. Sal from Scott. EMP where sal = 2975 or sal = 2850 or sal = 2450;
Nested queries with [any] are the same as those with [some. Early SQL only allowed the use of [any]. Later versions introduced [some] to distinguish it from [any] in English, and reserved the [any] keyword.

4. nested queries with all
Use the comparison operator to compare the value or column value of an expression with each of the values in a column returned by the subquery. If the result of one comparison is false, all tests return false.
Select EMP. empno, EMP. ename, EMP. job, EMP. sal from Scott. EMP where SAL> All (select Sal from Scott. EMP where job = ''manager '');
The execution process is equivalent to the following two steps:
(1) Run "select Sal from Scott. EMP where job = ''manager'" in the subquery ''".
(2) The parent query executes the following statements.
Select EMP. empno, EMP. ename, EMP. Job, EMP. Sal from Scott. EMP where SAL> 2975 and Sal> 2850 and Sal> 2450;

5. nested queries with exists

Select EMP. empno, EMP. ename, EMP. job, EMP. sal from Scott. EMP, Scott. dept where exists (select * from Scott. EMP where Scott. EMP. deptno = Scott. dept. deptno );

6. Perform nested queries

The parallel operation is the concept of union in the set. The sum of elements that belong to set a or set B is the union.
(Select deptno from Scott. EMP) Union (select deptno from Scott. Dept );

7. subscribe nested Query

The intersection operation is the concept of the intersection in the set. The sum of elements that belong to set a and belong to Set B is the intersection.

(Select deptno from Scott. EMP) intersect (select deptno from Scott. Dept );

8. nested queries with poor operations

The Difference operation is the concept of the difference set in the set. The sum of elements that belong to set a and do not belong to Set B is the difference set.
(Select deptno from Scott. Dept) minus (select deptno from Scott. EMP );
Note: The nested query of union, intersection, and difference operations requires that the attributes have the same definitions, including the type and value range.

The left side is a list of scalar expressions. the right-hand side can be a list of constant scalar expressions or a subquery enclosed by Circular Arc. The query must return fields with the same bibliography as the left-hand expression. in addition, the subquery cannot return more than the number of rows. (if it returns zero rows, the result is null .) the left-hand side compares the result line with the right-hand side of the subquery, or the right-hand side expression list. currently, only the = and <> operators can be used for Row-by-row comparison. if the two rows are equal or unequal, the result is true.

Generally, the expression or the null in the subquery row is combined according to the general rules of the SQL Boolean expression. if the members of both rows are non-empty and equal, the two rows are considered equal. If any member is non-empty and unequal, the two rows are not equal; otherwise, the result of the row comparison is unknown (null ).