Using a FULL JOIN in a SELECT, How can I use certain column from a Table but if it is null use a column from another Table?

Question

I have two tables:

-- Foo --                 -- Bar --
Street | City | Sales     Street | City | Purchases
  X    |  A   |  2           Y   |  B   |   1
  Y    |  B   |  3           Z   |  C   |   4

I have to return this:

Street | City | Profit
  1    |  A   |   2
  2    |  B   |   2
  3    |  C   |  -4

The only thing that came to my mind was this:

SELECT f.street, f.city, b.street, b.city, ISNULL(f.sales,0) - ISNULL(b.purchases,0) as Profit
FROM Foo f FULL JOIN Bar b
ON f.street = b.street AND f.city = b.city

I know this isn't what I need but got this result:

Street | City | Street | City | Profit
  1    |  A   |  NULL  | NULL |   2
  2    |  B   |   2    |  B   |   2
 NULL  | NULL |   3    |  C   |  -4

How can I combine the columns so when it finds in one table use that one and vice versa?

Tag your question with the database you are using. – Gordon Linoff Dec 08 '20 at 12:03 — Gordon Linoff, Dec 08 '20 at 12:03

score 0 · Answer 1 · answered Dec 08 '20 at 10:21

0

You can use coalesce() in the select clause:

select coalesce(f.street, b.street), coalesce(f.city, b.city)
    coalesce(f.sales, 0) - coalesce(b.purchases, 0) as profit
from foo f 
full join bar b on f.street = b.street and f.city = b.city

If your database supports the using clause to joins, this is simpler:

select street, city, 
    coalesce(f.sales,0) - coalesce(b.purchases,0) as profit
from foo f 
full join bar b using(street, city)

answered Dec 08 '20 at 10:21

GMB

216,147
25
84
135

i'm getting less lines in the result, shouldn't i get the same amount of lines? – Pupi Sala Dec 08 '20 at 10:31
@PupiSala: this produces the same number of lines as your original query. I only modified the `select` clause. – GMB Dec 08 '20 at 10:55
I simplified the question, but my real case have multiple lines for the same street-city. And i am using SUM and GROUP BY. And I'm getting more rows in the original query and less with the one with coalesce – Pupi Sala Dec 08 '20 at 11:14

score 0 · Answer 2 · answered Dec 08 '20 at 12:05

Another possibility is union all and aggregation:

select street, city, sum(sales), sum(purchases),
       sum(sales) - sum(purchases) as profit
from ((select street, city, sales, 0 as purchases
       from foo
      ) union all
      (select street, city, 0, sales
       from bar
      )
     ) fb
group by street, city;

This is safer than a full join because it will work if there are multiple rows in either table for a street/city combination.

Using a FULL JOIN in a SELECT, How can I use certain column from a Table but if it is null use a column from another Table?

2 Answers2