4

I am working on a PreparedStatement query in JDBC against an Oracle 11g database and find that the results of passing a null parameter differs from defining "is null" in the query itself.

For instance, this query:

String sql = "SELECT col1 FROM tbl WHERE col2 = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setNull(1, java.sql.Types.INTEGER);
ps.execute();

differs from this query:

String sql = "SELECT col1 FROM tbl WHERE col2 is null";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setNull(1, java.sql.Types.INTEGER);
ps.execute();

I am curious why this is the case, and how I can avoid defining two separate SQL statements to cover both "col2 = value" and "col2 is null" cases.

Peter Porter
  • 387
  • 1
  • 4
  • 11
  • 1
    I cannot seem to find a cleaner way to resolve this than the answer detailed [here](http://stackoverflow.com/questions/4215135/how-to-deal-with-maybe-null-values-in-a-preparedstatement?rq=1), which would be "WHERE col2 = ? OR (col2 IS NULL AND ? IS NULL)". – Peter Porter Jun 12 '12 at 05:13

3 Answers3

6

This has nothing to do with Java, really, this is how NULL works in Oracle.

NULL is always false when compared to anything (even to NULL), you have to use IS NULL.

This will also return no rows:

SELECT col1 FROM tbl WHERE col2 = NULL

or even

SELECT col1 FROM tbl WHERE NULL = NULL
Thilo
  • 257,207
  • 101
  • 511
  • 656
  • Is there a way to express this conditionally in a single SQL statement? – Peter Porter Jun 12 '12 at 05:03
  • Do you mean, how to make it work in Oracle and another database w/o changing the SQL statement, or how to write one statement to look for either null or non-null values? – BillRobertson42 Jun 12 '12 at 05:10
  • 1
    How to write one statement that looks for null or non-null values based on parameter input. Adding "WHERE col2 = ? OR (col2 IS NULL AND ? IS NULL)" seems like the most sensible option, requiring me to supply the variable twice is preferable to defining and maintaining two separate queries. Are there any preferable options? – Peter Porter Jun 12 '12 at 05:17
  • @peterporter: That is probably the "best" single SQL. But you should split the two cases in Java and issue separate SQL for better performance (because the access path will be different depending on you want IS NULL or not). – Thilo Jun 12 '12 at 05:34
  • 1
    @Thilo I appreciate the advice. However, in this case I believe it much more likely that a future developer will update the two queries improperly rather than the performance hit being noticeable. There are certainly other cases where the performance vs. maintenance tradeoff is desirable, though. – Peter Porter Jun 12 '12 at 05:42
3

I believe that ps.setNull(1, java.sql.Types.INTEGER) was meant for inserting NULL data values into the database.

You should use IS NULL if you are searching for NULL data values in an sql query.

After all, doing col2 = NULL won't work either because you cannot compare for NULL values using =

Lai Xin Chu
  • 2,462
  • 15
  • 29
  • What if I want to search for NULL in one case or a non-null value in another case, but I want to do so based on the input parameter? – Peter Porter Jun 12 '12 at 05:06
  • use an if-else statement. if (value == null) { // use this SQL query } else { // use another SQL query } – Lai Xin Chu Jun 12 '12 at 05:10
  • I was trying to avoid defining the query multiple times as it is a much more complex query and redefining it would add future maintenance headache. Adding logic to the query along the lines of "WHERE col2 = ? OR (col2 IS NULL AND ? IS NULL)" should do the trick. – Peter Porter Jun 12 '12 at 05:16
0

If you are definitely using an Oracle database then you can use this statement, it will also accept NULL as equal to NULL:

select col1 from tbl where decode(col2, ?, 1, 0)=1

Is not VERY readable but better than the expression above. Also, you avoid giving the same value twice to the prepared statement. Works for me on Oracle 11 or 12.

Roland
  • 21
  • 4