左外连接不返回左表中的所有行? [英] Left Outer Join doesn't return all rows from my left table?

问题描述

I am trying to get the number of page opens on a per day basis using the following query.

SELECT day.days, COUNT(*) as opens 
FROM day 
LEFT OUTER JOIN tracking ON day.days = DAY(FROM_UNIXTIME(open_date)) 
WHERE tracking.open_id = 10 
GROUP BY day.days

The output I get it is this:

days opens
1   9
9   2

The thing is, in my day table, I have a single column that contains the number 1 to 30 to represent the days in a month. I did a left outer join and I am expecting to have all days show on the days column!

But my query is doing that, why might that be?

解决方案

Nanne's answer given explains why you don't get the desired result (your WHERE clause removes rows), but not how to fix it.

The solution is to change WHERE to AND so that the condition is part of the join condition, not a filter applied after the join:

SELECT day.days, COUNT(*) as opens 
FROM day 
LEFT OUTER JOIN tracking
ON day.days = DAY(FROM_UNIXTIME(open_date)) 
AND tracking.open_id = 10 
GROUP BY day.days

Now all rows in the left table will be present in the result.

这篇关于左外连接不返回左表中的所有行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！