CODEWITHSUNDEEP
sqloracle-databaseoracle11gnull
Josh E
Josh E

Reputation: 117

why exactly does "WHERE <> NULL" return 0 results

i was looking through some of the examples in the oracle sql sample questions here:

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=303&p_certName=SQ1Z0_051

i'm just curious as to why "where <> NULL" causes it to return 0 results...my original thinking was that it would generate an error since null is not a value and you would have to use "where is not null"...

does oracle just have a fail through mechanism that whenever you don't have a value after <> it won't generate an error it just won't return any results?

edit: alex poole's answer is much clearer/better imo than the one's in the "duplicate"

Upvotes: 0

Views: 82

Answers (2)

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

Your thinking has some sense if looking at the constant NULL but it's much aggravating if thinking about variable or field or bind variable which can be NULL. Just for example:

select * from table where field > 5

would raise an exception if field would contain null values.

select * from table1 t1
start with parent_id is null
connect by parent_id = prior id

would raise an exception if parent_id would be null (yes... look at connect by clause)... and so on.

Oracle uses the rule: almost every operation with null (except of specially designed) would lead the null result too. a = null would produce null. a <> null would produce null. a in (1,2,3,null,5) would produce null. And so on. Sometimes it can be used for simplifying the code. Just for example:

SQL> create table t$t(a integer, b integer, check(a < b));

Table created

SQL> insert into t$t values (1, 2);

1 row inserted

SQL> insert into t$t values (1, 0);

ORA-02290: check constraint (AP.SYS_C00700649) violated

SQL> insert into t$t values (1, null);

1 row inserted

SQL> insert into t$t values (null, null);

1 row inserted

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191560

Null is undefined; it isn't equal to, or not equal to, anything. It has its own section in the documentaion, and the conditions table shows that asking is anything is not equal to null (using != or <> comparisions) is 'unknown'.

It isn't an error though, just a bug in the query; and it isn't the same as not having an expression after the <>. You do have an expression, it's just null, and not comparable to any other value.

So when you use where something <> null the result is unknown, and the filter rejects all rows. The same would happen with = null, even is all the values were not null. The result of the comparison is 'unknown' in both cases, so it wouldn't be reasonable to include any rows for either. You can't say that any rows match the condition.

(Arguably you also can't say any rows don't match, but it makes more sense to reject everything than include everything in that case - to me anyway! though I liked how @Sebas phrased it.)

Upvotes: 2

Related Questions