Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Empty IN clause parameter list in MySQL

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?

like image 786
Wiz Avatar asked Nov 03 '12 14:11

Wiz


People also ask

How do I check if a list is empty in SQL?

The len() function is used to find the number of elements in the list. So, to check if the list is empty or not using len(), we can pass the empty list to the len() function, and if we get 0, that means the list is empty.

Is Empty in MySQL query?

SELECT * FROM yourTableName WHERE yourSpecificColumnName IS NULL OR yourSpecificColumnName = ' '; The IS NULL constraint can be used whenever the column is empty and the symbol ( ' ') is used when there is empty value.

How do you return all records if parameter is NULL?

Inside the stored procedure, the parameter value is first tested for Null using the ISNULL function and then checked whether it is Blank (Empty). If the parameter has value then only matching records will be returned, while if the parameter is Null or Blank (Empty) then all records from the table will be returned.

Is empty string NULL in MySQL?

In the above syntax, if you compare empty string( ' ') to empty string( ' '), the result will always be NULL.


Video Answer


1 Answers

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.

like image 54
Barmar Avatar answered Oct 15 '22 23:10

Barmar