sql where not with nulls
Wednesday, September 24, 2008
Ah ha! Sound confusing? Why yes it is.
Turns out that SQL (and HQL for that matter) don't allow regular operators to run against a null field. Fair enough, it means:
SELECT * FROM myTable WHERE id=6
will evaluate to false if the id is null, so rows with null values won't be returned. Ok.
However, where it gets tricky is in NOT clauses, which also won't return if the value is null. If an operator (other than is null or is not null) is used on a null field, SQL evaluates it to null, which the where clause in turn evaluates to false. So...
SELECT * FROM myTable WHERE NOT id=6
will return rows where id is: 1, 2, 3, 4, 5, 7, 8 etc...
but NOT where id is: 6 or null
The way around it? If the field you are 'NOT' selecting on can be null, then you must include a 'is null' OR match:
SELECT * FROM myTable WHERE (NOT id=6 OR id is null)
0 comments:
Subscribe to:
Post Comments (Atom)