- 126.96.36.199 The Subquery as Scalar Operand
- 188.8.131.52 Comparisons Using Subqueries
- 184.108.40.206 Subqueries with ANY, IN, or SOME
- 220.127.116.11 Subqueries with ALL
- 18.104.22.168 Row Subqueries
- 22.214.171.124 Subqueries with EXISTS or NOT EXISTS
- 126.96.36.199 Correlated Subqueries
- 188.8.131.52 Derived Tables
- 184.108.40.206 Lateral Derived Tables
- 220.127.116.11 Subquery Errors
- 18.104.22.168 Optimizing Subqueries
- 22.214.171.124 Restrictions on Subqueries
A subquery is a
within another statement.
All subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.
Here is an example of a subquery:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
In this example,
SELECT * FROM t1 ... is the
outer query (or outer
(SELECT column1 FROM
t2) is the subquery. We say that
the subquery is nested within the outer
query, and in fact it is possible to nest subqueries within other
subqueries, to a considerable depth. A subquery must always appear
The main advantages of subqueries are:
They allow queries that are structured so that it is possible to isolate each part of a statement.
They provide alternative ways to perform operations that would otherwise require complex joins and unions.
Many people find subqueries more readable than complex joins or unions. Indeed, it was the innovation of subqueries that gave people the original idea of calling the early SQL “Structured Query Language.”
Here is an example statement that shows the major points about subquery syntax as specified by the SQL standard and supported in MySQL:
DELETE FROM t1 WHERE s11 > ANY (SELECT COUNT(*) /* no hint */ FROM t2 WHERE NOT EXISTS (SELECT * FROM t3 WHERE ROW(5*t2.s1,77)= (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM (SELECT * FROM t5) AS t5)));
A subquery can return a scalar (a single value), a single row, a single column, or a table (one or more rows of one or more columns). These are called scalar, column, row, and table subqueries. Subqueries that return a particular kind of result often can be used only in certain contexts, as described in the following sections.
There are few restrictions on the type of statements in which
subqueries can be used. A subquery can contain many of the
keywords or clauses that an ordinary
SELECT can contain:
comments, functions, and so on.
Beginning with MySQL 8.0.19,
VALUES statements can be used
in subqueries. Subqueries using
VALUES are generally more verbose
versions of subqueries that can be rewritten more compactly using
set notation, or with
TABLE syntax; assuming that table
ts is created using the statement
ts VALUES ROW(2), ROW(4), ROW(6), the statements shown
here are all equivalent:
SELECT * FROM tt WHERE b > ANY (VALUES ROW(2), ROW(4), ROW(6)); SELECT * FROM tt WHERE b > ANY (2, 4, 6); SELECT * FROM tt WHERE b > ANY (SELECT * FROM ts); SELECT * FROM tt WHERE b > ANY (TABLE ts);
TABLE subqueries are
shown in the sections that follow.
For information about how the optimizer handles subqueries, see Section 8.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”. For a discussion of restrictions on subquery use, including performance issues for certain forms of subquery syntax, see Section 126.96.36.199, “Restrictions on Subqueries”.