Empty IN clause parameter list in MySQL

mysql sql in-clause
Wiz picture Wiz · Nov 3, 2012 · Viewed 56.7k times · Source

What happens when you do a SQL query where the IN clause is empty?

For example:

SELECT user WHERE id IN ();

Will MySQL handle this as expected (that is, always false), and if not, how can my application handle this case when building the IN clause dynamically?

Answer

Barmar picture Barmar · Nov 3, 2012

If I have an application where I'm building the IN list dynamically, and it might end up empty, what I sometimes do is initialize the list with an impossible value and add to that. E.g. if it's a list of usernames, I'll start with an empty string, since that's not a possible username. If it's an auto_increment ID, I'll use -1 because the actual values are always positive.

If this isn't feasible because there are no impossible values, you have to use a conditional to decide whether to include AND column IN ($values) expression in the WHERE clause.

Related questions

MySQL variable format for a "NOT IN" list of values

Going crazy trying to set a variable in a query of type: SET @idcamposexcluidos='817,803,495'; so i can then use it on a WHERE id_campo not in (@idcamposexcluidos) I've tried defining the variable in different formats with no …

Read More
MySQL number of items within "in clause"

I have three tables to define users: USER: user_id (int), username (varchar) USER_METADATA_FIELD: user_metadata_field_id (int), field_name (varchar) USER_METADATA: user_metadata_field_id (int), user_id (int), field_value (varchar) I'd like to …

Read More
Select Multiple Ids from a table

I want to select some id's based on url string but with my code it displays only the first. If i write manual the id's it works great. I have a url like this http://www.mydomain.com/myfile.php?…

Read More