SQL Left Join losing rows after filtering
First of all i don't think it is a good idea to have number as an alias. Use a character or a word instead.
I would put the criteria in the join.
SELECT T1.Label, COUNT(T2.values)
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.fk = T1.pk
AND T2.Date BETWEEN '20100101' AND '20101231'
GROUP BY T1.Label
Other comments:
- I would use ANSI(yyyyMMdd) format for the dates, so there is no missunderstanding.
- I would be aware of the BETWEEN as it is not clear what you want to do if the date is '20101231 01:00' Would you like to include that date or not? >= and <= or >= and < is clearer.
You are filtering on the second table in the where
. The values could be NULL
and NULL
fails the comparisons.
Move the where
condition to the on
clause:
SELECT 1.Label, COUNT(2.values)
FROM Table1 1 LEFT JOIN
Table2 2
ON 1.fk = 1.pk AND
2.Date BETWEEN 1/1/2010 AND 12/31/2010
GROUP BY 1.Label
Note:
The date formats retain the dates from the question. However, I don't advocate using BETWEEN
for dates and the conditions should use standard date formats:
SELECT 1.Label, COUNT(2.values)
FROM Table1 1 LEFT JOIN
Table2 2
ON 1.fk = 1.pk AND
2.Date >= '2010-01-01' AND
2.Date < '2011-01-01'
GROUP BY 1.Label;
Some databases support the SQL Standard keyword DATE
to identify date constants.
simply move the condition in WHERE
clause to the ON
clause.
LEFT JOIN Table2 2 ON 1.fk = 1.pk AND 2.Date BETWEEN '1/1/2010' AND '12/31/2010'