SQL statement ignores my date condition when using outer join without a WHERE clause

Question

I am using the outer join to blank rows when there is no value but why is it ignoring the line and getting ALL dates

AND t.TradeDate BETWEEN @firstOfMonth AND @maxTradeDate

If I change the AND to WHERE it works but I lose the zeroed out rows I need

--DAILY
SELECT
    a.[Name] AS RepName, 
    CONVERT(VARCHAR, a.TradeDate, 107) AS TimePeriod, 
    ISNULL(SUM(CASE t.CancelCode
                   WHEN '1' THEN t.Quantity * -1
                   ELSE t.Quantity
               END), 0) AS Quantity, 
    ISNULL(SUM(CASE t.CancelCode
                  WHEN '1' THEN t.Principal * -1
                  ELSE t.Principal
               END), 0) AS Principal, 
    ISNULL(SUM(CASE t.CancelCode
                  WHEN '1' THEN t.TradeConcession * -1
                  ELSE t.TradeConcession
               END), 0) AS Comm, 
    1 AS TheOrder
FROM 
    dayreps a
LEFT JOIN 
    Trades t ON a.TradeDate = t.TradeDate 
             AND a.RepID = t.RepID
             AND t.TradeDate BETWEEN @firstOfMonth AND @maxTradeDate
GROUP BY 
    a.[Name], a.TradeDate

Not sure what you mean here. SQL Server doesn't "ignore" clauses. As there's no `OR` here then it's doing exactly as it says. Can you provide sample data that replicates this behaviour? — Thom A, Feb 27 '19 at 19:13
Try swhitching ISNULL into SUM function, in order to have a ZERO to sum up when JOIN gets a "blank" row. Otherwise summing some NULL values will give your current situation — LittleSweetSeas, Feb 27 '19 at 19:15
Add `t.TradeDate` to your selected column list and I think the results may be clearer. — Sean, Feb 27 '19 at 19:32
A [mcve] includes a clear specification. But you don't describe or exemplify your desired behaviour so we don't know what it is. PS "blank rows when there is no value" & "ignoring the line" are not clear. Use enough words, sentences & references to parts of examples to clearly say what you mean. — philipxy, Feb 27 '19 at 23:41
Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Always know what inner join you want as part of a left join. A where that requires a right table column to be not null after a left join on removes any rows extended by nulls, ie leaves only inner join on rows, ie "turns outer join into inner join". — philipxy, Feb 27 '19 at 23:45

score 1 · Accepted Answer · answered Feb 27 '19 at 19:38

By including the condition in the OUTER JOIN's ON clause, you are saying that a failure to match this condition cannot exclude a row from the left table (dayreps). Rather such rows are NULL'd out, just like rows where the other join condition fails.

So in principle moving the condition to the WHERE clause is correct, but you also have to be careful about what the condition says. If you put

t.TradeDate BETWEEN @firstOfMonth AND @maxTradeDate

in the WHERE clause, this will evaluate to false if the date is out of range, or if it is NULL - which t.TransDate will be for any row that was NULL'd out by the outer join.

Luckily, you can instead use

a.TradeDate BETWEEN @firstOfMonth AND @maxTradeDate

and, since this comes from your left table, putting this in the WHERE clause will not thwart the outer join.

It was right to switch from t.TradeDate (had nulls) to a.TradeDate (all dates) and then go back to the WHERE clause. So I thing going with the CORSS JOIN table a which contained all the dates helped. PRob didnt include enough of the query to have theis make much sense :( — punkouter, Feb 27 '19 at 19:51

Daniel Toledo · Answer 2 · 2019-02-27T19:34:12.450

I believe is by LEFT JOIN and becouse LEFT JOIN get all record from first table independent if there is match and in this case the first table is dayreps.

try with inner join

 SELECT
     a.[Name] AS RepName, 
            CONVERT(VARCHAR, a.TradeDate, 107) AS TimePeriod, 
            ISNULL(SUM(CASE t.CancelCode
                           WHEN '1'
                           THEN t.Quantity * -1
                           ELSE t.Quantity
                       END), 0) AS Quantity, 
            ISNULL(SUM(CASE t.CancelCode
                           WHEN '1'
                           THEN t.Principal * -1
                           ELSE t.Principal
                       END), 0) AS Principal, 
            ISNULL(SUM(CASE t.CancelCode
                           WHEN '1'
                           THEN t.TradeConcession * -1
                           ELSE t.TradeConcession
                       END), 0) AS Comm, 
            1 AS TheOrder
     FROM dayreps a
          INNER JOIN Trades t ON a.TradeDate = t.TradeDate and a.RepID = t.RepID
      AND t.TradeDate BETWEEN @firstOfMonth AND @maxTradeDate
      GROUP BY a.[Name], 
               a.TradeDate

Check this What is the difference between "INNER JOIN" and "OUTER JOIN"?

PD: Sorry for my English, I work in this =)

It fixes the date but then it removes the empty rows with 0 value. I need every date to contains the 13 reps exactly — punkouter, Feb 27 '19 at 19:34

SQL statement ignores my date condition when using outer join without a WHERE clause

2 Answers2