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
– T. Peter, Commented Feb 8, 2021 at 8:35
Try: select ('Homer', coalesce(null, 'null')) in (('Homer', coalesce(null, 'null'))); — Marko
– Marko, Commented Feb 8, 2021 at 12:42

Tim Biegeleisen · Accepted Answer · 2021-02-08 08:19:32Z

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 8, 2021 at 8:19

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

4 Comments

yoty66 Over a year ago

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 .

Strawberry Over a year ago

You need parentheses

Tim Biegeleisen Over a year ago

@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.

Strawberry Over a year ago

I can see this going very wrong very quickly

Imran Khan · Accepted Answer · 2021-02-08 08:29:33Z

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 8, 2021 at 8:29

Imran Khan

3214 silver badges10 bronze badges

1 Comment

yoty66 Over a year ago

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 .

Akina · Accepted Answer · 2021-02-08 09:08:09Z

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 8, 2021 at 9:08

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2021-02-08 12:36:29Z

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 8, 2021 at 12:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL multiple columns in IN clause with null values

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related