Subqueries using the ANY keyword will return true if the comparison returns true for at least one row returned by the subquery.

Syntax

The required syntax for an ANY or SOME quantified comparison is:

Or:

• scalar_expression may be any expression that evaluates to a single value.

• comparison_operator may be any one of =, >, <, >=, <=, <> or !=

ANY returns:

• TRUE if the comparison operator returns TRUE for at least one row returned by the Table subquery.

• FALSE if the comparison operator returns FALSE for all rows returned by the Table subquery, or Table subquery has zero rows.

• NULL if the comparison operator returns

SOME is a synonym for ANY, and IN is a synonym for = ANY .

Examples

100 is greater than two of the three values, and so the expression evaluates as true.

SOME is a synonym for ANY:

IN is a synonym for = ANY, and here there are no matches, so no results are returned:

Reading this query, the results may be counter-intuitive. It may seem to read as SELECT * FROM sq1 WHERE num does not match any results in sq2. Since it does match 100, it could seem that the results are incorrect. However, the query returns a result if the match does not match any of sq2. Since 100 already does not match 40, the expression evaluates to true immediately, regardless of the 100's matching. It may be more easily readable to use SOME in a case such as this:

_{This page is licensed: CC BY-SA / Gnu FDL}

Subqueries using the ALL keyword will return true if the comparison returns true for each row returned by the subquery, or the subquery returns no rows.

Syntax

• scalar_expression may be any expression that evaluates to a single value.

• comparison_operator may be any one of: =, >, <, >=, <=, <> or !=

ALL returns:

• NULL if the comparison operator returns NULL for at least one row returned by the Table subquery or scalar_expression returns NULL.

• FALSE if the comparison operator returns FALSE for at least one row returned by the Table subquery.

NOT IN is an alias for <> ALL.

Examples

Since 100 > all of 40,50 and 60, the evaluation is true and the row is returned.

Adding a second row to sq1, where the evaluation for that record is false:

Adding a new row to sq2, causing all evaluations to be false:

When the subquery returns no results, the evaluation is still true:

Evaluating against a NULL will cause the result to be unknown, or not true, and therefore return no rows:

_{This page is licensed: CC BY-SA / Gnu FDL}

Syntax

SELECT ... WHERE EXISTS <Table subquery>

Description

Subqueries using the EXISTS keyword will return true if the subquery returns any rows. Conversely, subqueries using NOT EXISTS will return true only if the subquery returns no rows from the table.

EXISTS subqueries ignore the columns specified by the of the subquery, since they're not relevant. For example,

and

produce identical results.

Examples

_{This page is licensed: CC BY-SA / Gnu FDL}

A scalar subquery is a subquery that returns a single value. This is the simplest form of a subquery, and can be used in most places a literal or single column value is valid.

The data type, length and character set and collation are all taken from the result returned by the subquery. The result of a subquery can always be NULL, that is, no result returned. Even if the original value is defined as NOT NULL, this is disregarded.

A subquery cannot be used where only a literal is expected, for example LOAD DATA INFILE expects a literal string containing the file name, and LIMIT requires a literal integer.

Examples

CREATE TABLE sq1 (num TINYINT);

CREATE TABLE sq2 (num TINYINT);

INSERT INTO sq1 VALUES (1);

INSERT INTO sq2 VALUES (10* (SELECT num FROM sq1));

SELECT * FROM sq2;
+------+
| num  |
+------+
|   10 |
+------+

Inserting a second row means the subquery is no longer a scalar, and this particular query is not valid:

No rows in the subquery, so the scalar is NULL:

A more traditional scalar subquery, as part of a WHERE clause:

_{This page is licensed: CC BY-SA / Gnu FDL}

A can quite often, but not in all cases, be rewritten as a .

Rewriting Subqueries as JOINS

A subquery using IN can be rewritten with the DISTINCT keyword. Consider this query:

It can be rewritten like this:

NOT IN

There are a number of limitations regarding subqueries, which are discussed below.

The following tables and data will be used in the examples that follow:

ORDER BY and LIMIT

To use ORDER BY or limit LIMIT in subqueries both must be used.. For example:

is valid, but

is not.

Modifying and Selecting from the Same Table

It's not possible to both modify and select from the same table in a subquery. For example:

Row Comparison Operations

There is only partial support for row comparison operations. The expression in

must be scalar and the subquery can only return a single column.

However, because of the way IN is implemented (it is rewritten as a sequence of = comparisons and AND), the expression in

is permitted to be an n-tuple and the subquery can return rows of n-tuples.

For example:

is permitted, but

is not.

Correlated Subqueries

Subqueries in the FROM clause cannot be correlated subqueries. They cannot be evaluated for each row of the outer query since they are evaluated to produce a result set during when the query is executed.

Stored Functions

A subquery can refer to a which modifies data. This is an extension to the SQL standard, but can result in indeterminate outcomes. For example, take:

where f() inserts rows. The function f() could be executed a different number of times depending on how the optimizer chooses to handle the query.

This sort of construct is therefore not safe to use in replication that is not , as there could be different results on the master and the slave.

_{This page is licensed: CC BY-SA / Gnu FDL}

Although subqueries are more commonly placed in a WHERE clause, they can also form part of the FROM clause. Such subqueries are commonly called derived tables.

If a subquery is used in this way, you must also use an AS clause to name the result of the subquery.

ORACLE mode

MariaDB starting with

Correlation Column List

MariaDB starting with

Examples

Assume that, given the data above, you want to return the average total for all students. In other words, the average of Chun's 148 (75+73), Esben's 74 (43+31), etc.

You cannot do the following:

A subquery in the FROM clause is however permitted:

The following is permitted:

In this example, the second column of the derived table dt is used both within (WHERE c2 > 0), and outside, (WHERE a2 > 10), the specification. Both conditions apply to t1.c2.

_{This page is licensed: CC BY-SA / Gnu FDL}

A row subquery is a subquery returning a single row, as opposed to a scalar subquery, which returns a single column from a row, or a literal.

Examples

CREATE TABLE staff (name VARCHAR(10), age TINYINT);

CREATE TABLE customer (name VARCHAR(10), age TINYINT);

INSERT INTO staff VALUES ('Bilhah',37), ('Valerius',61), ('Maia',25);

INSERT INTO customer VALUES ('Thanasis',48), ('Valerius',61), ('Brion',51);

SELECT * FROM staff WHERE (name,age) = (SELECT name,age FROM customer WHERE name='Valerius');
+----------+------+
| name     | age  |
+----------+------+
| Valerius |   61 |
+----------+------+

Finding all rows in one table and also in another:

SELECT name,age FROM staff WHERE (name,age) IN (SELECT name,age FROM customer);
+----------+------+
| name     | age  |
+----------+------+
| Valerius |   61 |
+----------+------+

_{This page is licensed: CC BY-SA / Gnu FDL}