Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1343629
  • 博文数量: 239
  • 博客积分: 5909
  • 博客等级: 大校
  • 技术积分: 2715
  • 用 户 组: 普通用户
  • 注册时间: 2010-07-24 20:19
文章分类

全部博文(239)

文章存档

2014年(4)

2013年(22)

2012年(140)

2011年(14)

2010年(59)

我的朋友

分类: Oracle

2012-11-16 17:00:30


点击(此处)折叠或打开

  1. You Asked

  2. HAi...

  3. what is the diffrence between

  4. select * from emp where comm is null

  5. select * from emp where comm = null

  6.  


  7. and we said...

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


  9. nothing is equal to null
  10. nothing is NOT equal to null

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

  12. ops$tkyte@ORA9IR2> select * from dual where null=null;
  13.  
  14. no rows selected
  15.  
  16. ops$tkyte@ORA9IR2> select * from dual where null <> null;
  17.  
  18. no rows selected
  19.  
  20. ops$tkyte@ORA9IR2> select * from dual where null IS null;
  21.  
  22. D
  23. -
  24. X
  25.  
  26. ops$tkyte@ORA9IR2>



  27. That is why code like:


  28.    if ( x = y )
  29.    then
  30.       ......
  31.    end if;

  32.    if ( a <> b )
  33.    then
  34.       ......
  35.    end if;


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

  39. For example:


  40. ops$tkyte@ORA9IR2> declare
  41.   2 x int;
  42.   3 y int;
  43.   4 a int;
  44.   5 b int := 55;
  45.   6 begin
  46.   7 dbms_output.put_line( 'enter' );
  47.   8 if ( x = y )
  48.   9 then
  49.  10 dbms_output.put_line( 'x = y' );
  50.  11 end if;
  51.  12 if ( a <> b )
  52.  13 then
  53.  14 dbms_output.put_line( 'a <> b' );
  54.  15 end if;
  55.  16 dbms_output.put_line( 'exit' );
  56.  17 end;
  57.  18 /
  58. enter
  59. exit

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

  63. a) true
  64. b) false
  65. c) i don't know <<<=== nulls

  66. The above code, if x=y should be true when x/y are null and if a<>b should be true when
  67. a/b are null must be coded as:
  68.  
  69. ops$tkyte@ORA9IR2> declare
  70.   2 x int;
  71.   3 y int;
  72.   4 a int;
  73.   5 b int := 55;
  74.   6 begin
  75.   7 dbms_output.put_line( 'enter' );
  76.   8 if ( x = y or (x is null and y is null) )
  77.   9 then
  78.  10 dbms_output.put_line( 'x = y' );
  79.  11 end if;
  80.  12 if ( a <> b or (a is null and b is not null) or (a is not null and b is null) )
  81.  13 then
  82.  14 dbms_output.put_line( 'a <> b' );
  83.  15 end if;
  84.  16 dbms_output.put_line( 'exit
另一个例子

点击(此处)折叠或打开

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


  2. is the same as:

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

  4. is the same as:

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

  6. is the same as:

  7. select * from DUAL where (TRUE);



  8. however,

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

  10. is the same as:

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

  12. is the same as:

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

  14. is the same as:

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

  16. is the same as:

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

  18. is the same as:


  19. select * from DUAL where (TRUE);

转自:
阅读(1445) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~