How to write a null safe compare "<=>" in pure SQL?

Question

In Mysql there is a compare operator that is a null safe: <=>. I use this in my Java program when creating prepared statements like this:

String routerAddress = getSomeValue();
String sql = "SELECT * FROM ROUTERS WHERE ROUTER_ADDRESS <=> ? ";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, routerAddress);

Now I would like to switch to the H2 database. How do I write the <=> operator in pure SQL (using for example IS NULL and IS NOT NULL)? I would like use the stmt.setString operation only once. It is okay to write the column name several times.

Related question is Get null == null in SQL. But that answer requires the search value to be written 2 times (that is: 2 question marks in my PreparedStatement)!?

Reference: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to

Trepidation

Not sure that I completely understand what you want to do. Do you want the statement to be able to return rows when you match ROUTER_ADDRESS on the non-null parameter you pass in, and to return rows where ROUTER_ADDRESS is null when you pass in null? – Insolation

Yes, thats exactly it! My getSomeValue() can return both null and non-null values. When it returns null I want all the rows where ROUTER_ADDRESS is null. – Trepidation

In pure (=standard) SQL that would be a is distinct from b – Cainozoic

Answer 1

Related question is Get null == null in SQL. But that answer requires the search value to be written 2 times (that is: 2 question marks in my PreparedStatement)!?

The second-ranked and subsequent answers give a method to do this without binding the search value twice:

SELECT * FROM ROUTERS 
WHERE coalesce(ROUTER_ADDRESS, '') = coalesce( ?, '');

Note that this requires a dummy value that can never be valid column value (that's "out of band"); I'm using the empty string. If you don't have any such value, you'll have to put up with binding the value twice:

SELECT * FROM ROUTERS 
WHERE ROUTER_ADDRESS = ? or (ROUTER_ADDRESS is null and ? is null);

Myrtamyrtaceous

Answer 2

The standard NULL-safe equality operators in SQL are IS DISTINCT FROM and IS NOT DISTINCT FROM.

Entoblast

H2 now also supports this syntax. And it supports the (shorter but non-standard) syntax x IS y and x IS NOT y. – Terraterrace

Answer 3

In SQL, NULL is not equal to itself. So you can either:

1 - Replace it with a dummy value and compare those, as in:

SELECT * FROM ROUTERS WHERE ISNULL(ROUTER_ADDRESS,'xxx') <=> ISNULL(?,'xxx')

or

2 - Replace it with a more elaborate logical test, as in:

SELECT *
FROM ROUTERS 
WHERE (
       (ROUTER_ADDRESS IS NULL AND ? IS NOT NULL)
       OR
       (ROUTER_ADDRESS IS NOT NULL AND ? IS NULL)
       OR
       (ROUTER_ADDRESS IS NOT NULL AND ? IS NOT NULL AND ROUTER_ADDRESS <> ?
      )

Rilke

Answer 4

If you want ROUTERS where ROUTER_ADDRESS is null when ? is null then possibly this could work:

SELECT * 
FROM ROUTERS 
WHERE ROUTER_ADDRESS = (case when ROUTER_ADDRESS is null and ? is null then null 
                             else ? end)

Conni

Answer 5

The correct answer for rewriting <=> in pure SQL (because you aren't using MySQL) is

"IS NOT DISTINCT FROM"

assuming that your database engine supports it. (MySQL does NOT, but many others do)

Failing that, many other database systems have their own proprietary tricks.

If you have neither, you need to coalesce to invalid data inside of the where clause.

COALESCE (a, "__NULL__") 

should work for char or text fields. (and i mean that exact string, which should never show in real data). non text fields are trickier, because sql likes to do implicit conversions, which make it very difficult to coalesce to out of band data. if a field contains only past dates, you can coalesce nulls to the far future. if a field contains only dates after a certain one, you can coalesce to the far past. For unsigned numbers, you can coalesce them to -1 (the comparison will still work, even though the field can't contain negative 1).

If that's not an option, you have to make really stupidly long checks.

If you don't actually need to consider null differently from empty, you can coalesce to that instead, but that only works for text/char.

Cognizant