带有空列的 MySQL 内部连接

Question

假设我有 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
. . .

Answer 1

是这样的

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)