MySQL LEFT JOIN filtering rows where NULL

Question

I am attempting to return whether a user has favourited an item or not. I have the MySQL statement:

SELECT *,`user_choice`.fav FROM `items`
LEFT JOIN `user_choice` ON `user_choice`.item_id = `items`.item_id

This returns a full set of results, with user_choice.fav equal to 0, 1 or NULL

However, I would like to only show the user_choice.fav (whatever that value may be) for a given user, so I have added a WHERE statement for that user like so:

SELECT *,`user_choice`.fav FROM `items`
LEFT JOIN `user_choice` ON `user_choice`.item_id = `items`.item_id
WHERE `user_choice`.user_id=xx

When I add this WHERE statement, it filters out any results from the first statement where user_choice.fav is NULL (ie not 1 or 0). How can I get it to return values even when they are NULL

example data:

items

item_id | item_name

1,itemname

2,item2name

3,item3name

user_choice

user_id | item_id | fav

1,1,1

1,3,0

Your question would be much clearer if you added sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Jun 21, 2014 at 14:34

Luke Peterson · Accepted Answer · 2014-06-21 14:31:54Z

4

0, 1 are not considered NULL values, so your WHERE statement is excluding them. You need to add another OR into your WHERE clause as follows:

SELECT *,`user_choice`.fav FROM `items`
LEFT JOIN `user_choice` ON `user_choice`.item_id = `items`.item_id
WHERE `user_choice`.user_id=xx OR `user_choice`.user_id IS NULL

answered Jun 21, 2014 at 14:31

Luke Peterson

8,8988 gold badges49 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3608238 Over a year ago

will this try and match with the user first, then default to null?

Luke Peterson Over a year ago

No, in your question you've asked how to return values even if they are NULL.

user3608238 Over a year ago

I am trying to return 0 or 1 where the user_id in user_choice is equal xx, but if the corresponding item and user do not exist in user_choice I would like to return NULL, where presently it is removing the row

Luke Peterson Over a year ago

Try a FULL OUTER JOIN, otherwise you'll need to post some data along with the question.

Collectives™ on Stack Overflow

MySQL LEFT JOIN filtering rows where NULL

item_id | item_name

user_id | item_id | fav

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

item_id | item_name

user_id | item_id | fav

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related