MySQL subquery with multiple return values in WHERE clause returns empty set

Question

When using a subquery in a WHERE conditional the query returns empty set:

If I do: (1)

USE general_db;
SELECT * FROM general_db.master WHERE alert IN ('M1_001','G1_011');
+------+-------------+
| mkey | alert       |
+------+-------------+
|   17 | G1_011      |
|   19 | M1_001      |
+------+--------+----+

(1) works as expected.

Then I do: (2)

SELECT DISTINCT TABLE_NAME AS 'alert' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='maintenance_db';
+-------------+
| alert       |
+-------------+
| G1_011      |
| M1_001      |
+-------------+

(2) Works as expected.

But then if I use the second query (2) as a subquery of the first (1): (3)

SELECT * FROM general_db.master WHERE alert IN (SELECT DISTINCT TABLE_NAME AS 'alert' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='maintenance_db');

Empty set (0,00 sec)

(3) does not work as expected. I would think it should return the same results as in (1).

How can I do for the combination of both (3) to return the same results as in (1)? And why is this query (3) not working?

More info:

As you have may noticed, the table names in maintenance_db, may be contained in a column named alert in master table thats inside the general_db.

It is very strange. I am sure the subquery syntax is correct, this query works fine:

SELECT * FROM general_db.master WHERE alert IN (SELECT alert FROM general_db.master WHERE alert IN ('G1_011','M1_001'));

So I think it may be something related to query (2).

This works:

USE maintenance_db;
select * from M1_001;
select * from G1_011;

So the names are correct I guess.

Thanks

@GSerg Yes. I do not change to other databases. At least not explicitly. — Tomás Arturo Herrera Castro
– Tomás Arturo Herrera Castro, Commented Mar 29, 2020 at 17:57
Instead of usign master could you please use general_db.master and try — Alpesh Jikadra
– Alpesh Jikadra, Commented Mar 29, 2020 at 18:12
@AlpeshJikadra Just tried it out but there is no difference. I will edit the question to add this remark. — Tomás Arturo Herrera Castro
– Tomás Arturo Herrera Castro, Commented Mar 29, 2020 at 18:15
How can I be entirely sure the table names do not contain spaces or such ? Maybe they are not equal to 'M1_001' and may be 'M1_001 '. — Tomás Arturo Herrera Castro
– Tomás Arturo Herrera Castro, Commented Mar 29, 2020 at 18:22

sbrbot · Accepted Answer · 2020-03-29 20:05:06Z

1

Why you are fetching table names from INFORMATION_SCHEMA.COLUMNS instead of INFORMATION_SCHEMA.TABLES table!? There's no WHERE for filtering by column names in COLUMNS table!

What about:

SELECT * 
FROM general_db.master 
WHERE TRIM(alert) IN 
(
    SELECT UPPER(TABLE_NAME) 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA='maintenance_db'
)

Also instead of using inner query why not to use simple JOIN like

SELECT m.* 
FROM general_db.master m
JOIN INFORMATION_SCHEMA.TABLES t ON (UPPER(t.table_name)=TRIM(m.alert) AND t.table_schema='maintenance_db')

edited Mar 29, 2020 at 20:05

answered Mar 29, 2020 at 19:14

sbrbot

6,5457 gold badges49 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tomás Arturo Herrera Castro Over a year ago

The second query works! But the first returns an empty set (the subqueries work by themself, but not when joined as one)

sbrbot Over a year ago

Depends how your MySQL server is set to manage table names: case sensitive or case insensitive. One can setup it inside MYSQL.INI file. Try with UPPPER-ing table names. By default MySQL stores table names in lower case while your entries are upper cased.

Tomás Arturo Herrera Castro Over a year ago

Tried: SELECT * FROM general_db.master WHERE alert IN (SELECT UPPER(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='maintenance_db'); but it returns empty set.

sbrbot Over a year ago

Maybe your data has trimming spaces. Try using TRIM() function.

Tomás Arturo Herrera Castro Over a year ago

Tried different combinations using: TRIM(UPPER(TABLE_NAME)), TRIM(TABLE_NAME), TRIM(alert) and TRIM(UPPER(alert)), still returns empty set

Collectives™ on Stack Overflow

MySQL subquery with multiple return values in WHERE clause returns empty set

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related