Two SQL Joins, Two Different Results

Hows is it possible for these two queries to be different. I mean the first query didn't include all the rows from my left table so I put the conditions withi开发者_JAVA百科n the join part.

Query 1

SELECT COUNT(*) as opens, hours.hour as point 
FROM hours 
LEFT OUTER JOIN tracking ON hours.hour = HOUR(FROM_UNIXTIME(tracking.open_date)) 
WHERE tracking.campaign_id = 83 
AND tracking.open_date < 1299538799 
AND tracking.open_date > 1299452401 
GROUP BY hours.hour

Query 2

SELECT COUNT(*) as opens, hours.hour as point 
FROM hours 
LEFT JOIN tracking ON hours.hour = HOUR(FROM_UNIXTIME(tracking.open_date)) 
AND tracking.campaign_id = 83 
AND tracking.open_date < 1299538799 
AND tracking.open_date > 1299452401 
GROUP BY hours.hour

The difference is that the first query gives me 18 rows where there are no rows between point 17 to 22. But when I run the second query, it shows the fully 24 rows but for rows between 17 and 22 it has a value of 1! I would of expected it to be 0 or NULL? If it really is 1 should it not have appeared in the first query?

How has this happened?

the first JOIN is really an INNER JOIN, the outer joined table should not appear in the WHERE clause like you have in the top query, instead of COUNT(*), pick a column from the outer joined table

You're using COUNT(*), which will count every row in your result set (as it's written), since even without data in tracking, you do have data in hours.

Try changing COUNT(*) to COUNT(tracking.open_date) (or any non-nullable column within tracking; it doesn't matter which one).

COUNT(*) counts the number of rows resulted in the query.

You can use count(tracking.open_date), basically any column from tracking table (right table)

The problem is that the first query will do an outer join, with some rows containing NULL in all tables from the tracking table. Then it will apply a filter on those tracking columns, and since they are null, the corresponding row from the result set will be filtered out.

The second query will do a proper outer join on all columns.