SQL subquery Nested SELECT statement

A nested SELECT statement is also called a subquery, and the query result of a SELECT statement can be used as the input value for another statement. A subquery can appear not only in the WHERE clause, but also in the FROM clause, as a temporary table, or in a select list, as a field value.

1, single-row subquery: A single-row subquery refers to a subquery that returns only one row of data. When a subquery result is referenced in the conditional statement of the main query statement, a single-line comparison symbol (=, <, >=, <=, <>) is used to compare.

Cases:
Select Ename,deptno,sal
From EMP
where deptno= (select Deptno from dept where loc= ' NEW YORK ');

2, Multiline subquery: Multi-row subquery is the result of the subquery is multi-row data. When a subquery result is referenced in a conditional statement in the main query statement, it must be compared with a multiline comparison symbol (In,all,any). Where in is meant to match any of the values in the subquery result (the "in" operator, the ability to test whether a value is in a list), all must conform to all the values of the subquery, any one of the values of the subquery results. Also note that the all and any operators cannot be used alone, and can only be used in conjunction with single-line comparators (=, >, <, >=, <=, <>).

Cases:

1). Multi-row subqueries use the in operation symbol Example: The query took the name of the student whose teacher was named Rona (assuming only)

Sql> Select Stname

From Student

where Stid in (SelectDistinct Stid from score where teid= (select Teid from teacher where tename= ' Rona '));

For information on all departments numbered a:

SELECT Ename,job,sal

From EMP

where Deptno in (SELECT deptno from dept WHERE dname like ' A% ');
2). Multi-row subqueries Use the all operation symbol Example: the name of the student who has more than one score above Kaka's highest score:

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 Stu Dent where stname= ' Kaka '));
3). Multi-row subqueries use the Any action symbol example: Query the name of a student who has more than one score above 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 Stu Dent where stname= ' Kaka '));

3, Dolez query: When it is a single-row multi-column subquery, the main query statement in the conditional statement reference subquery results can be compared by a single line comparison symbol (=, <, >=, <=, <>) for comparison; When multi-row, multi-column subquery, A multiline comparison symbol (In,all,any) must be used to compare the subquery results in the conditional statement of the main query statement.

Cases:
SELECT Deptno,ename,job,sal
From EMP
WHERE (Deptno,sal) in (SELECT Deptno,max (SAL) from the EMP GROUP by Deptno);

4. Inline View Sub-query

Cases:
(1) SELECT Ename,job,sal,rownum
From (SELECT ename,job,sal to EMP ORDER by Sal);
(2) SELECT Ename,job,sal,rownum
From (SELECT ename,job,sal to EMP ORDER by Sal)
WHERE rownum<=5;

5. Using subqueries in the HAVING clause

Cases:
Select Deptno,job,avg (SAL) from EMP GROUP by Deptno,job has AVG (sal) > (SELECT sal from emp WHERE ename= ' MARTIN ');

Let's take a look at some concrete examples,

First, give the name of the country with more population than Russia (Russia)

SELECT name from BBC WHERE population> (SELECT population from BBC WHERE name= ' Russia ')

Ii. any information given to any country in the region of ' India ' (India), ' Iran ' (Iran)

SELECT * from BBC WHERE Region in (SELECT region from BBC WHERE name in (' India ', ' Iran '))

Third, the 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= ' Kingdom ')

Information:

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 use join representations instead. In Transact-SQL, statements that contain subqueries and semantically equivalent statements that do not contain subqueries generally do not differ in performance. However, in some cases where there is a need to check for presence, the use of joins results in better performance. Otherwise, to ensure that duplicate values are eliminated, nested queries must be processed for each result of an external query. So in these cases, the Join method produces better results.
The following example shows a select subquery and a 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 that are nested in an external SELECT statement include the following components:

General select query that contains the General selection list component.
A general FROM clause that contains one or more table or view names.
The optional WHERE clause.
An optional GROUP by clause.
An optional HAVING clause.

Select queries for subqueries are always enclosed in parentheses. It cannot contain a compute or for BROWSE clause and can only contain an OR DER by clause if the TOP clause is also specified.

