left join return null even if there are no rows

Question

My Table

Table cat is having id,name

Table user is having id,uname,catid

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 are no category with id 0 i get zero rows.

What i want is even if there are no rows i want null or zeros as a result row.

How to do that?

This is not very important but this would reduce my code a lot.

Thank you.

Answer 1

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.

Answer 2

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.