Left join returns null even if there are no rows

Question

My tables:

The cat table has id, name

The user table has id, uname, catid

Sample data:
cat table

  1 |  Cate one
 2 |  cate two

user table

  1 |  sam |  1
 2 |  dam |  0

my request

 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 null strings.
If there are no lines, I want NULL or zeros as a result.

How to do it?

Answer 1

You need to either include the left join in the right join, or swap 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, you end up with a string containing zeros.

Answer 2

Change the LEFT JOIN to RIGHT JOIN ..., which should pull everything from the user table and anything from the category table, if available.