SQL Joins Excluding Data

Question

Say I have three tables:

Fruit (Table 1)
------
Apple
Orange
Pear
Banana

Produce Store A (Table 2 - 2 columns: Fruit for sale => Price)
-------------------------
Apple => 1.00
Orange => 1.50
Pear => 2.00

Produce Store B (Table 3 - 2 columns: Fruit for sale => Price)
------------------------
Apple => 1.10
Pear => 2.50
Banana => 1.00

If I would like to write a query with Column 1: the set of fruit offered at Produce Store A UNION Produce Store B, Column 2: Price of the fruit at Produce Store A (or null if that fruit is not offered), Column 3: Price of the fruit at Produce Store B (or null if that fruit is not offered), how would I go about joining the tables?

I am facing a similar problem (with more complex tables), and no matter what I try, if the "fruit" is not at "produce store a" but is at "produce store b", it is excluded (since I am joining produce store a first). I have even written a subquery to generate a full list of fruits, then left join Produce Store A, but it is still eliminating the fruits not offered at A. Any Ideas?

Answer 1

The key is to use a left join. This will include columns from the left side table even if there is no matching row in the right side table.

For example:

select f.name, a.price, b.price
from Fruit f
left join ProduceStoreA a on a.Name = f.Name
left join ProduceStoreB b on b.Name = f.Name

If a fruit is not found in Store A, the second column will be null. If it's not found in Store B, the third column will be null. If neither sells the fruit, both column two and three will be null.