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 ...