Multi-row subquery of oracle-28-subquery & subquery of multiple columns query

One, the basic type of sub -Query multiple rows subquery

(1) Use the in operator for multi-line subqueries. (Summary: Multiple rows of subqueries available after the in operator)

For example: Query the highest-paid employee information for each position.

Sql>select ename, Job, Sal from EMP where sal in (select Max (SAL) from EMP Group by Job);--Single row subquery

Or

Sql>select ename, Job, Sal from EMP where (sal,job) in (select Max (SAL), job from EMP Group by Job);-- Dolez Query

(2) Use the EXISTS operator for multi-line subqueries. (Summary: Multi-line subquery available after exists operator)

The purpose of introducing exists: in some cases, only subqueries are required to return a true or False value. You can use the EXISTS operator to define a subquery if you only consider whether the criteria are met and the data itself is not important.

Like what:

Sql>select empno, ename, Sal from EMP where exists (SELECT * FROM dept where deptno = ' 40 ')

"Note" Above SQL Statement if subqueries (SELECT * FROM dept wheredeptno = ' + ') have the result, then exists The preceding statement executes if exists The back is not the result, then the front of it will not be executed.

(3) Use the all operator for multi-line subqueries. (Summary: Multiple rows of subqueries available after the all operator)

Like what:

Sql>select empno, ename, Sal, job from emp where sal < All (select AVG (SAL) from EMP Group by Job) ;

"Attention" All the operator compares a subquery to return each value in the list.

<all is less than the minimum

>all is greater than the maximum

=all meaningless, generally not written.

Explanation: A subquery (Selectavg (SAL) from an EMP group by job) calculates the average wage for each position, so that the average wage for different positions is different, with a high and low, so the <all is less than the minimum, that is, less than the minimum average wage. The same >all is greater than the maximum, that is, greater than the maximum average wage.

(4) Use the any operator for multi-line subqueries. (Summary: Multiple rows of subqueries available after any operator)

Like what:

Sql>select empno, ename, Sal, job from emp where sal > No (select AVG (SAL) from GROUP by Jon)

"Attention" any operator followed by a multiline subquery returns each value in the list.

<any is less than the maximum

>any is greater than the minimum.

Example 1: Practice The All operator followed by a multiline subquery.

Solution: Current SC table data:

Now divide the same course into a group, calculating evenly, i.e. averaging by the same grouping of CNO values:

Use the all operator followed by a multiline subquery to view student information that is less than the minimum value (79.25) for all evenly divided:

Use the all operator followed by a multiline subquery to view the student information that is greater than the maximum value (80.32) for all evenly divided:

Example 2: Practice The Any operator followed by a multiline subquery.

Solution: Current SC table data:

Now divide the same course into a group, calculating evenly, i.e. averaging by the same grouping of CNO values:

Use the any operator followed by a multiline subquery to view the student information that is less than the maximum value (80.32) of all evenly divided:

Use the any operator followed by a multiline subquery to view the student information that is greater than the minimum value (79.25) for all evenly divided:

Second, the sub-query of the basic types of multi-column subquery

Dolez query to return multiple columns.

For example: query which employee's salary is the highest in any position.

Sql>selecte mpno, ename, Sal, job from EMP where (SAL, Job) in (select Max (SAL), job from EMP Group by Job);

Summary

A single-row subquery is a subquery in which only 1 a column name , such as (selectavg (SAL)from the EMP Group by Job); Multi-column subquery is more than 1 in a subquery A column name , such as (selectmax (SAL), job from theEMP Group by Job).

Multi-row subquery of oracle-28-subquery & subquery of multiple columns query