2

For example, () works in sqllite3:

select *
from orders
where custid = ?
and status in ()

But it fails in postgres:

ksysdb=# select code, applicable_objecttype 
from pssystem_ktagtype where applicable_objecttype in ();

ERROR:  syntax error at or near ")"
LINE 1: ...pe from pssystem_ktagtype where applicable_objecttype in ();

Using (null) makes postgres happy, and doesn't seem to bother sqllite3:

ksysdb=# select code, applicable_objecttype 
from pssystem_ktagtype where applicable_objecttype in (null);
code | applicable_objecttype
------+-----------------------
(0 rows)

But would it work on SQL Server and Oracle? What does ANSI SQL have to say? My guess is that null will never match anything (including null itself), so it the in list condition will always evaluate to false.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
JL Peyret
  • 10,917
  • 2
  • 54
  • 73
  • 1
    I think your assumption that null will never match anything is correct. In SQL, `X IN (A, B)` is equivalent to `(X = A) OR (X = B)` which means in case of null `X = NULL` which is false. – vc 74 Jun 30 '18 at 19:21
  • Yes, but will other databases choke on `in (null)`? I can test on Oracle later, but I don't have a SQL Server handy right now and I'd prefer that to cover other databases too. – JL Peyret Jun 30 '18 at 19:22
  • looking at youre last comment, there are some subtle differences between MS SQL, MySQL and Oracle SQL. The question I have is what is the overall goal, and what systems are you working with right now? as If you have an application with inline sql youll need to run and conditions \ if \ case statements, or if its within a stored procedure you might need to simply run different queries – Simon Price Jun 30 '18 at 19:25
  • what I am trying to achieve is generating a query dynamically that is syntactically correct for as many databases whether or not the list of accepted values is empty. It's not a subquery, because the contents of the list is to be decided at the application level. So, for example, you may want all `shipped, delayed` orders one time. But just `shipped` the next. What if you choose nothing? No, it shouldn't return anything, but it also should not cause a SQL execution error. Note: list members will be managed via a *prepared statement*, to avoid SQL injection issues. So `in (?,?)`. – JL Peyret Jun 30 '18 at 19:29
  • NULL is fine. It is perfectly valid SQL and will evaluate to UNKNOWN if it is the only thing in the list. But why send the query at all in that case as it wont return any rows? – Martin Smith Jun 30 '18 at 19:30
  • `IN (NULL)` should be fine in Postgres, SQL Server, Oracle, MariaDB, MySQL and SQLite. At least I couldn't produce any error with it on [SQL Fiddle](http://sqlfiddle.com/) or [db<>fiddle](https://dbfiddle.uk/). – sticky bit Jun 30 '18 at 19:33
  • @stickybit. if you post your comment as answer, I'll accept it. – JL Peyret Jun 30 '18 at 19:39

2 Answers2

1

IN (NULL) should be fine in Postgres, SQL Server, Oracle, MariaDB, MySQL and SQLite. At least I couldn't produce any error with it on SQL Fiddle or db<>fiddle.

Setup:

CREATE TABLE a
             (a varchar(1));
INSERT INTO a
            (a)
            VALUES ('a');
SELECT *
       FROM a
       WHERE a IN (NULL);
sticky bit
  • 36,626
  • 12
  • 31
  • 42
0

maybe you can do a sub query which return empty list: select * from orders where custid = ? and status in (select ...)

and i did't understand what you are trying to achieve, doing in on empty list will always return False.

SocketPlayer
  • 166
  • 6
  • no, sorry, although I am not the person who downvoted you, the point is that the app, not the server, decides what items it wants in the list. – JL Peyret Jun 30 '18 at 19:49