左外部联接不会返回我的左表中的所有行吗? [英] 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

我得到的输出是这样:

days opens
1   9
9   2

问题是，在我的日表中，我有一列包含数字1到30来表示一个月中的天数.我做了一个左外部连接，我希望在天列中显示整天！

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.

解决方案是将WHERE更改为AND，以便该条件成为联接条件的一部分，而不是联接之后应用的过滤器:

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屋！