CODEWITHSUNDEEP
mysqljoinnull
user2511599
user2511599

Reputation: 852

Mysql join on null

I have 2 tables, each with 3 columns to join with.

table A
c1 c2   c3
10 NULL NULL
10 NULL 1
10 1    NULL

table B
c1 c2   c3
10 NULL NULL
10 NULL 1
10 1    NULL

I would like to join them so that NULL = NULL, so

SELECT * FROM a JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3

I would like it to join on NULL should match NULL. So that in the end I'm getting the 3 records:

table A+B
c1 c2   c3   c1 c2   c3
10 NULL NULL 10 NULL NULL
10 NULL 1    10 NULL 1
10 1    NULL 10 1    NULL

is this possible somehow? I have tried also with IFNULL but did'n get the results what I expect. I would be grateful if you could point me to the right direction. Many thanks!

Upvotes: 1

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use the NULL-safe equality operator:

SELECT *
FROM a JOIN
     b
     ON a.c1 <=> b.c1 AND a.c2 <=> b.c2 AND a.c3 <=> b.c3;

However, with your sample data, a join on the first column is sufficient:

SELECT *
FROM a JOIN
     b
     ON a.c1 = b.c1 ;

Upvotes: 2

Related Questions