MySQL: filtering results from a list of id's

A short while ago I posted about testing if a variable exists in a list and I spoke about the MySQL “IN” keyword.

I realised this week that not everyone knows about the MySQL “IN” keyword, so I’ve decided to explain this very handy SQL keyword.

Lets say you have a user table with the following fields; user_id, username, password. You have a list of user ids (eg 1, 3, 5) and you want to draw the usernames and passwords for these users.

You could use the following SQL code:

SELECT `username`, `password` FROM `user`
WHERE `user_id` = 1 AND WHERE `user_id` = 3 AND WHERE `user_id` = 5;

Now while this is correct and will return the required results, it is quite a bit to code (especially if you have a larger list of user ids). Here comes the “IN” keyword to save us.

SELECT `username`, `password` FROM `user` WHERE `user_id` IN (1, 3, 5);

See how easy that is. Now if you have a much larger list (or the list grows) you simply add the user ids to the comma delimited list. Less time spent, and the same result. I’m all about the shortcuts….