SELECT a FROM (SELECT Y.b, Y.c FROM Y WHERE Y.d = '3') AS X WHERE a = X.c AND b = X.bSubqueries
A subquery is a SQL query embedded within another SQL query. The query containing the subquery is the outer query.
Subquery types:
-
Scalar subquery - a subquery that returns only a single column with a single value. Scalar subqueries are a type of expression and can be used where single valued expressions are expected.
-
Correlated subquery - a subquery that contains a column reference to from the outer query.
-
Uncorrelated subquery - a subquery that contains no references to the outer sub-query.
Inline views
Subqueries in the FROM clause of the outer query (also known as "inline views") can return any number of rows and columns. This type of subquery must always be given an alias. An inline view is nearly identical to a traditional view. See also WITH Clause.
Example subquery in FROM clause (inline view)
Subqueries can appear anywhere where an expression or criteria is expected.
You can use subqueries in quantified criteria, the EXISTS predicate, the IN predicate,
and as Scalar subqueries.
Example subquery in WHERE using EXISTS
SELECT a FROM X WHERE EXISTS (SELECT 1 FROM Y WHERE c=X.a)Example quantified comparison subqueries
SELECT a FROM X WHERE a >= ANY (SELECT b FROM Y WHERE c=3)
SELECT a FROM X WHERE a < SOME (SELECT b FROM Y WHERE c=4)
SELECT a FROM X WHERE a = ALL (SELECT b FROM Y WHERE c=2)Example IN subquery
SELECT a FROM X WHERE a IN (SELECT b FROM Y WHERE c=3)See also Subquery Optimization.