SQL nested SELECT statement Usage-

The SQL nested SELECT statement is a very common SQL statement, which gives you a detailed description of the syntax of the SQL nested SELECT statement, with examples for you to learn.

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:

1. Select Ename,deptno,sal
2. From EMP
3. 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)

1. SQL> select Stname
3. From Student
5. where Stid in (SelectDistinct Stid from score where teid= (select Teid from teacher where tename=' Rona '));

For information on all departments numbered a:

1. SELECT Ename,job,sal
3. From EMP
5. 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:

1. SQL> select Stname
3. From Student
5. 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). Multi-row subqueries use the Any action symbol example: Query the name of a student who has more than one score above Kaka:

1. SQL> select Stname
3. From Student
5. 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, 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:

1. SELECT Deptno,ename,job,sal
2. From EMP
3. WHERE (Deptno,sal) in (SELECT Deptno,max (SAL) from the EMP GROUP by Deptno);

4. Inline View Sub-query

Cases:

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

5. Using subqueries in the HAVING clause

Cases:

1. Select Deptno,job,avg (SAL) from EMP GROUP by Deptno,job have AVG (sal)> (SELECT sal from emp WHERE ename=' M Artin ');

SQL nested SELECT statement Usage-