1 2 3 Previous Next 43 Replies Latest reply: Dec 7, 2007 8:48 AM by 494018

# Using <> or != on a NULL

Hi,

This is probably a very easy question..

When I try to SELECT some rows based on tableA.col1 <> tableB.col1, I get less rows than expected back. I think it's because some of the tableA.col1 are NULL and tableB.col2 have values.

Is there a way to check for equality w/out having to use IS NULL and IS NOT NULL?

Thanks,
• ###### 1. Re: Using <> or != on a NULL
NVL() ?
• ###### 2. Re: Using <> or != on a NULL
Is there a way to check for equality w/out having to
use IS NULL and IS NOT NULL?
either (the most robust)
``````WHERE X!=Y
OR (X IS NULL AND Y IS NOT NULL)
OR (X IS NOT NULL AND Y IS NULL)``````
or, more compact
``WHERE decode(X,Y,1,2)=2``
• ###### 3. Re: Using <> or != on a NULL
Is there a way to check for equality w/out having to use IS NULL and IS NOT NULL?
e.g.:
``select * from emp where lnnvl(comm = :comm)``
• ###### 4. Re: Using <> or != on a NULL
select * from emp where lnnvl(comm = :comm)
note that LNNVL(NULL=NULL) is true. But maybe it is wished
• ###### 5. Re: Using <> or != on a NULL
And if you need to check inside PL/SQL (where DECODE is still not an option), this option is also available (at least from 10g on):
``NVL(NULLIF(X, Y), NULLIF(Y, X)) IS NOT NULL``
C.
• ###### 6. Re: Using <> or != on a NULL
In PL/SQL you can use IF ... THEN ... ELSE instead of decode. I wouldn't understand why to use a more cryptical command instead.
• ###### 7. Re: Using <> or != on a NULL
Depending on the code, I'd probably use a simple IF THEN construct. If, however, I've to build for example an auditing trigger, I'd argue that my solution would account for less code and therefore less maintainance effort.

``IF X <> Y OR (X IS NULL AND Y IS NOT NULL) OR (X IS NOT NULL AND Y IS NULL) THEN``
I would write
``IF NVL(NULLIF(X, Y), NULLIF(Y, X)) IS NOT NULL THEN ``
If I would stick to that construct, I would assume that other developers would soon find out (and maybe) adopt that technique. To prove that this really works, here's a little demonstration:
``````WITH t AS (SELECT 1 x
FROM dual
UNION
SELECT NULL
FROM dual
),
t2 AS (SELECT t.x
, t2.x y
FROM t
, t t2
)
SELECT t2.*
, CASE WHEN NVL(NULLIF(x, y), NULLIF(y, x)) IS NOT NULL
THEN '<>'
ELSE '=='
END eq
FROM t2
;

X          Y EQ
---------- ---------- --
1          1 ==
1            <>
1 <>
==``````
C.
• ###### 8. Re: Using <> or != on a NULL
what on earth is wrong with just the basic:

where nvl(x,'foo') = nvl(y,'foo')
• ###### 9. Re: Using <> or != on a NULL
This is ok, as long as you are sure that x and y are never 'foo' and are strings.
• ###### 10. Re: Using <> or != on a NULL
less code and therefore
less maintainance effort.
Are you serious?
IF X <> Y OR (X IS NULL AND Y IS NOT NULL) OR (X IS NOT NULL AND Y IS NULL) THEN

IF NVL(NULLIF(X, Y), NULLIF(Y, X)) IS NOT NULL THEN
which one is easier to maintain?
• ###### 11. Re: Using <> or != on a NULL
at least the first one will be easier to understand because it contains only <> , OR , AND, IS NULL and IS NOT NULL. Those operators as available in Oracle since ages. Very few people know about NULLIF, and this Function encapsulation may hurt more than one developer. Just my 2 cents ;-)

Anyway, it is still shorter, so if YOU are maintaining YOUR OWN code, than yes, it may require less maintenance
• ###### 12. Re: Using <> or != on a NULL
```WITH t AS (SELECT 1 x FROM dual UNION
SELECT NULL FROM dual),
t2 AS (SELECT t.x, t2.x y
FROM t, t t2)
select X,Y,
case when X=Y or nvl(X,Y) is null
then '==' else '<>' end as eq
FROM t2;```
```or
[pre]
WITH t AS (SELECT 1 x FROM dual UNION
SELECT NULL FROM dual),
t2 AS (SELECT t.x, t2.x y
FROM t, t t2)
select X,Y,decode(X,Y,'==','<>') as eq
FROM t2;```
Furthermore , We can use "SYS_OP_MAP_NONNULL".
But unofficial function.
• ###### 13. Re: Using <> or != on a NULL
at least the first one will be easier to understand
because it contains only <> , OR , AND, IS NULL and
IS NOT NULL. Those operators as available in Oracle
since ages. Very few people know about NULLIF, and
this Function encapsulation may hurt more than one
developer. Just my 2 cents ;-)
With that argument, why ever get a new version, since developers could become confused with new commands and options, right? Those are my 0.02 EUR.

C.
• ###### 14. Re: Using <> or != on a NULL
@CD: I really like your version, but I agree with Laurent. For most developers the the IF THEN ELSE version is better. Also consider that keyword highlighting helps to improve readability of this statement.

You mentioned an auditing trigger. I would suggest to write a packaged function/procedure for such a trigger, that includes the comparison and then inserts in the audit table. This is not only better maintainable, but also faster (saves parsing time).
1 2 3 Previous Next