full outer join只有9i以上才支持,8i不支持, 也不能等号两边都加(+),因此可以用union实现 例如: select a.id,b.id from table1 a,table2 b where a.id=b.id(+) union select a.id,b.id from table1 a,table2 b where a.id(+)=b.id
(+)在8i就是外连接,9i以上才有outer join。select * from a, b where a.id = b.id; 对于外连接,Oracle中可以使用“(+)”来表示,9i可以使用LEFT/RIGHT/FULL OUTER JOIN,下面将配合实例一一介绍。 1. 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的员工记录。 2. 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; 结果为:所有员工及对应部门的记录,包括没有任何员工的部门记录。 3. 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的员工记录和没有任何员工的部门记录。
也不能等号两边都加(+),因此可以用union实现
例如:
select a.id,b.id from table1 a,table2 b where a.id=b.id(+)
union
select a.id,b.id from table1 a,table2 b where a.id(+)=b.id