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)

Answer 2

试试这个：

select a.brand,b.boxId from
(select a.brand,a.Fruit,a.edible,a.color,a.size from prod) as a
inner join
(select * from box) as b
 on a.fruit = b.fruit 
where a.color is not null and a.edible is not null and a.size is not null
and b.color is not null and b.edible is not null and b.size is not null