25

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?

E_net4
  • 27,810
  • 13
  • 101
  • 139
Abs
  • 56,052
  • 101
  • 275
  • 409
  • Does this answer your question? [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) – philipxy Feb 18 '20 at 23:03

3 Answers3

73

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.

Community
  • 1
  • 1
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 2
    alternatively, the where clause could have been changed to `WHERE (tracking.open_id IS NULL or tracking.open_id = 10)` although i much prefer putting the predicate in the join clause like mark did, as I feel it makes most sense there. – goat Jan 16 '11 at 20:04
  • 3
    @chris: That works when the join fails, but won't work in the case where the join succeeds but the joining row has `tracking.open_id` equal to something other than 10. In this case the row will still be removed. – Mark Byers Jan 16 '11 at 20:06
16

You specify that the connected tracking.open_id must be 10. For the other rows it will be NULL, so they'll not show up!

Nanne
  • 64,065
  • 16
  • 119
  • 163
  • @Nanne I think you are mistakable +1 – Viktor Apoyan Apr 12 '12 at 08:18
  • **IMPORTANT** In case people are like me and don't scroll past the accepted answer, the response below this has a much better explanation and shows how to get OP's desired result _as well as_ how to fix the problem – Michael Ziluck Sep 16 '19 at 21:33
3

The condition is in the WHERE clause. After joining the tables the WHERE conditions are evaluated to filter out everything matching the criteria.Thus anything not matching tracking.open_id = 10 gets discarded.

If you want to apply this condition while joining the two tables, a better way is to use it with the ON clause (i.e. joining condition) than the entire dataset condition.

ioneyed
  • 1,052
  • 7
  • 12
Prerna Bharti
  • 41
  • 1