Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, ventakeshwara.

Asked: April 12, 2004 - 7:51 am UTC

Last updated: November 14, 2012 - 10:22 am UTC

Version: 9i

Viewed 10K+ times! This question is

You Asked

HAi...

what is the diffrence between

select * from emp where comm is null

select * from emp where comm = null



and we said...

besides that one "makes sense" and the other will never return any rows?


nothing is equal to null
nothing is NOT equal to null

but a NULL can be "null". When you need to retrieve NULLS, you must use "is null"

ops$tkyte@ORA9IR2> select * from dual where null=null;

no rows selected

ops$tkyte@ORA9IR2> select * from dual where null <> null;

no rows selected

ops$tkyte@ORA9IR2> select * from dual where null IS null;

D
-
X

ops$tkyte@ORA9IR2>



That is why code like:


if ( x = y )
then
......
end if;

if ( a <> b )
then
......
end if;


might be considered "NOT SAFE" since if X and Y are both NULL -- that if block will not be executed. Likewise if A is null and b is NOT NULL, that if block will not be executed.

For example:


ops$tkyte@ORA9IR2> declare
2 x int;
3 y int;
4 a int;
5 b int := 55;
6 begin
7 dbms_output.put_line( 'enter' );
8 if ( x = y )
9 then
10 dbms_output.put_line( 'x = y' );
11 end if;
12 if ( a <> b )
13 then
14 dbms_output.put_line( 'a <> b' );
15 end if;
16 dbms_output.put_line( 'exit' );
17 end;
18 /
enter
exit

here, most programmers might think "x=y" should be true and that "a <> b" should be true. but tri-valued logic, under which NULLS are designed, means that boolean compares actually have three outcomes:

a) true
b) false
c) i don't know <<<=== nulls

The above code, if x=y should be true when x/y are null and if a<>b should be true when a/b are null must be coded as:

ops$tkyte@ORA9IR2> declare
2 x int;
3 y int;
4 a int;
5 b int := 55;
6 begin
7 dbms_output.put_line( 'enter' );
8 if ( x = y or (x is null and y is null) )
9 then
10 dbms_output.put_line( 'x = y' );
11 end if;
12 if ( a <> b or (a is null and b is not null) or (a is not null and b is null) )
13 then
14 dbms_output.put_line( 'a <> b' );
15 end if;
16 dbms_output.put_line( 'exit' );
17 end;
18 /
enter
x = y
a <> b
exit

PL/SQL procedure successfully completed.


