Oracle SQL99 外连接的写法区别

1: cartesian 笛卡尔积
返回的结果集合：  
一般按照From后表的顺序，先从t1种抽取所有的记录和t2的第一条记录结合
                      然后从t1种抽取所有的记录和t2的第二条记录结合
                      然后从t1种抽取所有的记录和t2的第...条记录结合
SQL99：
SELECT t1.department_id, t2.employee_id
  FROM t_department t1 cross join t_employee t2;  
关键字：  cross join

oracle:
SELECT t1.department_id, t2.employee_id
  FROM t_department t1, t_employee t2;


2:Jion(inner join)
2.1 EquiJoin :等值连接
Sql99:
SELECT * FROM t_department;
DEPARTMENT_ID LOCATION_ID DEPARTMENT_NAME
------------- ----------- --------------------------------------------------------------------------------
D_1           L_1         D_N
D_2           L_2         D_N2
SQL> SELECT * FROM t_employee;

EMPLOYEE_ID DEPARTMENT_ID EMPLOYEE_NAME
----------- ------------- --------------------------------------------------------------------------------
E_3         D_4           E_N4
E_1         D_1           E_N1
E_2         D_3           E_N2
SQL> SELECT t1.department_id, t2.employee_id
  2          FROM t_department t1
  3          JOIN t_employee t2 ON t1.department_id = t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1           E_1

Oracle：
SELECT t1.department_id, t2.employee_id
  2          FROM t_department t1, t_employee t2
  3   WHERE t1.department_id = t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1           E_1

2.2：non_equiJoin:非等值连接
也是做笛卡尔积,找出符合连接条件的
SQL99：
SELECT t1.department_id, t2.employee_id
  2          FROM t_department t1
  3          JOIN t_employee t2 ON t1.department_id <> t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1           E_3
D_2           E_3
D_2           E_1
D_1           E_2
D_2           E_2

Oracle:
SELECT t1.department_id, t2.employee_id
  2          FROM t_department t1, t_employee t2
  3   WHERE t1.department_id <> t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1           E_3
D_2           E_3
D_2           E_1
D_1           E_2
D_2           E_2

2.3 outer join

2.3.1 左外关联
员工表增加记录
EMPLOYEE_ID DEPARTMENT_ID EMPLOYEE_NAME
        E_4              D_1                   E_N8

找出部门的所有员工，如果部门没有员工，则员工信息为NULL
SQL99：
SELECT t1.department_id, t2.employee_id
  2          FROM t_department t1
  3          LEFT OUTER JOIN t_employee t2 ON t1.department_id = t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1           E_4
D_1           E_1
D_2           null(不会显示)

Oracle: 等号在(+)的左边
SELECT t1.department_id, t2.employee_id
  2          FROM t_department t1, t_employee t2
  3   WHERE t1.department_id = t2.department_id(+);

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1           E_4
D_1           E_1
D_2           null(不会显示)       

2.3.2 右外关联
找出全体员工的部门信息,如果员工部门不存在要求显示员工的信息,部门则为NULL
SQL99：
SELECT t1.department_id, t2.employee_id
  2          FROM t_department t1
  3   RIGHT OUTER JOIN t_employee t2 ON t1.department_id = t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1           E_1
D_1           E_4
              E_2
              E_3
Oracle:
SELECT t1.department_id, t2.employee_id
  2          FROM t_department t1, t_employee t2
  3   WHERE t1.department_id(+) = t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1           E_4
D_1           E_1
              E_2
              E_3


2.3.3 全外关联
找出所有员工和所有部门的部门员工信息,如果对应的信息不存在则以NULL显示.
SQL 99:
SELECT t1.department_id, t2.employee_id
  2          FROM t_department t1
  3          FULL OUTER JOIN t_employee t2 ON t1.department_id = t2.department_id;

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1           E_4
D_1           E_1
D_2           
              E_2
              E_3

Oracle:
  不存在
  

3 其它情况
3.1. 同名字段Join
同名字段自动等值Join
SELECT  department_id, t2.employee_id -- 同名字段不能写成 “表名.字段名”
        FROM t_department t1 NATURAL
        JOIN t_employee t2;
DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1           E_1

分别在t_department，t_employee增加“TEST”字段，然后在t_department中的值赋为“2”，在t_employee中赋“1”
SELECT  department_id,  test, t2.employee_id
  2          FROM t_department t1 NATURAL
  3          JOIN t_employee t2;

DEPARTMENT_ID TEST EMPLOYEE_ID
------------- ---- -----------
没有结果集合

3.2 多个同名字段时，指定同名字段Join
SELECT department_id, t1.test, t2.employee_id
  2          FROM t_department t1
  3          JOIN t_employee t2
  4   USING (department_id);

DEPARTMENT_ID TEST EMPLOYEE_ID
------------- ---- -----------
D_1           2    E_4
D_1           2    E_1

SELECT t1.department_id, test, t2.employee_id
  2          FROM t_department t1
  3          JOIN t_employee t2
  4   USING (test);

DEPARTMENT_ID TEST EMPLOYEE_ID
------------- ---- -----------
没有结果集合

4. seft join(从词的意思可以看出是自己和自己做连接操作)
没有想出好例子.

5. 增加过滤条件
SQL99
SELECT department_id, t2.employee_id
  2          FROM t_department t1
  3          JOIN t_employee t2
  4   USING (department_id)
  5   WHERE t2.employee_id >= 'E_0';

DEPARTMENT_ID EMPLOYEE_ID
------------- -----------
D_1           E_4
D_1           E_1

Oracle：
在后面加上 And 条件即可。

6. 多表Join
求出三个表中有效的依赖关系的数据
SELECT t1.department_id, t2.employee_id, t3.location_id
  2          FROM t_department t1
  3          JOIN t_employee t2 ON t1.department_id = t2.department_id
  4          JOIN t_Location t3 ON t1.location_id = t3.location_id;

DEPARTMENT_ID EMPLOYEE_ID LOCATION_ID
------------- ----------- -----------
D_1           E_4         L_1
D_1           E_1         L_1