Description\n-----------\n\nThere are a number of limitations regarding subqueries, which are discussed below.\n\nThe following tables and data will be used in the examples that follow:\n\n``sql\nCREATE TABLE staff(name VARCHAR(10),age TINYINT);\n\nCREATE TABLE customer(name VARCHAR(10),age TINYINT);\n`\n\n`sql\nINSERT INTO staff VALUES \n('Bilhah',37), ('Valerius',61), ('Maia',25);\n\nINSERT INTO customer VALUES \n('Thanasis',48), ('Valerius',61), ('Brion',51);\n`\n\nORDER BY and LIMIT\n\nTo use ORDER BY or limit LIMIT in subqueries both must be used.. For example:\n\n`sql\nSELECT  FROM staff WHERE name IN (SELECT name FROM customer ORDER BY name);\n+----------+------+\n| name     | age  |\n+----------+------+\n| Valerius |   61 |\n+----------+------+\n`\n\nis valid, but\n\n`sql\nSELECT  FROM staff WHERE name IN (SELECT NAME FROM customer ORDER BY name LIMIT 1);\nERROR 1235 (42000): This version of MariaDB doesn't \n  yet support 'LIMIT & IN/ALL/ANY/SOME subquery'\n`\n\nis not.\n\nModifying and Selecting from the Same Table\n\nIt's not possible to both modify and select from the same table in a subquery. For example:\n\n`sql\nDELETE FROM staff WHERE name = (SELECT name FROM staff WHERE age=61);\nERROR 1093 (HY000): Table 'staff' is specified twice, both \n  as a target for 'DELETE' and as a separate source for data\n`\n\nRow Comparison Operations\n\nThere is only partial support for row comparison operations. The expression in\n\n`sql\nexpr op {ALL|ANY|SOME} subquery,\n`\n\nmust be scalar and the subquery can only return a single column.\n\nHowever, because of the way IN is implemented (it is rewritten as a sequence of = comparisons and AND), the expression in\n\n`sql\nexpression [NOT] IN subquery\n`\n\nis permitted to be an n-tuple and the subquery can return rows of n-tuples.\n\nFor example:\n\n`sql\nSELECT  FROM staff WHERE (name,age) NOT IN (\n  SELECT name,age FROM customer WHERE age >=51]\n);\n+--------+------+\n| name   | age  |\n+--------+------+\n| Bilhah |   37 |\n| Maia   |   25 |\n+--------+------+\n`\n\nis permitted, but\n\n`sql\nSELECT  FROM staff WHERE (name,age) = ALL (\n  SELECT name,age FROM customer WHERE age >=51\n);\nERROR 1241 (21000): Operand should contain 1 column(s)\n`\n\nis not.\n\nCorrelated Subqueries\n\nSubqueries 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.\n\nStored Functions\n\nA subquery can refer to a stored function which modifies data. This is an extension to the SQL standard, but can result in indeterminate outcomes. For example, take:\n\n`sql\nSELECT ... WHERE x IN (SELECT f() ...);\n``\n\nwhere _f()_ inserts rows. The function _f()_ could be executed a different number of times depending on how the optimizer chooses to handle the query.\n\nThis sort of construct is therefore not safe to use in replication that is not row-based, as there could be different results on the master and the slave.\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/joins-subqueries/subqueries/subquery-limitations