sql - Empty IN clause parameter list in MySQL

Question

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 1

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.