Oracle subquery details

The Oracle subquery is detailed. The subquery types are divided into single-row subqueries and multi-row subqueries Based on the query results (nested query results,

The Oracle subquery is detailed. The subquery types are divided into single-row subqueries and multi-row subqueries Based on the query results (nested query results,

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:

SELECT select_list

FROM table

WHERE expr operator

(SELECT select_list

FROM table );

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;