ITPub博客

首页 > 数据库 > Oracle > oracle的左右全自然笛卡尔集连接

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

原创 Oracle 作者:stilllovekk 时间:2016-07-04 15:21:34 0 删除 编辑


总的来说呢,其他的都比较简单,但是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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: or与索引
请登录后发表评论 登录
全部评论

注册时间:2015-01-30

  • 博文量
    20
  • 访问量
    29446