RE: Can a JOIN be used here instead of a UNION?

SSC Guru

Points: 186127

September 3, 2014 at 2:02 am

david.dartnell (9/3/2014)
Hi Everyone,
I am in the midst of writing a report which shows the 'Total Sales', 'Gross Profit', and 'Gross Profit %' by Sales Person. Traditionally I have used a UNION between two result sets to achieve this task. However I am wondering it if is possible to achieve the same outcome with a JOIN as shown below?
...

Hi David

It looks like your suggestion will work for you, however you will have to account for null values from both tables. Try changing the outer query to this

SELECT T0.*, '#' '#', T1.*

to see what I mean. A full outer join will return matching rows between the two tables, and rows from both tables where there's no match in the other. Where there's no match, the output columns will be null.

Also, I think you should either include branch in your join, or exclude it from the GROUP BY.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden