ORACLE 表连接方法

ORACLE 8i，9i 表连接方法。
一般的相等连接：
select * from a, b where a.id = b.id;
这个就属于内连接。

对于外连接：
Oracle中可以使用“(+) ”来表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN

LEFT OUTER JOIN：左外关联
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
等价于
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id(+)
结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录。

RIGHT OUTER JOIN：右外关联
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
等价于
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+)=d.department_id
结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录。

FULL OUTER JOIN：全外关联
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。ORACLE8i是不直接支持完全外连接的语法，也就是说不能在左右两个表上同时加上(+)，下面是在ORACLE8i可以参考的完全外连接语法
select t1.id,t2.id from table1 t1,table t2 where t1.id=t2.id(+)
union
select t1.id,t2.id from table1 t1,table t2 where t1.id(+)=t2.id

连接类型	定义	图示	例子
内连接	只连接匹配的行		select A.c1,B.c2 from A join B on A.c3 = B.c3;
左外连接	包含左边表的全部行（不管右边的表中是否存在与它们匹配的行）以及右边表中全部匹配的行		select A.c1,B.c2 from A left join B on A.c3 = B.c3;
右外连接	包含右边表的全部行（不管左边的表中是否存在与它们匹配的行）以及左边表中全部匹配的行		select A.c1,B.c2 from A right join B on A.c3 = B.c3;
全外连接	包含左、右两个表的全部行，不管在另一边的表中是否存在与它们匹配的行		select A.c1,B.c2 from A full join B on A.c3 = B.c3;
（theta）连接	使用等值以外的条件来匹配左、右两个表中的行		select A.c1,B.c2 from A join B on A.c3 != B.c3;
交叉连接	生成笛卡尔积——它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行一一匹配		select A.c1,B.c2 from A,B;

对于下面的使用到外连接的sql,
例一 select a.col1,a.col3 from a,b where a.col1=b.col2(+)
关于连接条件，经常出现的错误等价如下
1，a.col1=b.col2 or a.col is null

2,a.col1=b.col2 or a.col1 not in (select b.col2 from b) or a.col1 is null

（+）在右边，保证了左边表中的所有记录都会被取到，相当于一部分（part A)是select a.col1,b.col2,a.col3 from a,b where a.col1=b.col2取出的这部分记录；另一部分(part B)是a表中不满足上面条件剩下的所有记录(包括2种，一种是记录中a.col1为空，另一种是a.col1取了b.col2未出现过的值）都会在表里面显示，这部分select出来的所有与b表相关的字段均为空。所以左连接查询得到的记录条数应该与a表一致（记录数一致是我目前05.8.21的认识，需要思考确认）

正确的等价： part A：a.col1=b.col2 等价于 a.col1 in (select b.col2 from b where b.col2=a.col1)。这个看起来似乎很废话，是为了与下面做对比。
part B ：等价于 a.col1 not in (select b.col2 from b where b.col2=a.col1)。这个与a.col1 not in (select b.col2 from b where b.id is not null )的区别在于a.col1为空的记录是否能够被取出。对于b.col2 is not null这个限制，是因为在not in 中的select子查询的结果也就是b.col2为null的记录存在，主查询将一条记录也查不出来。这点和in不同。
null,not in,in的一些试验：
select 1 from dual where null is null; return 1 record
select 1 from dual where null = null; return 0 record
select 1 from dual where null in null; return 0 record
select 2 from dual where 1 in (select 1 from dual union select null from dual); return 1 record
select 2 from dual where 1 not in (select 1 from dual union select null from dual); return 0 record

注意这里面的限制，例一中主查询仅仅选择与a表有关的字段，所以上述等价成立：select a.col1,a.col3 from a where a.col1=b.col2 or a.col1 not in (select b.col2 from b where b.col2=a.col1 and b.col2 is not null)。如果选择部分b表中子段，则from 后的表需要包括b表，将无法实现上面的等价。这样的查询当然可以实现，但如果不使用左连接，查询语句会复杂的多。所以左右连接是非常简洁有用的语法。(因为子查询中可以出现主查询中的表名和字段名作为条件，子查询的表和字段不能被主查询直接使用，这很容易理解）

最最需要搞清楚的还是null和无记录的区别。select null from dual,返回了一条记录，只是值是null;例子select 1 from dual where 1=2,返回0条记录。
下面的试验，他们分别应用于not in 的子查询中
select a.id,a.name from test_1 a where a.id not in (select null from dual) ; return 0 record.
select a.id,a.name from test_1 a where a.id not in (select 1 from dual where 1=2) ; return a表所有记录
select a.id,a.name from test_1 a where a.id in (select 1 from dual where 1=2) ; return 0 record.
另外注意
select a.id,a.name from test_1 a where a.id in (select null from dual)；return 0 record.
因为这里符合上面提到的null in null的情况。

8.23补充
select 1 from dual where null like null; return 0 record.
只有null is null 是肯定成立的。所以在考虑一些允许为空的字段与参数的等值条件时，例如一个varchar2类型，通常做的是where col like nvl(:p_1,'%')，此时当参数p_1为空，col为空的记录自然不应该匹配，但当参数p_1和col都为空，此条件无法涵盖。所以应该修改为where col like nvl(:p_1,'%') or (col is null and :p_1 is null)

ps.发现对于一般的开发工作，在不考虑性能优化什么的时候，最最容易出现bug的就是关于一些边界条件的考虑。

原文地址： http://xsb.itpub.net/post/419/54491

[@more@]

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

转载于:http://blog.itpub.net/7282477/viewspace-1003916/