外连接的一些总结

[@more@]

外连接的一些总结

外连接扩展简单连接的结果，一个外连接返回的值不仅是所有满足连接条件的行，同时还有为NULL值的行。

建立试验表A、B

SQL> select * from a;

A1 A2

---------- --

1 1

2

3 3

SQL> select * from b;

B1 B2

---------- --

1 1

2 2

SQL> select * from a,b where a2=b2;

A1 A2 B1 B2

---------- -- ---------- --

1 1 1 1

SQL> select * from c;

C1 C2

---------- --

1 1

2 4

3

1、对于查询一个外连接AB两个表的话，并且返回A表上所有的行，这是一个左连接（left outer join），在FROM上使用LEFT JOIN,或者在where语句的B表的列上使用（+）。对于A上有一些行并不和表B匹配，Oracle会返回空值。

SQL> select * from a,b where a2=b2(+);

A1 A2 B1 B2

---------- -- ---------- --

1 1 1 1

2

3 3

SQL> select * from a left outer join b on a2=b2;

A1 A2 B1 B2

---------- -- ---------- --

1 1 1 1

2

3 3

2、对于查询一个外连接AB两个表的话，并且返回B表上所有的行，这是一个右连接（right outer join），在FROM上使用RIGHT JOIN,或者在where语句的A表的列上使用（+）。对于B上有一些行并不和表A匹配，Oracle会返回空值。

SQL> select * from a,b where a2(+)=b2;

A1 A2 B1 B2

---------- -- ---------- --

1 1 1 1

2 2

SQL> select * from a right outer join b on a2=b2;

A1 A2 B1 B2

---------- -- ---------- --

1 1 1 1

2 2

3、对于查询一个外连接AB两个表的话，并且返回A和B表上所有的行，这是一个全外连接（full outer join），在FROM上使用full JOIN。

SQL> select * from a full outer join b on a2=b2;

A1 A2 B1 B2

---------- -- ---------- --

1 1 1 1

2

3 3

2 2

4、在查询语句的FROM子句中有连接语法的时候是不能指定(+)操作的。

5、(+)只能用在WHERE子句中。

6、如果A、B是一种多连接状态的连接，需要在所有的条件上使用(+)。

7、外连接不能对自身进行。

SQL> select * from a where a2=a2(+);

select * from a where a2=a2(+)

*

第 1 行出现错误:

ORA-01416: 两表无法彼此外部连接

但是可以这样使用：

SQL> select * from a t1,a t2 where t1.a2=t2.a2(+);

A1 A2 A1 A2

---------- -- ---------- --

1 1 1 1

3 3 3 3

2

8、(+)操作只能用于列，不能适用于表达式。

9、一个条件包含(+)操作不能使用OR逻辑操作与其他条件联合使用。

10、条件不能用IN来比较一个带有操作的列。

11、不能在其子查询上比较任何带有(+)操作的列。

外连接是在SQL优化上难点之一，很多程序由于外连接的使用，会造成很多性能问题，这里简要总结以下几个存在的外连接使用不对的地方。

1、外连接的使用不当的地方

SQL> select * from a,b,c where a2(+)=b2 and a2=c2;

A1 A2 B1 B2 C1 C2

---------- -- ---------- -- ---------- --

1 1 1 1 1 1

SQL> select * from a,b,c where a2(+)=b2 and b2=c2;

A1 A2 B1 B2 C1 C2

---------- -- ---------- -- ---------- --

1 1 1 1 1 1

在这两个查询来看都有一个共同的特点，由于ab表之间有外连接的关系，但是ab表和其他表之间还是有限制条件，这样外连接就没有意义了，但是由于外连接的存在，优化器就会被限制，很多查询计划就会因为这个外连接的存在而不被优化器考虑。所以在这种情况下，应该将这个外连接删除。

2、递推的关系

SQL> select * from a,b,c where a2(+)=b2 and b2(+)=c2;

A1 A2 B1 B2 C1 C2

---------- -- ---------- -- ---------- --

1 1 1 1 1 1

2 4

3

Ab表之间有外连接，bc之间有外连接，既A要求B表的所有的行，而B要求C表的所有的行，最终的结果只是得到C表的所有的行。

SQL> select * from a,b,c where a2(+)=b2 and b2=c2(+);

A1 A2 B1 B2 C1 C2

---------- -- ---------- -- ---------- --

1 1 1 1 1 1

2 2

Ab表之间有外连接，bc之间有外连接，既A要求B表的所有的行，而C要求B表的所有的行，最终的结果只是得到B表的所有的行

总结以上两条，可以看到外连接是有递推的关系的，不能相互冲突。

SQL> select * from a,b,c where a2(+)=b2 and a2(+)=c2;

select * from a,b,c where a2(+)=b2 and a2(+)=c2

*

第 1 行出现错误:

ORA-01417: 表可以外部连接到至多一个其它的表

这里就是Ab表之间有外连接，ac之间也有外连接，就会出现问题

同时如果两个有外连接的表，无论其中哪一个和其他表有等值的限制条件的产生的时候，就会出现外连接无用的情况，但是这样就会造成性能问题。

外连接的改写

对于一个查询：

Select …,outer_joined_to_table.column

From some_table. outer_joined_to_table

Where …= outer_joined_to_table(+)

可以改写为：

Select …,(select column from outer_joined_to_table where …) from some_table;

ITPub博客

外连接的一些总结

外连接的一些总结

最新文章