MySQL multiple columns in IN clause with null values

0
votes

I want to query over two columns as 2-tuples , where one can be null.

example :

    +-------------------------+
    |   first    |     last   |
    +-------------------------+
    |   Homer    |     NULL   |
    |   Marge    |    Simpson |
    +-------------------------+

Something like:

Select * from cartoons where (first, last ) in ((Homer ,NULL  ), ( Marge ,Simpson) ) ;

(Which should retrieve both Homer's and Marge's rows)

I am using the following query for test:

select  ('Homer', null)  in (('Homer',  null));

which returns null.

Reference to related questions :

SQL version : 5.7.12

4
mysqlsqlwhere-in
why don't change those null value with IFNULL() to avoid null problem? or is there any limitation so you can't change the data at all? - T. Peter
What is your version of MySql? - forpas
added to question - yoty66
Try: select ('Homer', coalesce(null, 'null')) in (('Homer', coalesce(null, 'null'))); - Marko

4 Answers

0
votes

You can't use the tuple syntax in this case, which uses equality comparisons under the hood. Instead, use the longer version:

SELECT *
FROM cartoons
WHERE first = 'Homer' AND last IS NULL OR first = 'Marge' AND last = 'Simpson';

Just for reference, here is what your current query is actually saying:

SELECT *
FROM cartoons
WHERE first = 'Homer' AND last = NULL OR first = 'Marge' AND last = 'Simpson';

Note that comparing a column directly to NULL is semantically incorrect.

0
votes

Try This:

SELECT * FROM cartoons WHERE (first in (Array1)  AND last IS NULL) OR (last in (Array2)  AND first IS NULL) OR (first in (Array1)  AND last in (Array2));
0
votes
SELECT * 
FROM cartoons 
JOIN ( SELECT 'Homer' first, NULL last
       UNION ALL
       SELECT 'Marge', 'Simpson' ) criteria ON cartoons.first <=> criteria.first
                                           AND cartoons.last  <=> criteria.last
0
votes

Use NULL-safe comparisons, which in MySQL uses <=>. Assuming that only the second column can be NULL:

Select c.*
from cartoons c
where (first = 'Homer' and last <=> NULL) or
      (first = 'Marge' and last <=> 'Simpson')\;