What is a subquery?

When the result of a query is the condition of another query, it is called a subquery, and a subquery is another SELECT statement within the SQL statement.

A subquery can be included wherever it is an expression in the SELECT, INSERT, UPDATE, or DELETE command, and it can even be included in another subquery to complete a more complex query. Let's take a look at the subquery through an example.

Instance queries the employee information of the department named RESEARCH

Query the employee information with the department name (dname) of RESEARCH in the employee table emp as follows.

01 SELECT empno,ename,jobFROM emp02 WHERE deptno= (SELECT deptno FROM dept03 WHERE dname='RESEARCH')

Input through SQL Developer, the query results are shown in figure 7.1.

Figure 7.1 shows the employee information with the department name RESEARCH

Because the title requires to query the department name is RESEARCH employee information, but in the employee table emp does not have the department name (dname) field, only the department number (deptno) field, then as long as know the department name is RESEARCH number on it, this requirement is very simple, you can query through the department table dept, the code is as follows.

01 select deptno from dept02 where dname='RESEARCH'

The query result is shown in figure 7.2, through which you can get the department number, which is 20.

Figure 7.2 Department number with department name RESEARCH

Now the title can be simplified to query the employee information of department number 20, then the code is as follows.

01 select empno,ename,jobfrom emp02 where deptno=20

The query results are shown in figure 7.1.

If you connect these two query statements, you will form a subquery. It should be noted that the inner query is called a subquery and the outer query is called an outer query, as shown in figure 7.3.

Figure 7.3 subquery and external query

Note: the requirements of this example can also be realized by multi-table association query, that is, you can replace the above code with the following code. Comparing the following code with the code in example 01, you can see that the use of subqueries is more flexible, more powerful, and easier to understand than multi-table association queries.

01 select empno,ename,job02 from emp join dept on emp.deptno=dept.deptno 03 wheredept.dname = 'RESEARCH'