CODEWITHSUNDEEP
mysqlsqljoinnullable
Per
Per

Reputation: 483

How to join MySQL tables using a nullable column?

I'm a little bit out of practice with MySQL, so I hope I can find some advice for my problem here.

Basically I have two tables, call them A and B just for convenience. Both tables have a nullable column C of type varchar. When I join A and B using C, I lose all the rows where C is NULL in either table. I know this is normal in MySQL but what I would like to get is a join that includes combinations of rows where C is NULL in both tables. I found out that the query below seems to work well.

SELECT *
FROM A JOIN B
ON (A.C IS NULL AND B.C IS NULL) OR (A.C IS NOT NULL AND B.C IS NOT NULL AND A.C = B.C)

So my question is, is this query the best I can get or is there a way to make this join better? Thanks!

Upvotes: 48

Views: 15287

Answers (1)

GOTO 0
GOTO 0

Reputation: 47951

Use the NULL-safe equal operator:

SELECT * FROM A JOIN B ON A.C <=> B.C

Upvotes: 117

Related Questions