JDBC setNull() vs. Oracle "is null" produce different results

Question

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.

Answer 1

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