Full Outer Join Takes Only Left Table (but no where clause)

user_10286012 0
Viewed 46

I have come to an issue I don't seem able to solve. This looks very easy on paper, but as this is still new to me I guess I am missing something obvious..?

I have 2 tables wich contain:

  • Qty: Date, Product Code and Number
  • Rev: Date, Product Code and Revenue

The desired output is: Date, Product Code, Number and Revenue (basically, I want to merge them). The thing is, I may have products in Qty which don't exist in Rev, and products in Rev that don't exist in Qty. My guess was to use the below code:

SELECT Rev.Date
    ,ISNULL(rev.Prd,Qty.Prd) as ProductCode
    ,ISNULL(Sum(Number),0) as Number
    ,ISNULL(SUM(Revenue),0) as Revenue
FROM Rev
FULL OUTER JOIN Qty ON rev.Date=Qty.Date AND rev.Prd=Qty.Prd
GROUP BY rev.Date, rev.Prd, Qty.Prd
ORDER BY rev.Date

However, I am getting desesperate as despite all my tests, I keep missing some Product Codes from the Qty table (however I do have the ones from Rev which are not in Qty).

Most of the answers I found online are referring to some conflicts with the Where Clause, but I don't have any. Am I misunderstanding something here? Thanks!

Edit: This is my data:

TABLE Rev                               TABLE Qty       
Date        |   Prd   | Revenue         Date        |   Prd   | Number
------------|---------|-----------      ------------|---------|-------
07/09/2018  |   ProdA | 100             07/09/2018  |   ProdA | 1
07/09/2018  |   ProdB | 200             07/09/2018  |   ProdB | 1
07/09/2018  |   ProdC | 0               07/09/2018  |   ProdC | 1
07/09/2018  |   ProdD | 150             07/09/2018  |   ProdD | 3
07/09/2018  |   ProdE | 0               07/09/2018  |   ProdE | 1
07/09/2018  |   ProdF | 0               07/09/2018  |   ProdF | 2
07/09/2018  |   ProdH | 120             07/09/2018  |   ProdH | 8
07/09/2018  |   ProdI | 200             07/09/2018  |   ProdI | 3
07/09/2018  |   ProdX | 500             07/09/2018  |   PRODZ*| 1

And below my current output as well as the desired output:

OUTPUT                                     DESIRED          
Date        |   Prd   | Number |Revenue    Date        |    Prd   | Number |Revenue 
------------|---------|------------------  ------------|----------|-----------------
07/09/2018  |   ProdA | 1      |100        07/09/2018  |    ProdA | 1      |100
07/09/2018  |   ProdB | 1      |200        07/09/2018  |    ProdB | 1      |200
07/09/2018  |   ProdC | 1      |0          07/09/2018  |    ProdC | 1      |0
07/09/2018  |   ProdD | 3      |150        07/09/2018  |    ProdD | 3      |150
07/09/2018  |   ProdE | 1      |0          07/09/2018  |    ProdE | 1      |0
07/09/2018  |   ProdF | 2      |0          07/09/2018  |    ProdF | 2      |0
07/09/2018  |   ProdH | 8      |120        07/09/2018  |    ProdH | 8      |120
07/09/2018  |   ProdI | 3      |200        07/09/2018  |    ProdI | 3      |200
07/09/2018  |   ProdX | 0      |500        07/09/2018  |    ProdX | 0      |500
                                           07/09/2018  |    PRODZ*| 1      |0

The PRODZ* is the one missing in all my tests.

outer-join sql-server sql
1 Answers

Your GROUP BY is primary on the left table.

All unmatched rows will have a NULL value for rev.date and rev.prd so they are all combined into a single row in the result set.

Presumably, you intend:

SELECT COALESCE(Rev.Date, qty.Date) as date
       COALESCE(rev.Prd, Qty.Prd) as ProductCode
       COALESCE(Sum(Number), 0) as Number
       COALESCE(SUM(Revenue), 0) as Revenue
FROM Rev FULL OUTER JOIN
     Qty
     ON rev.Date = Qty.Date AND rev.Prd = Qty.Prd
GROUP BY COALESCE(Rev.Date, qty.Date), COALESCE(rev.Prd, Qty.Prd)
ORDER BY COALESCE(Rev.Date, qty.Date);

Queries with FULL JOIN tend to have a lot of NULL handling.

user_11440350
Related