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.
Syntax
value [NOT] IN (value1, value2,...)
ORvalue [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.
Syntax
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).
Syntax
UNIQUE (subquery)
Example
SELECT * FROM dept d WHERE UNIQUE
(SELECT deptno FROM emp e WHERE e.deptno = d.deptno)
