空值字段匹配问题

[brotherfromaq]

有两个表TABLE1和TABLE2
TABLE1
dim_1 dim_2 dim_3 dim_4 dim_5 dim_6 dim_7 dim_8 dim_9 dim_10
a b c
d e f

a b c
a g f
a g f
TABLE2
dim_1 dim_2 dim_3 dim_4 dim_5 dim_6 dim_7 dim_8 dim_9 dim_10
a b c
d e f
d e f
a g f


用以下语句查询返回0条记录，为什么，正确的写法如何？

select * from table1 a,table2 b where a.dim_1=b.dim_1 and a.dim_2=b.dim_2 and a.dim_3=b.dim_3 and a.dim_4=b.dim_4 and a.dim_5=b.dim_5 and a.dim_6=b.dim_6 and a.dim_7=b.dim_7 and a.dim_8=b.dim_8 and a.dim_8=b.dim_8

[Adebayor]

[Quote=引用 6 楼 bw555 的回复:]
替换成''不行吧,
''和null是等价的，
''永远也不等于''

替换成任意在字段中不可能出现的字符串，

引用 5 楼 oraclelogan 的回复:

或者这样也行的：


SQL code  select a.*,b.*
    from table1 a, table2 b
  where nvl(a.dim_1,'') = nvl(b.dim_1,'')
    and nvl(a.dim_2,'') = nvl(b.dim_2,'')
    and nvl(a.dim_3,'') = nvl(b.dim_3,'')
    and nvl(a.dim_4,'') = nvl(b.dim_4,'')
    and nvl(a.dim_5,'')…
[/Quote]
是这样子的

[libolibo888]

那么前面说过null和''都不能直接=来判断，所以这里是错的
nvl(a.dim_2,'') = nvl(b.dim_2,'')不成立

这样是成立的。
SQL code select a.*,b.*
from table1 a, table2 b
where nvl(a.dim_1,'空值') = nvl(b.dim_1,'空值')
and nvl(a.dim_2,'空值') = nvl(b.dim_2,'空值')
and nvl(a.dim_3,'空值') = nvl(b.dim_3,'空值')
and nvl(a.dim_4,'空值') = nvl(b.dim_4,'空值')
and nvl(a.dim_5,'空值')…

[suncrafted]

空值和''都不能直接的=来连接

学习

[oraclelogan]

select a.*,b.*
  from table1 a, table2 b
 where a.dim_1(+) = b.dim_1
   and a.dim_2(+) = b.dim_2
   and a.dim_3(+) = b.dim_3
   and a.dim_4(+) = b.dim_4
   and a.dim_5(+) = b.dim_5
   and a.dim_6(+) = b.dim_6
   and a.dim_7(+) = b.dim_7
   and a.dim_8(+) = b.dim_8
   and a.dim_8(+) = b.dim_8

[ojuju10]

a.dim_1=b.dim_1 都改写这样吧

nvl(a.dim_1,'')=nvl(b.dim_1,'')

null 永远不会等于null

[hotyxm]

oracle 对空值是不做处理的。
你可以用decode来处理一下可能的空值字段，比如

select * from table1 a,table2 b 
where a.dim_1=b.dim_1 
and a.dim_2=b.dim_2 
and a.dim_3=b.dim_3 

and decode(a.dim_4,'','A',a.dim_4)=decode(b.dim_4,'','A',a.dim_4)
.....

[robin_ares]

[Quote=引用 2 楼 ojuju10 的回复:]
a.dim_1=b.dim_1 都改写这样吧

nvl(a.dim_1,'')=nvl(b.dim_1,'')

null 永远不会等于null
[/Quote]
只有引用的内容不允许回复！！

[oraclelogan]

或者这样也行的：

  select a.*,b.*
    from table1 a, table2 b
   where nvl(a.dim_1,'') = nvl(b.dim_1,'')
     and nvl(a.dim_2,'') = nvl(b.dim_2,'')
     and nvl(a.dim_3,'') = nvl(b.dim_3,'')
     and nvl(a.dim_4,'') = nvl(b.dim_4,'')
     and nvl(a.dim_5,'') = nvl(b.dim_5,'')
     and nvl(a.dim_6,'') = nvl(b.dim_6,'')
     and nvl(a.dim_7,'') = nvl(b.dim_7,'')
     and nvl(a.dim_8,'') = nvl(b.dim_8,'')
     and nvl(a.dim_8,'') = nvl(b.dim_8,'')