A subquery can be nested within a WHERE or HAVING clause of an external select,insert,update or DELETE statement, or nested within another subquery. Although nesting restrictions vary depending on the complexity of available memory and other expressions in the query, nesting to layer 32 is possible. Individual queries may not support 32-tier nesting. Any place where an expression can be used can use a subquery, as long as it returns a single value.

If a table appears only in a subquery and does not appear in an external query, the columns in the table cannot be included in the output (the selection list for the outer query).

Statements that contain subqueries typically take 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, a subquery can be evaluated as a stand-alone query. Conceptually, subqueries result in an external query (although this is not necessarily how Microsoft SQL Server 2005 actually handles Transact-SQL statements with subqueries).

There are three basic sub-queries. They are:

Operates on a list introduced by a comparison operator modified by in or by any or all.
is introduced through unmodified comparison operators and must return a single value.
The presence test introduced through EXISTS.

1. Nested query 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 above statement is done by querying employees who have equal pay and ward, or using not in for queries.

2. Nested query with any
A comparison operator compares the value or column value of an expression to each of the column values returned by the subquery, and returns true if the result of one comparison is true.

Select Emp.empno,emp.ename,emp.job,emp.sal from scott.emp where Sal >any (select Sal from scott.emp where job= ' MANAGER ’’);
Equivalent to the following two steps of the execution process:
(1) Execute "Select Sal from Scott.emp where job= ' manager '"
(2) query to 3 salary values 2975, 2850 and 2450, the parent query executes the following statement:
Select Emp.empno,emp.ename,emp.job,emp.sal from scott.emp where Sal >2975 or sal>2850 or sal>2450;

3. Nested query 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 ' );
Equivalent to the following two steps of the execution process:
(1) subquery, execute "Select Sal from Scott.emp where job= ' manager '".
(2) The parent query executes the following statement.
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" and nested queries for "some" are the same. Earlier SQL allowed only "any", and later versions, in order to distinguish it from the English "any", introduced "some" and retained the "any" keyword.

4. Nested query with all
A comparison operator compares the value or column value of an expression to each of the column values returned by the subquery, and returns false if the result of one comparison is false.
Select Emp.empno,emp.ename,emp.job,emp.sal from scott.emp where Sal >all (select Sal from scott.emp where job= ' MANAGER ’’);
Equivalent to the following two steps of the execution process:
(1) subquery, execute "Select Sal from Scott.emp where job= ' manager '".
(2) The parent query executes the following statement.
Select Emp.empno,emp.ename,emp.job,emp.sal from scott.emp where Sal >2975 and sal>2850 and sal>2450;

5. Nested query with exists

Select Emp.empno,emp.ename,emp.job,emp.sal from scott.emp,scott.dept where exists (SELECT * from Scott.emp where scott.em P.DEPTNO=SCOTT.DEPT.DEPTNO);

6. And working with nested queries

And operation is the concept of a set in the collection. The sum of the elements that belong to collection A or set B is the set.
(select Deptno from Scott.emp), union (select Deptno from scott.dept);

7. Nested queries for cross-operation

Cross-operation is the concept of intersection in a set. The sum of the 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 for differential operations

The difference operation is the concept of the difference set in the set. The sum of the 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: Nested queries for the and, intersection, and difference operations require that the properties have the same definition, including the type and range of values.

The left-hand side is a list of scalar expressions. The right-hand side can be a list of equal-length scalar expressions, or a parenthesized subquery that must return exactly the same field as the left-hand side of the expression bibliography. In addition, the subquery cannot return more than one line of quantity. (if it returns 0 rows, the result is NULL.) The left-hand side is compared to the subquery result row on the right-hand side, or to the right-hand side of the expression list. Currently, only the = and <> operators are allowed to be compared on a line-by-row basis. If the two lines are equal or unequal, the result is true.

Typically, NULL in an expression or subquery row is combined according to the general rules of the SQL Boolean expression. If the members of the two rows are non-null and equal, the two rows are considered equal, and if any of the corresponding members are non-null and unequal, then the result of such a row comparison is unknown (NULL).

SQL subquery Nested SELECT statement