Re: WHERE clause handling of null: "IS NULL" versus "=?" & setNull

[vrota...@gmail.com]

On Mon, Mar 12, 2012 at 6:41 PM, Mark Addleman <markad...@gmail.com> wrote:

Attached is a test case that demonstrates a difference in how where clause handles NULL.  In the first case, my SELECT includes "WHERE c IS NULL"  My second case has "WHERE c=?" and then I do preparedStatement.setNull(1, Types.VARCHAR).  The queries return different results.  There must be some difference in the semantics of the two queries but I don't know what it is.  Can someone explain it?  Thanks

NULL = NULL -> false
NULL <> NULL -> false

and so on. That's the standard, and raison d'être for IS NULL/IS NOT NULL sql operators. Any good intro to databases should explain it
 

   Vasile Rotaru

[Mark Addleman]

Gotcha.  Thanks for the explanation.  Looks like I need to bone up on my Chris Date :)

One more question:  Given the spec, what value is the setNull method in PreparedStatement? 

On Monday, March 12, 2012 9:59:44 AM UTC-7, Vasile Rotaru wrote:

[vrota...@gmail.com]

On Mon, Mar 12, 2012 at 7:17 PM, Mark Addleman <markad...@gmail.com> wrote:

Gotcha.  Thanks for the explanation.  Looks like I need to bone up on my Chris Date :)

One more question:  Given the spec, what value is the setNull method in PreparedStatement? 

Never used that one. For queries at least. I did for updates, though. And, while I may mis-remember it, it seems that setObject allows you to set both null and non-null values. Having a separate setNull make sense if you cannot set NULL with setInt, setString, setDate..  I remember such a limitation, but it may have been just a Hibernate thing. Not sure

--
   Vasile Rotaru

[Rami Ojares]

> what value is the setNull method in PreparedStatement?

Value NULL obviously. Some say NULL is not a value but a placeholder for
a proper value.
But in practice it is treated as a value whose type is compatible with
all other types (it's type is supertype of all other types unless the
type explicitly excludes the null value from it's set of values).
I find it a good decision in H2 to omit the value UNKNOWN.

It is worth mentioning that
- if where expression evaluates to null it is treated as not matching
the row.
- if check constraint evaluates to null it is considered as
passing/matching the check constraint.

- Rami

[Thomas Mueller]

Hi,

Instead of using WHERE C = ? / C IS NULL you could use (but only in
H2): WHERE C IS ?. The standardized way is actually WHERE C IS NOT
DISTINCT FROM ?, but many databases don't support it yet (H2 does
support it). See also:

http://h2database.com/html/grammar.html#condition_right_hand_side

Regards,
Thomas

> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.