Oracle中的NULL

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

另一个例子

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

转自：