1: cartesian 笛卡尔积
返回的结果集合：
一般按照From后表的顺序，先从t1种抽取所有的记录和t2的第一条记录结合
然后从t1种抽取所有的记录和t2的第二条记录结合
然后从t1种抽取所有的记录和t2的第...条记录结合
SQL99：
SELECT t1.department_id, t2.employee_id
FROM t_department t1 cross joint_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 FROMt_department t1
3 JOINt_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 FROMt_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 FROMt_department t1
3 JOINt_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 FROMt_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 FROMt_department t1
3 LEFT OUTERJOIN 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 FROMt_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 FROMt_department t1
3 RIGHT OUTER JOIN t_employee t2 ONt1.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 FROMt_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 FROMt_department t1
3 FULL OUTERJOIN 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 FROMt_department t1 NATURAL
3 JOINt_employee t2;
DEPARTMENT_ID TEST EMPLOYEE_ID
------------- ---- -----------
没有结果集合
3.2 多个同名字段时，指定同名字段Join
SELECT department_id, t1.test, t2.employee_id
2 FROMt_department t1
3 JOINt_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 FROMt_department t1
3 JOINt_employee t2
4 USING (test);
DEPARTMENT_ID TEST EMPLOYEE_ID
------------- ---- -----------
没有结果集合
4. seft join(从词的意思可以看出是自己和自己做连接操作)
没有想出好例子.
5. 增加过滤条件
SQL99
SELECT department_id, t2.employee_id
2 FROMt_department t1
3 JOINt_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 FROMt_department t1
3 JOINt_employee t2 ON t1.department_id = t2.department_id
4 JOINt_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