Rating

  (20 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Null Values

Venkateswara Reddy, April 13, 2004 - 4:03 am UTC

Tom

Its realy very good.

Thanks

Reddy

1 + null <> sum(1, null)

Jim Dickson, November 05, 2004 - 5:59 am UTC

To me, the behaviour below is inconsistent but I am sure you can provide an explanation.

It appears SUM function and addition operator behave differently (at least when datatype not specified).

Tested on 9.2.0.5.0 Standard on Solaris 2.8

I am sure ANSI say something like null operator operand = null.

jdickson@DV01> select sum(a) from
2 (
3 select 1 a from dual
4 union all
5 select null a from dual
6 )
7 ;

SUM(A)
----------
1

jdickson@DV01>
jdickson@DV01> select 1 + null from dual;

1+NULL
----------




Tom Kyte
November 05, 2004 - 4:25 pm UTC

aggregates, by their definition, ignore nulls.

sum, min, max, count( expression ) -- they ignore nulls.


1+null is null by definition.

follow-up

Jim Dickson, November 05, 2004 - 6:07 am UTC

I meant to write:
I am sure ANSI say something like:
- NULL operator operand = null
and
- aggregates ignore NULLs.

But surely this leads to inconsitency above.

Tom Kyte
November 05, 2004 - 4:26 pm UTC

it is not an inconsistency -- you are comparing an expression:

1+null

to an aggregate

sum()

and they are not comparable, they are different.

What if we only have nulls?

alastair green, January 09, 2007 - 11:34 am UTC

Hello Tom,

We recently had a case where SUM was returning NULL as there were no non-NULL rows. I had naively assumed that it would return zero in this case:

SQL> create table Test_agg
2 (
3 Val_col integer
4 )
5 ;

Table created

SQL> insert into test_agg(val_col)values(1);

1 row inserted

SQL> insert into test_agg(val_col)values(2);

1 row inserted

SQL> insert into test_agg(val_col)values(3);

1 row inserted

SQL> insert into test_agg(val_col)values(null);

1 row inserted

SQL> select sum(val_col)from test_agg;

SUM(VAL_COL)
------------
6

SQL> delete test_agg where val_col is not null;

3 rows deleted

SQL> select sum(val_col)from test_agg;

SUM(VAL_COL)
------------

SQL>

Is this ANSI standard behaviour, if not, what is the rationale for doing it this way? My mental model was to think of nulls as being replaced by zeros but this is certainly not the case.
Tom Kyte
January 11, 2007 - 9:33 am UTC

nulls are NOT replaced by zeroes, sum(expression) is defined as the sum of the non-null values of expression.

There are no non-null values to sum, there is "nothing", the absence of anything here.

What if we only have nulls? Addendum

alastair Green, January 10, 2007 - 9:35 am UTC

I meant to add this on my previous post:

It appears that SUM is initially NULL and as non-NULL values are found ORACLE calculates the sum. Why is the initial value not zero?

To: alastair Green

Michel Cadot, January 12, 2007 - 12:11 am UTC


Why an initial value of 0?

A sum does not start at 0. It starts at the first value encountered. Then you add the following ones.
If there is no value, there is no sum; so the result is NULL.

Regards
Michel

use NVL function

raaghid, January 12, 2007 - 7:28 am UTC

Use NVL function, provided if both the variable values are null then it is considered as equal values.

SQL> declare
2 x int;
3 y int;
4 a int;
5 b int := 55;
6 begin
7 dbms_output.put_line( 'enter' );
8 if ( nvl(x,0) = nvl(y,0) )
9 then
10 dbms_output.put_line( 'x = y' );
11 end if;
12 if ( nvl(a,0) <> nvl(b,0) )
13 then
14 dbms_output.put_line( 'a <> b' );
15 end if;
16 dbms_output.put_line( 'exit' );
17 end;
18 /

strange bug

martina, January 12, 2007 - 9:09 am UTC

hi Tom,

when searching metalink i encountered to following:
-- bug 5608853 base bug 5608853
After 9i->10g upgrade , Customer met wrong result.
create table test1(aa varchar(10),bb varchar(10));
create table test2(cc varchar(10),aa varchar(10),dd varchar(10));
insert into test1 values('11','qaz');
insert into test1 values('22','wsx');
insert into test1 values('33','edc');
commit; .
select a.bb from test1 a,test2 b
where a.aa=b.aa(+)
and a.bb='qaz'
and b.dd(+)='';

< Result> 9i => qaz -> no problem
10g => no rows selected -> wrong result

So I copied and pasted that and tried it. It returned 'qaz' at 9i and no rows selected at 10g. then a colleague tried it too and found that it WORKED (returned qaz!) at one of our 10g. We did not find any differences between our 10gs. I was about to file a SR when i realized that it is no bug at all as it contains *and b.dd(+)=''; * so *no rows selected* is expected.

why does it return 'qaz' at 9i and at least one 10g???
why is it filed as a bug???

a puzzled martina

to raaghid; Bug waiting to happen

Marco van der Linden, January 13, 2007 - 3:31 am UTC

If you mean to test x = y where either both are equal or both are null, you definitely have a bug there. Run the code below and you'll see what I mean.

declare
  x number;
  y number := 0;
begin
  if (x = y or (x is null and y is null))
  then
    dbms_output.put_line('x=y or x is null and y is null');
  end if;
  --
  if nvl(x, 0) = nvl(y, 0)
  then
    dbms_output.put_line('x=y or (x=0 and y is null) or (x is null and y=0) or (x is null and y is null)');
  end if;
end;

You are not comparing x and y where both may be null, you are saying 'if x or y is null, treat them as if they were zero'. Apples and pears.

NULL = UNKNOWN

Neil Barsema, January 15, 2007 - 9:25 am UTC

The moment the whole null issue realy hit home with me, was when I was told to think of it as 'Unknown' in stead of 'no value' sudenly it starts to make sense that 'unknown = unknown' is unknown, it might be true, just like unknown <> unknown might be.

The only place where this doesn't realy hold up is

select * from dual where '' is null

As far as I can see there is no way to have an empty string be 'known' in Oracle.


unknown is unknown

martina, January 16, 2007 - 9:19 am UTC

hi neil,

select * from dual where '' is null;
Was always a synonym for me for:
select * from dual where null is null;

That is why i´m so bewildered that a query containing
the line b.dd(+)='' can deliver a result!

regards,martina




Try to keep this straight ...

Phil Singer, January 17, 2007 - 9:26 am UTC

This last clause points up to a long standing issue in Oracle. Oracle implements the SQL null as an empty string. Oracle recognizes that this is 'wrong', and advises everyone not to rely on it.

In other words, '' is not a null, it is an empty string.

I know exactly how long '' is; I have no idea how long a null is. If I know something about a datum, it cannot be null.

With other databases, which distinguish between NULL and '', a predicate such as "and col1 = '' " makes perfect sense. In fact, if you browse the MetaLink forums, you will find plenty of people trying to port applications which support empty string to Oracle, having problems, and wondering what to do.

Still strange

martina, January 18, 2007 - 12:57 pm UTC

ok, i would have written where b.dd (+) is null anyway if it was my statement. I would not have written = null or =''. but the outcome of the statement is null = '' due to the outer join. and that should not give any results, schould it?

strange is the fact that it gives results on 9i and on 1 10g out of several and we cannot find the difference.

regards, martina

Solution may lie in this direction

Phil Singer, January 18, 2007 - 11:06 pm UTC

I'm sorry that I did not catch the real follow up question here. Obviously, this is classified as a bug, because, whenever two different versions of the RDMS give different results for the same query, there must be a bug.

As to how this could happen, my hunch is that the two versions are resolving the ambiguity in the old outer join syntax two different ways. That predicate, b.dd='' can first be applied to the test2 table, and then the outer join takes place, or the outer join can first be made, and both predicates then applied. In the first case, only the a.bb='qaz' is left to be applied to the intermediate table, which, unless I am completely confused, will contain all three rows of test1 (outer joins always contain all rows of the required table which have not been filtered away).

ANSI NULLS (10g)

Elwyn Lloyd Jones, November 12, 2009 - 8:55 am UTC

If:

> select * from DUAL where (null=0 or 1=1);

works (It returns DUMMY: X)

Why (when I invert the logic) doesn't:

> select * from DUAL where not (null=1 or 1=0);

work? (It returns DUMMY: NULL)

Tom Kyte
November 15, 2009 - 1:57 pm UTC

select * from DUAL where (null=0 or 1=1);


is the same as:

select * from DUAL where (UNKNOWN or TRUE);

is the same as:

select * from DUAL where (UNKNOWN) or (TRUE);

is the same as:

select * from DUAL where (TRUE);



however,

select * from DUAL where not (null=1 or 1=0);

is the same as:

select * from DUAL where not (UNKNOWN or FALSE);

is the same as:

select * from DUAL where not (UNKNOWN) or not (FALSE);

is the same as:

select * from DUAL where not (UNKNOWN) or (TRUE);

is the same as:

select * from DUAL where (UNKNOWN) or (TRUE);

is the same as:


select * from DUAL where (TRUE);



I don't understand your "logic" thinking that the second query doesn't work? You didn't just invert the logic, you changed the predicate entirely.

I have no idea why you might think it should return nothing?? can you explain?


because ...

Sokrates, November 16, 2009 - 2:35 am UTC

because:

SQL> select * from DUAL where (null=0 or 1=1);

DUM
---
X

SQL> select * from DUAL where not (null=1 or 1=0);

no rows selected




Tom, your answer was not correct:

select * from DUAL where not (null=1 or 1=0)

is equivalent to
select * from DUAL where not (UNKNOWN or FALSE); 


which is equivalent to
[
see
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions004.htm#sthref2743
]

select * from DUAL where not (UNKNOWN);

which is
select * from DUAL where (UNKNOWN);


which surely does not return any row

The point to remember here is

UNKNOWN or FALSE : UNKNOWN
UNKNOWN or TRUE : TRUE
!!

Tom Kyte
November 23, 2009 - 9:14 am UTC

doh, thanks for that followup.

guess I should have just:

ops$tkyte%ORA11GR2> set autotrace on
ops$tkyte%ORA11GR2> select * from dual where NOT( 1=0 or null=null );

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3752461848

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
<b>
   1 - filter(NULL IS NOT NULL)
</b>

Sokrates, November 23, 2009 - 9:32 am UTC


Does DISTINCT treat NULL as duplicate???

Mukund Nampally, April 10, 2010 - 5:07 pm UTC

Hi Tom,
I think Oracle considers each NULL as a unique value.
But when I do the distinct on columns having NULL values I get only one record with NULL value instead of all the records which have NULL value.
example: the standard emp table where comm column can have NULL value.
select distinct comm from emp gives:
--> this is NULL
1400.00
500.00
300.00
0.00
The first entry being the NULL value
But, is that not I should expect all the records with NULL values?Is that in case of ONLY NULL values the distinct keyword gives only one NULL value
Test: create table t(a number unique );
insert into t values (1);
insert into t values (NULL);
insert into t values (NULL);
insert into t values (NULL);
select * from t gives:
1
--> NULL
--> NULL
--> NULLL
and select distinct a from t gives
1
--> NULL



Tom Kyte
April 13, 2010 - 9:05 am UTC

that is the "documented, by definition" behavior of distinct as per the language specification.


group by works the same way.



NULLS and Cardinality

A reader, November 12, 2012 - 5:04 pm UTC

In your column in the latest issue of the Oracle Magazine ( http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62asktom-

1867739.html), you discussed NULLs and cardinality. I'm not quite following your example--why does Oracle think there are only 175

rows instead of the original estimate of 36,024 rows after you updated NULLs to '01-jan-9999'? Can you please elaborate? THanks.



Tom Kyte
November 14, 2012 - 10:22 am UTC

think of the histogram - we have 703 distinct values in the column before the update - 704 afterwards. So, we have a frequency (not height balanced) histogram - an imperfect view of the data.

without that whacky value - the histogram would have looked something like:

     x
     x
     x
     x
     x
     x
     x
     x
     x
xxxxxxxx



with that whacky out lying value - it would be:

     x                                                                       x
     x                                                                       x
     x                                                                       x
     x                                                                       x
     x                                                                       x
     x                                                                       x
     x                                                                       x
     x                                                                       x
     x                                                                       x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


if it were *height balanced*, but it is not - so we have to put many values into each bucket and you end up with (conceptually)



                      xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


anytime you have more than 254 unique values - the histogram becomes "imperfect" and depending on the nature of the data - could be really bad.

(the next release has some new things to help address thing - but still - if you don't have to use a bad practice - don't!)


Other than NULL, what else do we have

Udankar, May 21, 2015 - 4:04 pm UTC

When I write a SELECT QUERY

SELECT COLUMN_NAME, COUNT(*)
FROM MATERIALIZED_VIEW
GROUP BY COLUMN_NAME
ORDER BY COLUMN_NAME

The first 3 rows are BLANK for the COLUMN_NAME

COLUMN_NAME | COUNT(*)
======================
BLANK | 4
BLANK | 101396
BLANK | 32868

When I rewrite the same SELECT QUERY as

SELECT COLUMN_NAME, COUNT(*)
FROM MATERIALIZED_VIEW
where COLUMN_NAME is null
GROUP BY COLUMN_NAME
ORDER BY COLUMN_NAME

No rows are returned

When I tweak the same SELECT QUERY again

SELECT COLUMN_NAME, COUNT(*)
FROM MATERIALIZED_VIEW
where COLUMN_NAME = ' '
GROUP BY COLUMN_NAME
ORDER BY COLUMN_NAME

The result set is

COLUMN_NAME | COUNT(*)
======================
BLANK | 32868

I am not sure what value is there in the other 2 blanks, when they are not "NULL" or BLANK SPACE


I am querying a Materialized view in Oracle.
This base table for the Materialized view is a DB2 table.
The column in discussion is a CHAR(18)

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here