Subqueries¶
Subqueries are scalar expressions comprised of another query.
Forms¶
Scalar¶
Scalar subqueries are subqueries that return one row and one column.
Property | Description | Required |
---|---|---|
Input | Input relation | Yes |
IN
predicate¶
An IN
subquery predicate checks that the left expression is contained in the right subquery.
Examples¶
SELECT *
FROM t1
WHERE x IN (SELECT * FROM t2)
SELECT *
FROM t1
WHERE (x, y) IN (SELECT a, b FROM t2)
Property | Description | Required |
---|---|---|
Needles | Expressions whose existence will be checked | Yes |
Haystack | Subquery to check | Yes |
Set predicates¶
A set predicate is a predicate over a set of rows in the form of a subquery.
EXISTS
and UNIQUE
are common SQL spellings of these kinds of predicates.
Property | Description | Required |
---|---|---|
Operation | The operation to perform over the set | Yes |
Tuples | Set of tuples to check using the operation | Yes |
Set comparisons¶
A set comparison subquery is a subquery comparison using ANY
or ALL
operations.
Examples¶
SELECT *
FROM t1
WHERE x < ANY(SELECT y from t2)
Property | Description | Required |
---|---|---|
Reduction operation | The kind of reduction to use over the subquery | Yes |
Comparison operation | The kind of comparison operation to use | Yes |
Expression | Left-hand side expression to check | Yes |
Subquery | Subquery to check | Yes |