MySQL multiple columns in IN clause with null values

Question

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 :

• MySQL multiple columns in IN clause
• Why MYSQL IN keyword not considering NULL values

SQL version : 5.7.12

Answer 1

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.