SQL join two tables with null values in either table

Question

I'm having some trouble joining the contents of two tables. Here's the current situation:

Bought
Article   Bought Year     
1             12400    2011     
1             28000    2012     
1             46351    2015     

Sold
Article   Sold      Year     
1             6400      2011     
1             12000    2013     
1             60900    2014     

Desired Result
Article   Bought Sold    Year     
1             12400    6400    2011     
1             28000    NULL   2012     
1             NULL     12000  2013     
1             NULL     60900  2014     
1             46351    NULL   2015     

I've tried the following to achieve the desired result:

SELECT b.article, b.bought, s.sold, b.year
FROM Bought AS b
  LEFT JOIN Sold as s ON s.article = b.article AND s.year = b.year
WHERE b.article = '1'
ORDER BY b.year    

This only returns the result for 2011 (Where both values are present).

Another try using a third table holding all articles returned the same bad result and it has two year columns which is not ideal:

SELECT art.article, b.bought, s.sold, b.year, s.year
FROM articles AS art
  LEFT OUTER JOIN bought AS b ON art.article = b.article
  LEFT OUTER JOIN Sold AS s ON art.article = s.article
     AND (b.year = s.year OR b.year IS NULL OR s.year IS NULL)
 WHERE art.article = '1'    

I've tried using different kinds of joins with the last SQL statement but none of them seem to work. How can I achieve the desired result?

Answer 1

It sounds like you want a full outer join:

SELECT coalesce(b.article, s.article) as article, b.bought, s.sold,
       coalesce(b.year, s.year) as year
FROM Bought b FULL OUTER JOIN
     Sold s
     ON s.article = b.article AND s.year = b.year
WHERE (b.article = '1' OR s.article = '1')
ORDER BY year 

Answer 2

You should use full outer join :

select coalesce(b.article, s.article)
     , b.bought
     , s.sold
     , coalesce (b.year, s.year) as year
from Bought b
full join Sold s on s.article = b.article
                 and s.year = b.year
where coalesce(b.article, s.article) = '1'
order by year