0

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"

Josh E
  • 87
  • 3
  • 9
  • I would have expected it to disregard the where clause since something that is a value is different from something that is not... I'd be interested in the answer. – Tonio Apr 21 '15 at 16:42
  • I think this holds the answer: http://stackoverflow.com/a/9581790/3401018 – Tonio Apr 21 '15 at 16:43
  • that's another thing, it's doing a "where <> null", which should result in it never being the case since it's never not going to not equal null, wouldn't it just return everything – Josh E Apr 21 '15 at 16:44
  • 1
    @JoshE The condition is not valid, so it does not return anything. You have to think by sequents. "Is it true that xxx <> null?" No. – Sebas Apr 21 '15 at 16:47
  • possible duplicate of [Not equal <> != operator on NULL](http://stackoverflow.com/questions/5658457/not-equal-operator-on-null) – Lukas Eder Apr 21 '15 at 16:52

2 Answers2

2

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.)

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • hmmm so because it is treated as an "unknown" result, oracle says "damn, don't know what to do here, can't compare these two values, so i won't return anything" – Josh E Apr 21 '15 at 16:46
  • Isn't more like I want to compare A and B but I don't know what B is, so I don't know what A <> B might be so I'll just reject the clause? – Tonio Apr 21 '15 at 16:47
  • 2
    @JoshE - no, actually Oracle (or any other relation database) knows *precisely* what to do in this case. The rule is simple - "any comparison with NULL returns NULL". – Bob Jarvis - Слава Україні Apr 21 '15 at 16:48
0

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
Sanders the Softwarer
  • 2,478
  • 1
  • 13
  • 28