Subqueries A subquery’s outer statement can be any one of:  SELECT ,  INSERT ,  UPDATE ,  DELETE ,  SET , or  DO . 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 13.2.11.12, “Restrictions on Subqueries” . In its simplest form, a subquery is a scalar subquery that returns a single value. A scalar subquery is a simple operand, and you can use it almost anywhere a single column value or literal is legal, and you can expect it to have those characteristics that all operands have: a data type, a length, an indication that it can be  NULL , and so on. For example: The subquery in this  SELECT  returns a single value ( ’abcde’ ) that has a data type of  CHAR , a length of 5, a character set and collation equal to the defaults in effect at  CREATE TABLE  time, and an indication that the value in the column can be  NULL . Nullability of the value selected by a scalar subquery is not copied because if the subquery result is empty, the result is  NULL . For the subquery just shown, if  t1  were empty, the result would be  NULL  even though  s2  is  NOT NULL . There are a few contexts in which a scalar subquery cannot be used. If a statement permits only a literal value, you cannot use a subquery. For example,  LIMIT  requires literal integer arguments, and  LOAD DATA  requires a literal string file name. You cannot use subqueries to supply these values. When you see examples in the following sections that contain the rather spartan construct  (SELECT column1 FROM t1) , imagine that your own code contains much more diverse and complex constructions. Suppose that we make two tables: Then perform a  SELECT : SELECT (SELECT s1 FROM t2) FROM t1; The result is  2  because there is a row in  t2  containing a column  s1  that has a value of  2 . In MySQL 8.0.19 and later, the preceding query can also be written like this, using  TABLE : SELECT (TABLE t2) FROM t1; A scalar subquery can be part of an expression, but remember the parentheses, even if the subquery is an operand that provides an argument for a function. For example: SELECT UPPER((SELECT s1 FROM t1)) FROM t2; The same result can be obtained in MySQL 8.0.19 and later using  SELECT UPPER((TABLE t1)) FROM t2 . 2684 CREATE TABLE t1 (s1 INT); INSERT INTO t1 VALUES (1); CREATE TABLE t2 (s1 INT); INSERT INTO t2 VALUES (2); CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL); INSERT INTO t1 VALUES(100, ’abcde’); SELECT (SELECT s2 FROM t1); 13.2.11.1 The Subquery as Scalar Operand