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?

You first query has v.artk in the WHERE, but there are no v table... — jarlh
– jarlh, Commented May 4, 2015 at 7:54
Did you come up with formatting your query results via <kbd> or is this something that has already been done here? — Peter Lang
– Peter Lang, Commented May 4, 2015 at 7:56
@PeterLang found it as the third answer in this thread: meta.stackexchange.com/questions/73566/… — MrBunni
– MrBunni, Commented May 4, 2015 at 8:13
@MrBunni: Interesting, thanks! Looks cumbersome to align the columns though... — Peter Lang
– Peter Lang, Commented May 4, 2015 at 8:48

Gordon Linoff · Accepted Answer · 2015-05-04 07:56:41Z

15

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

answered May 4, 2015 at 7:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

souslov · Accepted Answer · 2015-05-04 08:02:49Z

5

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

edited May 4, 2015 at 8:02

answered May 4, 2015 at 7:54

souslov

44.7k11 gold badges92 silver badges113 bronze badges

Comments

Veselin Davidov · Accepted Answer · 2015-05-04 08:00:04Z

0

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.

answered May 4, 2015 at 8:00

Veselin Davidov

7,0911 gold badge17 silver badges23 bronze badges

Collectives™ on Stack Overflow

SQL join two tables with null values in either table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related