Review restrictions on subquery usage. This page details unsupported operations, such as modifying a table while selecting from it in a subquery.
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:
To use ORDER BY or limit LIMIT in subqueries both must be used.. For example:
is valid, but
is not.
It's not possible to both modify and select from the same table in a subquery. For example:
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.
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.
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
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 IN (SELECT name FROM customer ORDER BY name);
+----------+------+
| name | age |
+----------+------+
| Valerius | 61 |
+----------+------+SELECT * FROM staff WHERE name IN (SELECT NAME FROM customer ORDER BY name LIMIT 1);
ERROR 1235 (42000): This version of MariaDB doesn't
yet support 'LIMIT & IN/ALL/ANY/SOME subquery'DELETE FROM staff WHERE name = (SELECT name FROM staff WHERE age=61);
ERROR 1093 (HY000): Table 'staff' is specified twice, both
as a target for 'DELETE' and as a separate source for dataexpr op {ALL|ANY|SOME} subquery,expression [NOT] IN subquerySELECT * FROM staff WHERE (name,age) NOT IN (
SELECT name,age FROM customer WHERE age >=51]
);
+--------+------+
| name | age |
+--------+------+
| Bilhah | 37 |
| Maia | 25 |
+--------+------+SELECT * FROM staff WHERE (name,age) = ALL (
SELECT name,age FROM customer WHERE age >=51
);
ERROR 1241 (21000): Operand should contain 1 column(s)SELECT ... WHERE x IN (SELECT f() ...);