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?
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.
column IN (1, 2, 3) is equivalent to column=1 OR column=2 OR column=3, then it is natural that an empty list would be equivalent to zero OR terms. But this leads to weird rules if you try to combine it with other boolean expressions. For example, should <expr> AND column IN (1,2,3) map to <expr> AND (true)? But that seems wrong, if intuitively you would say column IN () should be false. –
Maddening IN queries don't work with empty sets!! Thank you @Sherris –
Monohydroxy This gives me 0 results as well:
SELECT id FROM User
WHERE id IN (NULL);
Tried on MYSQL 5.6
id IN (NULL) evaluates to NULL and not FALSE. That's mostly a problem for NOT IN: SELECT id FROM User WHERE id NOT IN (NULL); will return 0 results as well. –
Linker The closest approximation of this query with valid syntax is:
SELECT user FROM tbl1 WHERE id IN (SELECT id FROM tbl1 WHERE FALSE);
which unconditionally returns an empty result set. The subquery in the bracket always returns an empty set, and no value can be found in an empty set, since an empty set contains no values.
(select top 0 0), which has the advantage of not requiring a table name. –
Judgment IN list dynamically. He's not looking for a way to produce a vacuous IN clause, he wants to know how to deal with it when it happens accidentally because he's given an empty array of values. –
Sherris SELECT user FROM tbl1 WHERE id IN (FALSE) –
Compositor id isn't a boolean field, which is probably a reasonable assumption, but an assumption nevertheless. –
Here SELECT TOP N isn't MySQL. On the other hand (SELECT 0 WHERE 0) works. However, doing IN (SELECT ...) seems to create a dependent subquery in MySQL 5.7 (as shown in explain) and the resulting query is extremely slow. –
Breadnut (select top 0 NULL), which should work with any column type. –
Benthamism IN (select NULL from (select 0) x where FALSE) –
Wolsey Use an always false statement
Before creating the SQL, check for the array size.
If the size is 0, generate the where statement as 1 = 2, as in:
SELECT * FROM USERS WHERE name in ()
becomes
SELECT * FROM USERS WHERE 1 = 2
For "not in" on an empty array, generate an always true statement as in:
SELECT * FROM USERS WHERE name not in ()
becomes
SELECT * FROM USERS WHERE 1 = 1
This should work for more complex queries as:
SELECT * FROM USERS WHERE (name in () OR name = 'Alice')
becomes
SELECT * FROM USERS WHERE (1 = 2 OR name = 'Alice')
WHERE true and WHERE false in that case, or am I missing something. Both approaches are not ideal when dynamically building queries though. –
Eleusis If you use AUTO_INCREMENT for id (1,2,3, ..) and if array is empty, you can add one item [0]. So it will be
if (empty($arr)) {
$arr[] = 0;
}
SELECT user WHERE id IN (0);
And there will be no mysql parse error. This case is very usefull in subqueries - when your main query is not dependent on subquery results.
Better way - don't call the query if array is empty.
$data = null;
if (!empty($arr)) {
$data = ... call query
}
You can't leave IN operator empty, you must put into it something, NULL for example. But even, in this case, it will be not working as expected, because comparing with NULL always returns NULL (empty). One possible solution is to add a subselect.
Example:
SELECT user_id FROM users;
+-----------+
| user_id |
+-----------+
| 1000 |
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| 1005 |
| 1006 |
| 1007 |
| 1008 |
| 1009 |
| 1010 |
+-----------+
SELECT user_id FROM users WHERE user_id NOT IN ();
ERROR: 1064: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at line 1
SELECT user_id FROM users WHERE user_id NOT IN (NULL);
Empty set (0.0003 sec)
SELECT user_id FROM users WHERE user_id IN (1001, 1002, 1003)
AND user_id NOT IN (SELECT user_id FROM users WHERE user_id IN (NULL));
+---------+
| user_id |
+---------+
| 1001 |
| 1002 |
| 1003 |
+---------+
3 rows in set (0.0004 sec)
You cal a little bit modify (shorten) queries, but I think, they also will a little more slowly.
Here is another variation of always false statement for empty lists that preserves both logic and the notion of actual column in the query.
Incorrect id in () can be rewritten into:
where id <> id;
Similarly incorrect negative condition id not in () can be transformed by custom query building code into:
where id = id;
This approach is safer than id not in (NULL) as it doesn't evaluate to NULL.
But beware that this would filter out of the result the rows where id is null. This may be considered a feature in some cases.
Espesially useful with complex stacked query building logic where nested builder is not aware how the resulting subquery could be used above.
One way to handle this:
SELECT user WHERE id in (SELECT 1 WHERE 1!=1)
There could be a need to replace 1 with a value of the appropriate data type as mentioned in a comment by @JamesHoux.
An assumption made here is that a user's id is an int.
I assume that you still need to run the query when your IN is empty; for example with LEFT JOIN ... ON ... AND IN ().
As you are already dynamically building the IN in the application layer, I'd handle the empty case there.
Here's a basic example in PHP
$condition = 'FALSE' // Could feasibly want TRUE under different circumstances
if($values){
$inParams = **dynamically generated IN parameters from $values**;
$condition = 'IN (' . $inParams . ')';
}
$sql = 'SELECT * FROM `user` WHERE '.$condition;
If you don't need to run the query with an empty IN, then don't; save yourself a trip to the database!
N.B. In case you aren't already, I'd build/use a function that goes the long way round and binds in your IN parameters properly, rather than just concatting them raw into the SQL. This will give you some protection against SQL injection if it's used on raw data.
If you are using that query in an application and you pass dynamically a list of objects to the query, I should not call to the database to do a select with an impossible value, I should return an empty list without calling to the database query, directly.
Because it has no sense to do a query that you know that is empty before calling it.
© 2022 - 2024 — McMap. All rights reserved.
INclause. Sheesh! – Ziguard