IN () "error"

should it really throw an error?

[LifeinAsia]

Let's say that you are selecting from a table based on a list of values. For whatever reason, the list happens to be empty. So you end up with a statement something like the following:

SELECT *
FROM Foo
WHERE FooID IN ()

In MS SQL you get an error. (Just curious- do other DBs throw errors as well?)

But is it really an error? To me, if I pass in an empty list, then I would expect to get back no matching records, not an error message.

Yes, I can do (and often do) checking to see if the list is empty before passing it to the SQL statement. But it seems to me that it would be more efficient if SQL just returned 0 records instead of an error message.

Other people's thoughts/comments?

[syber]

It is a syntax error. The IN predicate is just shorthand for = OR. In your case it is the same thing as saying WHERE FooID = . You could probably achieve what you want by saying:

SELECT *
FROM Foo
WHERE FooID IN (null)