Geek asks and answers

Two SQL joins, two different results

Perhaps these two queries may be different. I mean, the first query did not include all the rows from my left table, so I put the conditions in the join part.

Request 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

Request 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 lines, where there are no lines between points from 17 to 22. But when I run the second request, it shows 24 lines completely, but for lines between 17 and 22 it has a value of 1 ! I expected it to be 0 or NULL? If it is really 1, if it did not appear in the first request?

How did this happen?

+3
source share
4 answers

COUNT(*), ( ), tracking hours.

COUNT(*) COUNT(tracking.open_date) ( tracking, , ).

+3

JOIN INNER JOIN, WHERE, , COUNT (*),

+5

COUNT(*) , .

count(tracking.open_date), ( )

+1

The problem is that the first query will perform an outer join, and some rows containing NULL in all tables from the tracking table. He will then apply a filter to these tracking columns, and since they are zero, the corresponding row from the result set will be filtered.

The second query will perform the correct outer join for all columns.

0
source

More articles:


All Articles