Subquery: When a query result is a condition of another query, it is called a subquery.
Notes for using subqueries:
Subqueries can be nested in multiple layers
The subquery must be enclosed in parentheses ().
Subquery Syntax:
SELECTSelect_list
FROMTable
WHEREExpr operator
(SELECTSelect_list
FROMTable);
L subquery (internal query) is executed once before the primary query.
L The subquery results are used by the primary query (external query ).
Example: query the employee information whose salary is greater than JONES
The analysis process is as follows:
First, query the employee salary of JONES: Result 2975
SQL> select sal from emp where ename = 'Jones ';
In fact, we want to query the following information:
SQL> select * from emp where sal> 2975;
// The results of the preceding subquery are as follows:
SQL> select * from emp where sal> (select sal from emp where ename = 'Jones ');
Note:
L subqueries should be included in brackets.
L place the subquery on the right of the comparison condition.
Subqueries are classified into single-row subqueries and multi-row subqueries Based on the query results (nested query results,
Note:
L single-row operators correspond to single-row subqueries, while multiple-row operators correspond to multiple-row subqueries.
Single Row Operator
>,>=, <, <=, <>, =
Example:
// Query information about employees in the same position numbered 7876 and whose salaries are greater than 7521
SQL> select * from emp where job = (select job from emp where empno = 7876) and sal> (select sal from emp where empno = 7521 );
// Subquery contains group functions
SQL> select * from emp where sal> (select avg (nvl (sal, 0) from emp );
// Subquery the department with the having clause to query the department with the minimum wage greater than the minimum wage of Department 20 and the minimum wage
SQL> select deptno, min (sal) from emp group by deptno having min (sal)> (select min (sal) from emp where deptno = 20 );
Note: The subquery can return empty rows without any query results.
Multi-row subquery
L multiple rows are returned.
L use multi-line comparison operators.
Operators include:
Operator |
Description |
In |
Equal to any |
Any |
Any value returned by the subquery is the same and some |
All |
Compare with all values returned by the subquery |
Exists |
|
// Query the information of any employee whose salary is less than the employee's CLERK and does not include the information of the employee whose job is CLERK.
SQL> select * from emp where sal <any (select sal from emp where job = 'cler') and job <> 'cler ';
// Compare all with all values> all indicates that the value is greater than the maximum value of the query result.
SQL> select * from emp where sal> all (select sal from emp where job = 'cler') and job <> 'cler ';
// The employee information with the same position number as 10 does not include the employee information.
SQL> select * from emp where job in (select job from emp where deptno = 10) and deptno <> 10;