CODEWITHSUNDEEP
sqlpostgresql
MrBunni
MrBunni

Reputation: 103

SQL join two tables with null values in either table

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?

Upvotes: 9

Views: 12495

Answers (3)

potashin
potashin

Reputation: 44581

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

Upvotes: 5

Veselin Davidov
Veselin Davidov

Reputation: 7071

You can do an union. First select with left join and then union the sold results that don't have corresponding rows in bought.

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 article = 1
Union 
SELECT article,null, sold, year
FROM sold where article =1 and year not in (select year from bought)

Something like that. I haven't tested it but you can get the idea.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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

Upvotes: 15

Related Questions