oracle的左右全自然笛卡尔集连接

总的来说呢，其他的都比较简单，但是full join 相对有点不好理解，不过多做两个列子，多看看，也就理解了。




先建两张表，来点测试数据。
scott@ORA> create table t1 (s varchar2(2),n number(1,0));


Table created.


scott@ORA> insert into t1 values ('a',1);


1 row created.


scott@ORA> insert into t1 values ('b',2);


1 row created.


scott@ORA> insert into t1 values ('c',3);


1 row created.


scott@ORA> commit;


Commit complete.


scott@ORA>  create table t2(s varchar2(2),n number(1,0));


Table created.


scott@ORA> insert into t2 values('b',2);


1 row created.


scott@ORA> insert into t2 values('c',3);


1 row created.


scott@ORA> insert into t2 values('d',4);


1 row created.


scott@ORA> commit;


Commit complete.


scott@ORA> select s,n from t1;


S           N
-- ----------
a           1
b           2
c           3


scott@ORA> select s,n from t2;


S           N
-- ----------
b           2
c           3
d           3


平常不加join，就用逗号隔开的表名的连接方式
scott@ORA> select t1.s,t2.s from t1,t2 where t1.n=t2.n;


S  S
-- --
b  b
c  c


scott@ORA> select t1.s,t2.s from t1 inner join t2 on t1.n=t2.n;


S  S
-- --
b  b
c  c


scott@ORA> select t1.n,t2.n from t1 join t2 on t1.s = t2.s;


         N          N
---------- ----------
         2          2
         3          3


这三个是一样的，inner join中的inner  可以不写。只返回完全满足连接条件的数据。


scott@ORA>  select t1.s,t2.s from t1 left join t2 on t1.n=t2.n;


S  S
-- --
b  b
c  c
a


scott@ORA> select t1.s,t2.s from t1,t2 where t1.n=t2.n(+);


S  S
-- --
b  b
c  c
a


scott@ORA> select t1.s,t2.s from t1 left outer join t2 on t1.n=t2.n;


S  S
-- --
b  b
c  c
a


左连接，返回左表的所有数据，右表满足条件的就返回，不满足的就不反悔，并且对应的在查询结果中显示为空。
但是啊，并不一定左表有多少数据就返回多少条，没有去重功能的函数存在时，应该是返回结果集会大于等于左表数据量，而不会小于。
因为可能会存在  左：右＝1:n。在oracle以前的版本也可以这样写左连接，但是现在好像不推荐用这个写法，反正我写sql左连接都用left join。
另外，不管左右全，连接，都可以加个outer，等价。




右连接，其他的都跟left join一样，只不过一个返回左表的所有数据，一个返回右表的。没意思，不多说；如下是右连接。


scott@ORA>  select t1.s s1,t2.s s2 from t1 right join  t2 on t1.n=t2.n;


S1 S2
-- --
b  b
c  c
   d


scott@ORA>  select t1.s,t2.s from t1,t2 where t1.n(+)=t2.n;


S  S
-- --
b  b
c  c
   d


scott@ORA> select t1.s s1,t2.s s2 from t1 right outer join  t2 on t1.n=t2.n;


S1 S2
-- --
b  b
c  c
   d
全连接就是这样的：返回两表的所有数据，不满足条件的，对应的那个部分显示为空。另外全连接不支持（＋）的写法。
这个需要多看看。～～！


scott@ORA>  select t1.s,t2.s from t1 full join t2 on t1.n=t2.n;


S  S
-- --
b  b
c  c
   d
a


scott@ORA> select t1.s,t2.s from t1 full outer join t2 on t1.n=t2.n;


S  S
-- --
b  b
c  c
   d
a


scott@ORA>  select t1.s,t2.s from t1 full join t2 on t1.s = t2.s and t1.s='b';


S  S
-- --
b  b
   c
   d
c
a


scott@ORA>  select t1.s,t2.s from t1 full join t2 on t1.s = t2.s and t1.s='c';


S  S
-- --
   b
c  c
   d
b
a


scott@ORA> select t1.s,t2.s from t1 full join t2 on t1.s = t2.s and t1.s='a';


S  S
-- --
   b
   c
   d
b
c
a




还有个什么自然连接，就是natural join，原理是使用这个连接，不用写连接条件，oracle自己会根据两个表中相同的字段进行连接，必须字段名字段类型都相同。不然会报错。
还有就是用自然连接的话，select后面的字段前边不能加表的别名。
反正我感觉这就是个鸡肋，甚至系统中存在这样的我可能都会认为这个系统设计的有问题。懒得测试这个了。虾面的就是自然连接


scott@ORA>  select t1.s,t2.n from t1 natural join t2;
 select t1.s,t2.n from t1 natural join t2
             *
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier


scott@ORA> select t1.s,n from t1 natural join t2;
select t1.s,n from t1 natural join t2
       *
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier


scott@ORA> select s,n from t1 natural join t2;


S           N
-- ----------
b           2
c           3


另外一种呢，就是所谓的交叉连接，也就是笛卡尔集。这个东西，很多时候都是有问题的，但是很多时候却又利用他来造数据等。没法说，得根据实际情况来说。他是前边表每一条与后边的每一条去匹配。返回结果集的数量就是  前边表的数量乘以后边表的数量。如下：
scott@ORA> select t1.s,t2.s from t1,t2;


S  S
-- --
a  b
a  c
a  d
b  b
b  c
b  d
c  b
c  c
c  d


9 rows selected.


scott@ORA> select t1.s,t2.s from t1 cross join t2;


S  S
-- --
a  b
a  c
a  d
b  b
b  c
b  d
c  b
c  c
c  d


9 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30123160/viewspace-2121448/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30123160/viewspace-2121448/