Joins That Includes Non-matching Record

user_10596255 0
Viewed 35

I have a tag list. I want to list all tags and the total count each tag is used.

I can get the result using a LEFT JOIN. My problem is that my statement does not include tags that are not used. If possible, I also wanted to show all tags (used and not used in one SQL statement).

SQL Statement:

SELECT tag.name, COUNT(tag.id)
FROM tags
LEFT JOIN posts ON posts.tagid = tags.id
WHERE posts.published = 'yes'
GROUP BY posts.tagid
sql
1 Answers

Move the WHERE condition to the ON clause:

SELECT t.name, COUNT(p.tagid)
FROM tags t LEFT JOIN
     posts p
     ON p.tagid = t.id AND p.published = 'yes'
GROUP BY t.name, t.tagid;

The general rule is: For a LEFT JOIN filters on the first table go in the WHERE clause. Filters on the second table go in the ON clause. Otherwise, the WHERE clause turns the outer join into an inner join.

Notes:

  • The COUNT() now counts matches from the second table, so it can return 0 values.
  • The unaggregated SELECT column in consistent with the GROUP BY.
  • I added table aliases so the query is easier to write and to read.
user_11440350
Related