转：oracle连接查询

SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
  FROM HR.EMPLOYEES E LEFT OUTER JOIN HR.DEPARTMENTS D
       ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

 
 
 SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
  FROM HR.EMPLOYEES E ,HR.DEPARTMENTS D 
  WHERE E.DEPARTMENT_ID= D.DEPARTMENT_ID(+)
  
  -----------------
  
  SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
  FROM HR.EMPLOYEES E RIGHT OUTER JOIN HR.DEPARTMENTS D
       ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
 
 
 SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
  FROM HR.EMPLOYEES E ,HR.DEPARTMENTS D 
  WHERE E.DEPARTMENT_ID(+)= D.DEPARTMENT_ID  
  -------------------------
  
    SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
  FROM HR.EMPLOYEES E FULL OUTER JOIN HR.DEPARTMENTS D
       ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
       
-- 全连接是左连接和右连接的并集
--内连接是左连接和右连接的交集     
 
SELECT COUNT(0) FROM HR.EMPLOYEES CROSS JOIN HR.DEPARTMENTS  --共2889条记录，笛卡尔积

SELECT COUNT(0) FROM HR.EMPLOYEES   --107  条记录
SELECT COUNT(0) FROM HR.DEPARTMENTS   --27 条记录

SELECT 107 * 27 FROM DUAL;  --2889

-------------------------
SELECT * FROM HR.EMPLOYEES NATURAL JOIN HR.DEPARTMENTS 


SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
  FROM HR.EMPLOYEES E INNER JOIN HR.DEPARTMENTS D
       ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

SELECT E.EMPLOYEE_ID, E.LAST_NAME, DEPARTMENT_ID, D.DEPARTMENT_NAME
  FROM HR.EMPLOYEES E INNER JOIN HR.DEPARTMENTS D USING (DEPARTMENT_ID)

讲外连接之前，先举例介绍内连接，也就是一般的相等连接。

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的员工记录和没有任何员工的部门记录。

其实啊：外连接就是查两张表左连接就是左边的表全有值，右边表的值可以为空（+）

右连接是左边表值可以为空（+）右边表的值全有

全连接是左连接和右连接的并集好像是这个符号（U）

内连接是左连接和右连接的交集。。。

其余连接方式：

cross join: 交叉连接，查出的表的笛卡尔积 eg: select * from A cross join B （A表有M行，B表有N行，结果是M*N行）。

左连接

a.id=b.id(+) ===> a表内容全部显示，以左边的表为基准

left join : left join 左边的表全部显示,以左边的表为基准；

select * from emp e left join dept d on e.deptno=d.deptno;

右连接 a.id(+)=b.id ===> b表内容全部显示，以右表的表为基准。

right join: right join 右边的表全部显示，以右边的表为基准

select * from emp e right join dept d on e.deptno=d.deptno;

right join ,left join 没有 where from 语句等。

注：+号在=号左边叫右连接，+号在=右边叫做左连接。

自然连接：natural join

自然连接，natural join 会根据列明，自动创建连接，省略where语句，避免笛卡尔积的出现

eg: select empno,ename,sal,deptno,loc from emp natural join dept;

using :表示与指定的列相关联。

eg: select e.ename,e.sal,deptno,d.loc from emp e join dept d using (deptno) where deptno=20;

注：被using 子句所引用的列，在sql 语句中的任何地方不能使用表名或者别名作为前缀。

PS :做外连接的时候，where 条件中不可以加从表的条件，需将从表做个子查询，做成另外一个表。

eg :select t.acctype, nvl(b.name,t.acctype) as name
      from biacciccardmaptb t, dictcodesettb b
      where b.category = '账户类型'
        and t.acctype=b.code(+)
        and t.iccardno = '1000751090001385'   this is wrong

should :

select t.acctype, nvl(b.name,t.acctype) as name
from biacciccardmaptb t, (select * from dictcodesettb where category = '账户类型' )b
where
t.acctype=b.code(+)
and t.iccardno = '1000751090001385'

http://bqsongning.blog.163.com/blog/static/115471473200932284753219/