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?
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2).
The LEFT JOIN returns all rows from the left table and the matching rows from the right table.
A FULL OUTER JOIN returns one distinct row from each table—unlike the CROSS JOIN which has multiple.
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.
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!
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With