MySQL Inner Join with null columns

Question

Assuming I have 2 tables

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

is it possible to join Table a and b even with the rule that:if there are null values, continue evaluating the remaining columns by skipping the null column.

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
. . .

What do you mean null? all fields if there's a null or specific field only? — Vijunav Vastivch
– Vijunav Vastivch, Commented Feb 3, 2017 at 7:09

Jaydip Jadhav · Accepted Answer · 2017-02-03 07:10:54Z

6

Yes like this

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)

answered Feb 3, 2017 at 7:10

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vijunav Vastivch · Accepted Answer · 2017-02-03 07:58:25Z

2

Try this:

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

answered Feb 3, 2017 at 7:58

Vijunav Vastivch

4,2111 gold badge18 silver badges32 bronze badges

2 Comments

Vijunav Vastivch Over a year ago

It will show all not null you can change is null if you want all null value between two tables.

Led Over a year ago

thanks, but I accepted JaydipJ because he posted first

Collectives™ on Stack Overflow

MySQL Inner Join with null columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related