nesting queries with SQL

In a SELECT query statement, you can embed a SELECT query statement, called a nested query. In some books, the embedded SELECT statement is called a subquery, and the result of the subquery formation becomes the condition of the parent query.
Subqueries can be nested in multiple tiers, and the data tables for subquery operations can be data tables that are not manipulated by the parent query. You cannot have an ORDER by group statement in a subquery.
4.4.1 Simple nested query
In the command edit area, execute the following statement.
―――――――――――――――――――――――――――――――――――――
Select Emp.empno,emp.ename,emp.job,emp.sal from Scott.emp where sal>= (select Sal from scott.emp where ename= ' WARD '); ―――――――――――――――――――――――――――――――――――――
Click the Execute button to see the results shown in Figure 4.19.
"See CD-ROM File": \ 4th Chapter \4.4\441.sql.

In this code, the subquery select Sal from scott.emp where Ename= ' ward ' is meant to query the salary of the employee named Ward from the EMP datasheet, meaning that the parent query is to find an employee whose salary is greater than or equal to Ward's salary in the EMP data table. The above query process is equivalent to two steps in the execution process.
(1) Execute "Select Sal from scott.emp where ename= ' WARD '", Draw sal=1250;
(2) Execute "Select Emp.empno,emp.ename,emp.job,emp.sal from Scott.emp where sal>=1250;"
4.4.2 nested query with "in"
In the command edit area, execute the following statement.
―――――――――――――――――――――――――――――――――――――
Select Emp.empno,emp.ename,emp.job,emp.sal from Scott.emp where Sal in (select Sal from scott.emp where ename= ' WARD ');
―――――――――――――――――――――――――――――――――――――
Click the Execute button to see the results shown in Figure 4.20.
"See CD-ROM File": \ 4th Chapter \4.4\442.sql.

The above statement completes the query for employees who are equal in salary and ward, or you can use ' not in ' to query. 4.4.3 nested query with "any"
In the command edit area, execute the following statement.
―――――――――――――――――――――――――――――――――――――
Select Emp.empno,emp.ename,emp.job,emp.sal from scott.emp where Sal >any (select Sal from scott.emp where job= ' Manag ER ');
―――――――――――――――――――――――――――――――――――――
Click the Execute button to see the results shown in Figure 4.21.
"See CD-ROM File": \ 4th Chapter \4.4\443.sql.

The query process with any is equivalent to the two-step execution process.
(1) Execute "Select Sal from scott.emp where job= ' MANAGER" and the result is shown in Figure 4.22.
"See CD-ROM File": \ 4th Chapter \4.4\443-1.sql.

(2) query to 3 salary values 2975, 2850 and 2450, the parent query executes the following statement.
"See CD-ROM File": \ 4th Chapter \4.4\443-2.sql.
――――――――――――――――――――――――――――――――――――――
Select Emp.empno,emp.ename,emp.job,emp.sal from scott.emp where Sal >2975 or sal>2850 or sal>2450; ――――――――――――――――――――――――――――――――――――――
4.4.4 nested query with "some"
In the command edit area, execute the following statement.
―――――――――――――――――――――――――――――――――――――
Select Emp.empno,emp.ename,emp.job,emp.sal from scott.emp where Sal =some (select Sal from scott.emp where job= ' MANAGER ');
―――――――――――――――――――――――――――――――――――――
Click the Execute button to see the results shown in Figure 4.23.
"See CD-ROM File": \ 4th Chapter \4.4\444.sql.
Nested queries with some are the same steps as any.
(1) Subquery, executing "Select Sal from scott.emp where job= ' MANAGER", the result is shown in Figure 4.22.
(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; ―――――――――――――――――――――――――――――――――――――
"See CD-ROM File": \ 4th Chapter \4.4\444-2.sql.

Nested queries with "any" have the same nested query functionality as "some". Earlier SQL only allowed the use of "any", and later versions were used to differentiate "any" from English, introducing "some" and retaining "any" keywords.
4.4.5 nested query with "all"
In the command edit area, execute the following statement.
―――――――――――――――――――――――――――――――――――――
Select Emp.empno,emp.ename,emp.job,emp.sal from scott.emp where Sal >all (select Sal from scott.emp where job= ' Manag ER ');
―――――――――――――――――――――――――――――――――――――
Click the Execute button to see the results shown in Figure 4.24.
"See CD-ROM File": \ 4th Chapter \4.4\445.sql.

Nested queries with all are the same steps as "some".
(1) subquery, the result is shown in Figure 4.22.
(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;
―――――――――――――――――――――――――――――――――――――
"See CD-ROM File": \ 4th Chapter \4.4\445-2.sql.
4.4.6 nested query with "exists"
In the command edit area, execute the following statement.
―――――――――――――――――――――――――――――――――――――
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);
―――――――――――――――――――――――――――――――――――――
Click the Execute button to see the results shown in Figure 4.25.
"See CD-ROM File": \ 4th Chapter \4.4\446.sql.

Nested queries that are 4.4.7 and manipulated
And operation is the concept of the set in the set. The sum of the elements that belong to the set a or set B is a set. In the command edit area, execute the following statement.
―――――――――――――――――――――――――――――――――――――
(select Deptno from scott.emp) union (select Deptno from scott.dept); ―――――――――――――――――――――――――――――――――――――
Click the Execute button to see the results shown in Figure 4.26.
"See CD-ROM File": \ 4th Chapter \4.4\447.sql.

Nested queries for 4.4.8 operations
Cross operation is the concept of intersection in a set. The sum of elements that belong to set a and belong to set B is the intersection. In the command edit area, execute the following statement.
―――――――――――――――――――――――――――――――――――――
(select Deptno from Scott.emp) intersect (select Deptno from scott.dept); ―――――――――――――――――――――――――――――――――――――
Click the Execute button to see the results shown in Figure 4.27.
"See CD-ROM File": \ 4th Chapter \4.4\448.sql.

Nested queries for 4.4.9 error operations
The difference operation is the concept of the difference set in the set. The sum of elements that belong to set a and that does not belong to set B is the difference set.
In the command edit area, execute the following statement.
―――――――――――――――――――――――――――――――――――――
(select Deptno from scott.dept) minus (select Deptno from scott.emp);
―――――――――――――――――――――――――――――――――――――
Click the Execute button to see the results shown in Figure 4.28.
"See CD-ROM File": \ 4th Chapter \4.4\449.sql.

Nested query requirements for the and, and intersection operations, attributes have the same definition, including type and range of values.