3.2.8 Subquery

3.2.8.1 The Subquery as Scalar Operand

For example :

SELECT (SELECT s2 FROM t1);  
SELECT (SELECT s1 FROM t2) FROM t1;  
SELECT UPPER((SELECT s1 FROM t1)) FROM t2;  

3.2.8.2 Comparisons Using Subqueries

The most common use of a subquery is in the form:

non_subquery_operand comparison_operator (subquery)

Where comparison_operator is one of these operators:

=    >    <    >=    <=    <>    !=    <=>

MySQL also permits this construct:

non_subquery_operand LIKE (subquery)

3.2.8.3 Subqueries with ANY, IN, or SOME

Syntax:

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

Where comparison_operator is one of these operators:

=    >    <    >=    <=    <>    !=

3.2.8.4 Subqueries with ALL

Syntax:

operand comparison_operator ALL (subquery)

3.2.8.5 Subqueries with EXISTS or NOT EXISTS

For example:

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

Not support Correlated Subqueries for now.

3.2.8.6 Derived Tables (Subqueries in the FROM Clause)

SELECT ... FROM (subquery) [AS] tbl_name ...