oracle两个表自然连接,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/，如需转载，请注明出处，否则将追究法律责任。