Join
操
作
Join操作基本分为3大类:外连接
(细分为:左连接、右连接、全连接
)、自然连接、内连接
Join操作的共性:第一步均为将所有参与操作的表进行了一个笛卡儿积,然后才依据各连接条件进行记录的筛选
SQL> select * from employees;
LAST_NAME
DEPARTMENT_ID SALARY
------------------------------
------------------------ ------------
Getz
10 3000
Davis
20 1500
King
20 2200
Davis
30 5000
Kochhar
5000
SQL> select * from departments;
DEPARTMENT_ID
DEPARTMENT_NAME
------------------------
------------------------------
10 Sales
20 Marketing
30 Accounts
40 Administration
--------------------------------------------------
Left outer join--------------------------------------------------
SQL> select * from employees e left outer join departments d
2 on e.department_id=d.department_id;
LAST_NAME DEPARTMENT_ID SALARY DEPARTMENT_ID DEPARTMENT_NAME
----------------- ------------------------ ----------- ----------------------- ------------------------------
Getz
10 3000 10 Sales
King
20 2200 20 Marketing
Davis
20 1500 20 Marketing
Davis
30 5000 30 Accounts
Kochhar
5000
附加:Oracle9i以前版本中左连接的写法如下:
SQL> select * from employees e ,departments d
2 on e.department_id(+)=d.department_id;
------------------------------------------------
-Right outer join-------------------------------------------------
SQL> select * from employees right outer join departments
2 using(department_id);
DEPARTMENT_ID
LAST_NAME SALARY DEPARTMENT_NAME
-----------------------
----------------- ---------- ------------------------------
10 Getz 3000 Sales
20
Davis 1500 Marketing
20
King 2200 Marketing
30 Davis 5000 Accounts
40 Administration
附加:Oracle9i以前版本中左连接的写法如下:
SQL> select * from employees e ,departments d
2 where e.department_id=d.department_id(+);
--------------------------------------------------
Full outer join--------------------------------------------------
SQL> select * from employees full outer join departments
2 using(department_id);
DEPARTMENT_ID LAST_NAME
SALARY DEPARTMENT_NAME
------------------------ -------------------
---------- -----------------------------
10 Getz 3000 Sales
20
King 2200 Marketing
20
Davis 1500 Marketing
30 Davis 5000 Accounts
Kochhar
5000
40
Administration
说明:[1]外连接必须使用on或using子句提供相应的连接条件
[2]不能为using子句中所列举的列指定表别名,即使在group by和select子句中也是如此
[3]外连接规则:左连右补,右连左补,全连左右合并
如:对表departments表进行右连接时,在两表完成笛卡儿积后再依据连接条件using(department_id)来筛选两表中department_id值相同的记录,但对DEPARTMENT_ID=40时employees表中没有与之匹配的记录,按常理此DEPARTMENT_ID=40所对应的记录将被抛弃,但就是为了保全连接表(departments表)中的所有记录就必须在employees表中虚拟出一条与之匹配的记录来保全连接表的所有记录,当然这条虚拟的记录显示时值全为null
--------------------------------------------------
Natural join--------------------------------------------------
SQL> select * from employees natural join departments;
DEPARTMENT_ID
LAST_NAME SALARY DEPARTMENT_NAME
------------------------
------------------ ---------- ------------------------------
10 Getz 3000 Sales
20
Davis 1500 Marketing
20
King 2200 Marketing
30 Davis 5000 Accounts
说明:自然连接是通过对参与表关系中所有同名的属性对取等(即相等比较)来完成的,故无须自己添加连接条件与外连接的区别在于对于无法匹配的记录外连接会虚拟一条与之匹配的记录来保全连接表中的所有记录,但自然连接不会
--------------------------------------------------
Inner join--------------------------------------------------
SQL> select * from employees inner join departments
2 using(department_id);
DEPARTMENT_ID
LAST_NAME SALARY DEPARTMENT_NAME
------------------------
----------------- ---------- ------------------------------
10 Getz 3000 Sales
20 Davis
1500 Marketing
20
King 2200 Marketing
30 Davis 5000 Accounts
说明:内连接与自然连接基本相同,不同之处在于自然连接只能是同名属性的等值连接,而内连接可以使用using或on子句来指定连接条件