fedorqui fedorqui - 3 months ago 7
SQL Question

Why does "zero" results in COUNT appear or not when placing the condition in WHERE or LEFT JOIN?

I have read that placing conditions in WHERE or in JOIN does not matter. However, I am running into a result that sounds a bit fishy: the results differ depending on where I put the condition. Could anyone can explain its underlying logic to me?

I have two dummy tables topic and issues. An issue has a title and a relationship 1-n with topic, so that every issue is related to a topic. Then, the issue has a title that can be repeated across topics. Its structure is quite bad but I cannot change it :/

All of this is in SQL Fiddle.

<topic> <issues>

id | name id | topic_id | title
------------ ---------------------
1 | art 1 | 1 | final
2 | music 2 | 1 | semi final
3 | sport 3 | 2 | final
4 | 2 | draft


Now I want to select how many times a topic appears in a list of issues, getting a
0
for when there is none. Using
LEFT JOIN
makes it, with the help of How to include “zero” / “0” results in COUNT aggregate?:

SELECT
t.name, count(i.title)
FROM
topic as t
LEFT JOIN issues as i
ON t.id = i.topic_id
GROUP BY t.id;


This returns the expected result:

name | count
--------------
art | 2
music | 2
sport | 0


Now I want to know how many times a given title appears across topics. For example, how many times "final" happens for every topic? And here is where the problem appears:

SELECT
t.name, count(i.title)
FROM
topic as t
LEFT JOIN issues as i
ON t.id = i.topic_id
AND i.title = "final" -- in the LEFT JOIN!
GROUP BY t.id;


Returns:

name | count
--------------
art | 1
music | 1
sport | 0 -- yeah, sport - 0 is here


Whereas

SELECT
t.name, count(i.title)
FROM
topic as t
LEFT JOIN issues as i
ON t.id = i.topic_id
WHERE
i.title = "final" -- in the WHERE!
GROUP BY t.id;


Returns

name | count
--------------
art | 1
music | 1 -- where is sport - 0?

Answer
WHERE
    i.title = "final" 

also excludes rows where i.title is NULL. So when LEFT JOIN has no corresponding values in the i table the WHERE excludes the rows.

WHERE
    i.title = "final" or i.title is null

leaves the rows

Comments