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?
Best Answer
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.