Mysql – How to use MySQL variables in subqueries

MySQLselectsubquery

I'm trying to run the following query, but it keeps getting "reduced" and failing. Cannot get MySQL to provide any information as to why.

Here's the query:

SELECT * FROM animals WHERE name IN(SELECT @animal_names);

Here's the information underneath the variable and table.

TABLE:

mysql> mysql> SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

VARIABLE:

mysql> SELECT GROUP_CONCAT(QUOTE(name)) INTO @animal_names FROM animals WHERE name LIKE '%%';
Query OK, 1 row affected (0.01 sec)



mysql>
mysql> SELECT @animal_names;
+-----------------------------------------------+
| @animal_names                                 |
+-----------------------------------------------+
| 'dog','cat','penguin','lax','whale','ostrich' |
+-----------------------------------------------+
1 row in set (0.00 sec)

ISSUE:

mysql> SELECT * FROM animals WHERE name IN(SELECT @animal_names);
Empty set (0.00 sec)



mysql> EXPLAIN SELECT * FROM animals WHERE name IN(SELECT @animal_names);
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | animals | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set, 1 warning (0.00 sec)



Note (Code 1249): Select 2 was reduced during optimization

MySQL refuses to tell me why the "SELECT @animal_ids" is not working! Is there some MySQL variable that needs to be adjusted for that subquery to work?

This is a test sample. I'm trying to do this on a larger scale with about 800 values in a list. I want the list in a variable so I don't have to do A SELECT query multiple times to DELETE, INSERT, SELECT the values I need across multiple databases and tables.

Obviously this can be easily done with a Python or Golang script, but I'm trying to do this in MySQL directly. While I appreciate it, please refrain from offering script-based solutions.

Best Answer

The variable contains ONE string, but to work properly the subquery in IN() needs to return different items as multiple rows.

SELECT * FROM animals WHERE name IN(SELECT @animal_names);

is translated to

SELECT * FROM animals WHERE name IN(SELECT '\'dog\',\'cat\',\'penguin\',\'lax\',\'whale\',\'ostrich\'');

MySQL variable cannot hold table resultset so this is not directly possible. You would have to create a subquery to extract separate values to rows by joining some counter table and parsing the string. Or you may use dynamic sql which allows to take a string and parse it - that way you would define new variable containing entire select with the IN list filled by your current variable, and then prepare a statement from that string representation (sort of SQL eval). But probably best would be to just use the query direcly as a subquery instead of filling the variable. Newer version should materialize such independent subquery automatically. For good performance on older versions, you might want to create a temporary table instead and use JOIN and not IN.

Just a note - GROUP_CONCAT() is not very safe as there is a limit (variable) to how long string it can return and some results may be lost (with a warning) when the limit is exceeded.

Related Question