在讲外连接之前先举例介绍内连接也就是一般的相等连接
select * from a b where aid = bid;
对于外连接Oracle中可以使用(+)来表示i可以使用LEFT/RIGHT/FULL OUTER JOIN下面将配合实例一一介绍
LEFT OUTER JOIN左外关联
SELECT elast_name edepartment_id ddepartment_name
FROM employees e
LEFT OUTER JOIN departments d
ON (edepartment_id = ddepartment_id);
等价于
SELECT elast_name edepartment_id ddepartment_name
FROM employees e departments d
WHERE edepartment_id=ddepartment_id(+);
结果为所有员工及对应部门的记录包括没有对应部门编号department_id的员工记录
RIGHT OUTER JOIN右外关联
SELECT elast_name edepartment_id ddepartment_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (edepartment_id = ddepartment_id);
等价于
SELECT elast_name edepartment_id ddepartment_name
FROM employees e departments d
WHERE edepartment_id(+)=ddepartment_id;
结果为所有员工及对应部门的记录包括没有任何员工的部门记录
FULL OUTER JOIN全外关联
SELECT elast_name edepartment_id ddepartment_name
FROM employees e
FULL OUTER JOIN departments d
ON (edepartment_id = ddepartment_id);
结果为所有员工及对应部门的记录包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录
