WHERE clause causing table to omit LEFT JOIN rule

Question

Hi,

I am essentially attempting to modify this stored procedure.

Modified stored procedure:

CREATE PROCEDURE sp1(d1 date, d2 date, client INT(10))
    declare d datetime;

    create TEMPORARY TABLE foo (d date NOT NULL, Amount INT(10) DEFAULT 0);

    set d = d1;

    while d <= d2 do 
        insert into foo (d) values (d);
        set d = date_add(d, interval 1 day);
    end while;

    SELECT SUM(p.Amount), foo.d
    FROM foo LEFT JOIN ItemTracker_dbo.Payment ON foo.d = p.Datetime
    WHERE p.ClientId = ClientId
    GROUP BY
        foo.d;

    DROP TEMPORARY TABLE foo;
end PROCEDURE

NOTE: the WHERE clause... p.ClientId = client

I was wracking my brain trying to figure out why it was omitting the zero's.

upon removal of WHERE p.ClientId = client the procedure began to return NULL...

Why is the WHERE clause ommiting the null rows? I am probably misunderstanding what exactly a LEFT JOIN is.

How can I go about filtering the SUM(p.Amount) results to only return the sum WHERE clientId = client?

Answer 1

It should work if you put all the conditions into the LEFT JOIN condition:

SELECT SUM(p.Amount), foo.d
FROM foo
LEFT JOIN ItemTracker_dbo.Payment p ON foo.d = p.Datetime AND p.ClientId = ClientId
GROUP BY
    foo.d;

Answer 2

The issue is that the = operator is not NULL-safe in SQL. NULL = NULL is false. You have to use the NULL-safe equivalent, which if I remeber correctly is <=> on MySQL. You can also use the COALESCE function or write a=b as (a=b OR a IS NULL or b IS NULL).

Answer 3

Move the WHERE clause into the ON clause like this:

SELECT SUM(p.Amount), foo.d
FROM foo LEFT JOIN ItemTracker_dbo.Payment ON foo.d = p.Datetime 
    and p.ClientId = ClientId
GROUP BY
    foo.d;

By putting the ClientId comparison in the WHERE clause, you were effectively turning the LEFT JOIN back into an INNER JOIN on ClientId.

Answer 4

First of all, isn't your sample code

SELECT SUM(p.Amount), foo.d 
    FROM foo LEFT JOIN ItemTracker_dbo.Payment ON foo.d = p.Datetime 
    WHERE p.ClientId = ClientId 
    GROUP BY 
        foo.d; 

missing a p for the alias for table ItemTracker_dbo.Payment ?? i.e., shouldn;t it read:

SELECT SUM(p.Amount), foo.d 
    FROM foo 
       LEFT JOIN ItemTracker_dbo.Payment p
            ON foo.d = p.Datetime 
WHERE p.ClientId = ClientId 
GROUP BY foo.d; 

Anyway, the reason you are having this problem is that where clause conditions are not applied until after the outer join is processed (where the rows from the outer side are added back in) So you need to move the where condition into the join condition:

SELECT SUM(p.Amount), foo.d 
    FROM foo 
       LEFT JOIN ItemTracker_dbo.Payment p
            ON foo.d = p.Datetime 
                And p.ClientId = ClientId 
GROUP BY foo.d;