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 :

SQL version : 5.7.12

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, Feb 08 '21 at 08:35
Try: select ('Homer', coalesce(null, 'null')) in (('Homer', coalesce(null, 'null'))); — Marko, Feb 08 '21 at 12:42

score 0 · Accepted Answer · answered Feb 08 '21 at 08:19

0

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.

answered Feb 08 '21 at 08:19

Tim Biegeleisen

502,043
27
286
360

Thank u for your answer . In my real life use case Im getting a long array of these 2-tuples . So writing this as a very long query is really not optimal solution . – yoty66 Feb 08 '21 at 08:23
1

You need parentheses – Strawberry Feb 08 '21 at 08:24
@Strawberry You need to re-read the precedence rules of SQL. `AND` has greater precedence than `OR`, so the logic as already written above is correct. – Tim Biegeleisen Feb 08 '21 at 08:28
1

I can see this going very wrong very quickly – Strawberry Feb 08 '21 at 08:30

score 0 · Answer 2 · answered Feb 08 '21 at 08:29

0

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));

answered Feb 08 '21 at 08:29

Imran Khan

311
4
9

Thank u for your answer . But tough very creative this isn't equivalent to searching by tuples . Since a ('Marge' , null) tuple would also be part of the result set . – yoty66 Feb 08 '21 at 08:51

score 0 · Answer 3 · answered Feb 08 '21 at 09:08

0

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

answered Feb 08 '21 at 09:08

Akina

39,301
5
14
25

score 0 · Answer 4 · answered Feb 08 '21 at 12:36

0

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')\;

answered Feb 08 '21 at 12:36

Gordon Linoff

1,242,037
58
646
786

MySQL multiple columns in IN clause with null values

4 Answers4