Search code examples
sqlsql-serverouter-join

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

I have 2 tables:

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

The desired output is: Date, Product Code, Number and Revenue. (I want to merge the tables.) I may have products in Qty which don't exist in Rev, and products in Rev that don't exist in Qty.

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 keep missing some Product Codes from the Qty table. I do have the ones from Rev which are not in Qty.

Answers I found online refer to conflicts with the Where clause, but I don't have any. What am I misunderstanding?

Input:

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

Current and 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

PRODZ* is missing.

Solution

  • 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.