Subqueries

A query is an operation that retrieves data from one or more tables or views. In this reference, a top-level query is called a Select statement, and a query nested within a Select statement is called a subquery.

A subquery is a query expression that appears in the body of another expression such as a Select, an Update, or a Delete statement. In the following example, the second Select statement is a subquery:

SELECT * FROM emp WHERE deptno IN

(SELECT deptno FROM dept)

IN Predicate

The In predicate specifies a set of values against which to compare a result set. If the values are being compared against a subquery, only a single column result set is returned.

Grammar

value [NOT] IN (value1, value2,...)

OR

value [NOT] IN (subquery)

Example

SELECT * FROM emp WHERE deptno IN

(SELECT deptno FROM dept WHERE dname <> 'Sales')

EXISTS Predicate

The Exists predicate is true only if the cardinality of the subquery is greater than 0; otherwise, it is false.

Grammar

EXISTS (subquery)

Example

SELECT empno, ename, deptno FROM emp e WHERE EXISTS

(SELECT deptno FROM dept WHERE e.deptno = dept.deptno)

UNIQUE Predicate

The Unique predicate is used to determine whether duplicate rows exist in a virtual table (one returned from a subquery).

Grammar

UNIQUE (subquery)

Example

SELECT * FROM dept d WHERE UNIQUE

(SELECT deptno FROM emp e WHERE e.deptno = d.deptno)

Correlated Subqueries

A correlated subquery is a subquery that references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a Select, Update, or Delete statement.

A correlated subquery answers a multiple-part question in which the answer depends on the value in each row processed by the parent statement. For example, you can use a correlated subquery to determine which employees earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department.

Grammar

SELECT select_list

FROM table1 t_alias1

WHERE expr rel_operator

(SELECT column_list

FROM table2 t_alias2

WHERE t_alias1.column

rel_operator t_alias2.column)

UPDATE table1 t_alias1

SET column =

(SELECT expr

FROM table2 t_alias2

WHERE t_alias1.column = t_alias2.column)

DELETE FROM table1 t_alias1

WHERE column rel_operator

(SELECT expr

FROM table2 t_alias2

WHERE t_alias1.column = t_alias2.column)

NOTE: Correlated column names in correlated subqueries must be explicitly qualified with the table name of the parent.

Examples

Example A

The following statement returns data about employees whose salaries exceed their department average. This statement assigns an alias to emp, the table containing the salary information, and then uses the alias in a correlated subquery:

SELECT deptno, ename, sal FROM emp x WHERE sal >

(SELECT AVG(sal) FROM emp WHERE x.deptno = deptno)

ORDER BY deptno

Example B

This is an example of a correlated subquery that returns row values:

SELECT * FROM dept "outer" WHERE 'manager' IN

(SELECT managername FROM emp

WHERE "outer".deptno = emp.deptno)

Example C

This is an example of finding the department number (deptno) with multiple employees:

SELECT * FROM dept main WHERE 1 <

(SELECT COUNT(*) FROM emp WHERE deptno = main.deptno)

Example D

This is an example of correlating a table with itself:

SELECT deptno, ename, sal FROM emp x WHERE sal >

(SELECT AVG(sal) FROM emp WHERE x.deptno = deptno)