ALL and ANY operators

ALL and ANY operators can be used in podzapytaniach that return more than one row. They are given in WHERE and HAVING with comparison operators.

ANY Operator - a row is selected, if the expression is consistent with at least one value selected in the subquery.

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL > ANY (SELECT DISTINCT SAL
FROM EMP
WHERE DEPTNO = 10);

The lowest salary in the department 10 of 1300. Query selects all those who earn more than 1300.

In order not to have been selected many times the same earnings (3000 is greater, and from 1300 and from 2450 - have been selected twice), often used together with the operators of ALL and ANY DISTINCT keyword.

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10;

Operator ALL - the condition must be satisfied by all the values ​​selected in the subquery.

Chose all employees who earn more than anyone in the department 30:

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL > ALL (SELECT DISTINCT SAL
FROM EMP
WHERE DEPTNO = 30)
ORDER BY SAL;

The highest salary in the department 30 is 2850. Query selects all those who earn more than 2850.

The HAVING clause with nested queries

Subqueries can also be in a HAVING clause (remember - the HAVING clause refers to groups of rows). There is no limit to the number of levels of nested subqueries.

To select events in which the average wage is higher than the average wage in the job 'MANAGER', we write:

SELECT JOB, AVG (SAL)
FROM EMP
HAVING AVG (SAL) > (SELECT AVG (SAL)
FROM EMP
WHERE JOB = 'MANAGER')
GROUP BY JOB;

To select the position where the lowest average earnings, we write:

SELECT JOB, AVG (SAL)
FROM EMP
HAVING AVG (SAL) = (SELECT MIN (AVG (SAL))
FROM EMP
GROUP BY JOB)
GROUP BY JOB;

Sort data in a subquery

The subquery must be no ORDER BY clause. The ORDER BY clause can only occur once for the entire query, and then must come last.

Inquiries correlated

The block is a special case of correlated nested query. Normal subquery is executed once, at the very beginning, and his results link external inquiry. For inquiries correlated subquery is executed for each row in the outer query.

To find people who earn less than the average in their professions, we write:

SELECT ENAME, SAL, DEPTNO, AVG (SAL)
FROM EMP E
WHERE SAL < (SELECT AVG (SAL)
FROM EMP
WHERE JOB = E.JOB)
ORDER BY JOB;

Operator EXIST

In the case of correlated queries sometimes we are only interested whether the row meets the specified conditions exist. Then we use the EXIST operator.

To use the EXIST operator to find employees who have subordinates, write:

SELECT ENAME, SAL, DEPTNO
FROM EMP E
WHERE EXISTS (SELECT empno
FROM EMP
WHERE EMP.MGR = E.EMPNO)
ORDER BY DEPTNO;

To find a department where no one works:

SELECT DNAM, DEPTNO
FROM DEPT D
WHERE NOT EXISTS (SELECT 1
FROM EMP E
WHERE E.DEPTNO = D.DEPTNO)
ORDER BY DEPTNO;

Note that the internal inquiry does not have to return any value from the table, it is important whether he will be returned to the line, not its contents.