left join return null even if there are no rows

Question

My tables:

Table cat has id, name

Table user has id, uname, catid

Sample data:
cat table

1 | Cate one
2 | cate two

user table

1 | sam | 1
2 | dam | 0

my query is

SELECT cat.id, cat.name 
FROM cat LEFT JOIN user 
  ON cat.id = user.catid
WHERE user.id = 2

Since there is no category with id 0 I get zero rows.
If there are no rows I want NULL or zeros as a result.

How do I do that?

Answer 1

You either need to turn your left join into a right join or swap the tables around:

SELECT cat.id, cat.name
  FROM user LEFT JOIN cat ON cat.id = user.catid
 WHERE user.id = 2

With your example data, this will give you a row containing nulls as a result.

Answer 2

Change your LEFT JOIN to a RIGHT JOIN... that should pull everything from the users table, and anything from the category table if it is available.