Recently, while writing some Spring/JPA code, I stumbled on an issue I tend to face with from time to time: I passed an empty collection to a repository method that generates an IN expression for the WHERE clause causing an SQL query syntax exception (WHERE column IN ()). To work around this issue I just return an empty collection in a DAO object, not letting the syntactially illegal SQL code to execute (if it's justified for a particular case). According to this reference, empty () are illegal as the IN right operand syntax is as follows:
<in predicate value> ::= <table subquery> | <left paren> <in value list> <right paren>
<in value list> ::= <value expression> { <comma> <value expression> } ...
requiring the value list to have at least one value.
I'm just wondering: would SQL database engines just return empty result sets and would there be backwards compatibility broken if the SQL grammar would allow empty value list for the IN operation?
3 Answers 3
It somewhat unlikely but possible that someone has built code that looks for this specific error and handles it in some special way that is different from what it would do if there were no results. Maybe they might return an error to the user. Such an approach is bad-practice IMO but I've seen (much) worse.
I think that it's a mistake that this syntax fails given that a nested select that returns no results inside an in clause doesn't cause an error. That is, it's a parsing issue, not a semantic one.
I had a similar issue where I'm using utility functions to generate the right-hand-side of column IN (...) and column NOT IN (...) for any number of values including 0.
IN () can be achieved with IN (null) as this is always false, even for null on the left hand side.
NOT IN () can be achieved with NOT IN (SELECT null WHERE 1=0) which is always true, even for null on the left hand side.
This is a strange question.
The reason why WHERE field_name IN () is disallowed (if indeed it is - I've never written such code) is because it more suggests an error in coding than a legitimate intention.
The same as if you wrote WHERE field_name = ;, with the semicolon terminating the statement and there being no field or constant specified on the right hand side of the comparison (which is a syntax error in any language I know).
If there was a desire to make the condition tautologically false and thus return an empty table, you could just write WHERE 1=0.
Obviously, in terms of generated SQL code, it may simplify things slightly to be able to specify zero or more elements in the IN list, but it also wouldn't be so hard to simply generate different code in the zero-length case. For example, to substitute WHERE field_name IN (NULL) in cases where there are no (non-null) values, which I believe is valid syntax.
The behaviour of the IN operator is such that comparison of any value and NULL is always treated as FALSE (even where both values being compared are NULL), so this achieves the apparent purpose.
IN ()predicate, and what would such a syntax grammar change affect. Not allowing an empty value list looks somewhat strange to me.IN(val,...)syntax anymore). It's now also interesting why was SQL designed this way.