[bw555]

替换成''不行吧,
''和null是等价的，
''永远也不等于''

替换成任意在字段中不可能出现的字符串，

[Quote=引用 5 楼 oraclelogan 的回复:]
或者这样也行的：


SQL code select a.*,b.*
from table1 a, table2 b
where nvl(a.dim_1,'') = nvl(b.dim_1,'')
and nvl(a.dim_2,'') = nvl(b.dim_2,'')
and nvl(a.dim_3,'') = nvl(b.dim_3,'')
and nvl(a.dim_4,'') = nvl(b.dim_4,'')
and nvl(a.dim_5,'') = nvl(b.dim_5,'')
and nvl(a.dim_6,'') = nvl(b.dim_6,'')
and nvl(a.dim_7,'') = nvl(b.dim_7,'')
and nvl…
[/Quote]

[inthirties]

[Quote=引用楼主 brotherfromaq 的帖子:]
有两个表TABLE1和TABLE2
TABLE1
dim_1 dim_2 dim_3 dim_4 dim_5 dim_6 dim_7 dim_8 dim_9 dim_10
a b c
d e f

a b c
a g f
a g f
TABLE2
dim_1 dim_2 dim_3 dim_4 dim_5 dim_6 dim_7 dim_8 dim_9 dim_10
a b c
d e f
d e f
a g f


用以下语句查询返回0条记录，为什么，正确的写法如何？

select * from table1 a,table2 b where a.dim_1=b.dim_1 and a.dim_2=b.dim_2 and a.dim_3=b.dim_3 and a.dim_4=b.dim_4 …
[/Quote]

空值和''都不能直接的=来连接的，你用decode或者nvl转一下

NVL(eExpression1, eExpression2) 如果eExpression1为空的话返回eExpression2，否则返回eExpression1.

DECODE(eExpression, Search_1, Result_1) 比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值

用以上的做sample就是
nvl(a.dim_1,''), 如果a.dim_1为null 返回''

DECODE(a.dim_1, null, '', a.dim_1) 如果a.dim_1和null匹配的话，就返回 '' 否则返回 a.dim_1，


不过这里用''代替应该是不对的

我们来看看
nvl(a.dim_1,'')=nvl(b.dim_1,'') 这个关系式，

根据前面的介绍
nvl(a.dim_1,'')在a.dim_1为null的时候，这里返回''
同理nvl(b.dim_1,'')在b.dim_1为null的时候，这里返回''

那么前面说过null和''都不能直接=来判断，所以这里是错的

实验如下
SQL> select 1 from dual where nvl(null,'')=nvl(null,'');
未选定行

如果我们把''变成' ',结果就不一样了
SQL> select 1 from dual where nvl(null,' ')=nvl(null,' ');

1
----------
1

SQL>

所以这里直接nvl(a.dim_1,'') = nvl(b.dim_1,'') 是不行的 应该改成 nvl(a.dim_1,' ') = nvl(b.dim_1,' ')

[Quote]
select a.*,b.*
from table1 a, table2 b
where nvl(a.dim_1,' ') = nvl(b.dim_1,' ')
and nvl(a.dim_2,' ') = nvl(b.dim_2,' ')
and nvl(a.dim_3,' ') = nvl(b.dim_3,' ')
and nvl(a.dim_4,' ') = nvl(b.dim_4,' ')
and nvl(a.dim_5,' ') = nvl(b.dim_5,' ')
and nvl(a.dim_6,' ') = nvl(b.dim_6,' ')
and nvl(a.dim_7,' ') = nvl(b.dim_7,' ')
and nvl(a.dim_8,' ') = nvl(b.dim_8,' ')
and nvl(a.dim_8,' ') = nvl(b.dim_8,' ')
[/Quote]

或者给其他的默认值