mysql 空连接,MySQL内部连接与空列

假设我有2张桌子

Table a

brand Fruit edible color size

farmers banana yes yellow null

fresh banana yes red 10

bounty banana null green 2

farmers apple yes red 5

organic grapes null violet 5

love strawberry yes null 5

flow lavander no null null

Table b

boxId fruit edible color size

10100 banana yes yellow 9

19299 banana yes red 10

10992 apple yes red 5

10299 grapes yes red 5

01929 lavander no violet 3

是否可以连接表a和b,即使规则如下：如果有空值,则通过跳过空列继续评估剩余的列.

select a.brand, b.boxId from a prod

inner join b box on a.fruit = b.fruit

where a.edible = b.edible and

a.color = b.color and

a.size = b.size

brand boxID

farmers 10100

fresh 19299

. . .

解决方法:

是这样的

select a.brand, b.boxId

from a prod

inner join b box on a.fruit = b.fruit

where ( a.edible = b.edible OR a.edible IS NULL OR b.edible IS NULL ) and

( a.color = b.color OR a.color IS NULL OR b.color IS NULL ) and

( a.size = b.size OR a.size IS NULL OR b.size IS NULL)

标签：mysql,inner-join,null

来源： https://codeday.me/bug/20190611/1218067.html