How to join MySQL tables using a nullable column?

mysql sql join nullable
Per picture Per · Aug 6, 2013 · Viewed 9.2k times · Source

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!

Answer

GOTO 0 picture GOTO 0 · Aug 6, 2013

Use the NULL-safe equal operator:

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

Related questions

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More
How to do a FULL OUTER JOIN in MySQL?

I want to do a Full Outer Join in MySQL. Is this possible? Is a Full Outer Join supported by MySQL?

Read More
INNER JOIN ON vs WHERE clause

For simplicity, assume all relevant fields are NOT NULL. You can do: SELECT table1.this, table2.that, table2.somethingelse FROM table1, table2 WHERE table1.foreignkey = table2.primarykey AND (some other conditions) Or else: SELECT table1.this, table2.that, table2.somethingelse …

Read More