skip to main content
Supported SQL and Extensions : Oracle Eloqua, HubSpot, and Marketo : Subqueries
 

Try DataDirect Cloud Now

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.

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)
* Correlated Subqueries