How do I handle nulls in mySQL joins?-CodePudding

Consider table t1 which has these three columns among others:

nation	state	region
x1
x1	y1
x1	y1	z1

and t2:

nation	state	region	population
x1			p1
x1	y1		p2
x1	y1	z1	p3

How do I join t1 and t2 to get the population column in t1?

This only works when none of them are null:

join on t1.nation=t2.nation and t1.state=t2.state and t1.region=t2.region

CodePudding user response：

MySQL has a "null-safe equals" operator <=> which is equivalent to standard SQL IS NOT DISTINCT FROM, and which compares two NULLs as being equal.

So you may refactor your query to this:

SELECT t1.nation, t1.state, t1.region, t2.population
FROM Table1 t1
INNER JOIN Table2 t2
    ON t2.nation <=> t1.nation AND
       t2.state  <=> t1.state  AND
       t2.region <=> t1.region;