50

The statement is

SELECT * FROM tableA WHERE x = ?

and the parameter is inserted via java.sql.PreparedStatement 'stmt'

stmt.setString(1, y); // y may be null

If y is null, the statement returns no rows in every case because x = null is always false (should be x IS NULL). One solution would be

SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL)

But then i have to set the same parameter twice. Is there a better solution?

Thanks!

aioobe
  • 413,195
  • 112
  • 811
  • 826
Zeemee
  • 10,486
  • 14
  • 51
  • 81

5 Answers5

42

I've always done it the way you show in your question. Setting the same parameter twice is not such a huge hardship, is it?

SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL);
Paul Tomblin
  • 179,021
  • 58
  • 319
  • 408
  • Thanks Paul for your note. So you vote for 'this is best practice'? – Zeemee Nov 18 '10 at 13:28
  • I've never found a better way. – Paul Tomblin Nov 18 '10 at 13:32
  • 5
    Having two separate SQL might be preferable if performance is important, though, because the access path will be different depending on whether you want IS NULL or not. – Thilo Jun 12 '12 at 05:36
  • Another example where simplicity comes closest to elegance – dammkewl Feb 10 '17 at 13:50
  • 3
    Sure, not a huge hardship, especially when you have `x = ? AND y = ? AND z = ?` and have to rewrite all of that because of the marvelous idea of SQL to treat NULL like nothing else does. –  Sep 22 '20 at 20:07
10

There is a quite unknown ANSI-SQL operator IS DISTINCT FROM that handles NULL values. It can be used like that:

SELECT * FROM tableA WHERE x NOT IS DISTINCT FROM ?

So only one parameter has to be set. Unfortunately, this is not supported by MS SQL Server (2008).

Another solution could be, if there is a value that is and will be never used ('XXX'):

SELECT * FROM tableA WHERE COALESCE(x, 'XXX') = COALESCE(?, 'XXX')
Zeemee
  • 10,486
  • 14
  • 51
  • 81
  • Tried it with MySQL+PHP PDO and it also returned `false` when trying to prepare the statement :( – Pere Mar 05 '15 at 23:05
  • 1
    [IS DISTINCT FROM](https://www.postgresql.org/docs/current/static/functions-comparison.html) works well with PostgreSQL – Alexander Farber Sep 27 '16 at 11:59
  • In old versions of Oracle, NVL may be used for the same thing, but COALESCE is nicer if available (see https://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce). – Istvan Devai Jun 23 '17 at 21:05
  • Looks like is distinct from pretty much only works on postgresql. On mysql you can use the operator x<=>? for not equals and not x<=>? for equals. – Sarel Botha May 23 '18 at 20:21
6

would just use 2 different statements:

Statement 1:

SELECT * FROM tableA WHERE x is NULL

Statement 2:

SELECT * FROM tableA WHERE x = ?

You can check your variable and build the proper statement depending on the condition. I think this makes the code much clearer and easier to understand.

EDIT By the way, why not use stored procedures? Then you can handle all this NULL logic in the SP and you can simplify things on the front end call.

dcp
  • 54,410
  • 22
  • 144
  • 164
  • Thanks dcp, this looks straight forward to me. But imagine if my statement doesn't have 1 parameter, but 5. I would need 5^2 Statements = 25! – Zeemee Nov 18 '10 at 13:23
  • So this will avoid the problem that it still says ` = ` instead of ` IS `? – aioobe Nov 18 '10 at 13:24
  • 1
    @dcp, you seem to have changed your answer from one thing to something quite different. Why? – Paul Tomblin Nov 18 '10 at 13:25
  • setNull doesn't do anything that setString(col++,null) doesn't already do. setNull is more useful for types that don't have a natural null, like int or double. – Paul Tomblin Nov 18 '10 at 13:27
  • @Paul Tomblin - Yes, sorry for that. I was thinking he could use setNull, but that won't work for his case. That's only useful when you want to set a null value for something you're inserting/updating. – dcp Nov 18 '10 at 13:29
  • @Mulmoth - As mentioned in my edit, one thing you may want to consider is using a stored procedure, then you can handle the logic in the SP. – dcp Nov 18 '10 at 13:29
  • @dcp, I don't like stored procedures, because they tend to complicate deployment, maintenance and debugging. However, your suggestion is another aspect. – Zeemee Nov 18 '10 at 13:31
  • @Mulmoth - Interesting. I've found stored procedures to simplify maintenance tremendously, because the SQL code is compiled on the server. Plus, I can re-use the SP code in other applications rather than re-inventing the wheel each time. – dcp Nov 18 '10 at 13:34
1

If you use for instance mysql you could probably do something like:

select * from mytable where ifnull(mycolumn,'') = ?;

Then yo could do:

stmt.setString(1, foo == null ? "" : foo);

You would have to check your explain plan to see if it improves your performance. It though would mean that the empty string is equal to null, so it is not granted it would fit your needs.

Knubo
  • 8,333
  • 4
  • 19
  • 25
  • 1
    Thanks Knubo, but i cannot make sure that empty strings are not used as a value. – Zeemee Nov 18 '10 at 14:20
  • If you really really need the performance, you could pick some value that you are sure that never would be present to test for. This would be a performance hack, so I'd rather avoid it if you could. (For instance, it could be that your data never contains a single €, so you could check for that instead of ''. – Knubo Nov 18 '10 at 17:03
  • You were right on the money with your performance concern. It's VERY slow. I clock in at about 4000 times slower, on average, than queries without `IFNULL`. ~50ms instead of microseconds. – Mark Jeronimus Nov 17 '22 at 00:55
0

In Oracle 11g, I do it this way because x = null technically evaluates to UNKNOWN:

WHERE (x IS NULL AND ? IS NULL)
    OR NOT LNNVL(x = ?)

The expression before the OR takes care of equating NULL with NULL, then the expression after takes care of all other possibilities. LNNVL changes UNKNOWN to TRUE, TRUE to FALSE and FALSE to TRUE, which is the exact opposite of what we want, hence the NOT.

The accepted solution didn't work for me in Oracle in some cases, when it was part of a larger expression, involving a NOT.

Mazer
  • 81
  • 1
  • 2