Here is an example of a subquery: In this example,  SELECT * FROM t1 ...  is the  outer query  (or  outer statement ), and  (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 within parentheses. The main advantages of subqueries are: Here is an example statement that shows the major points about subquery syntax as specified by the SQL standard and supported in MySQL: •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.” 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:  DISTINCT ,  GROUP BY , ORDER BY ,  LIMIT , joins, index hints,  UNION  constructs, comments, functions, and so on. Beginning with MySQL 8.0.19,  TABLE  and  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  SELECT  or  TABLE  syntax; assuming that table  ts  is created using the statement CREATE TABLE ts VALUES ROW(2), ROW(4), ROW(6) , the statements shown here are all equivalent: Examples of  TABLE  subqueries are shown in the sections that follow. 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))); 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); SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); Subqueries 2683