看了网上一些例子,自己改了改,并做了实验,希望能把这个东西说明白。
先创建表,并插入数据:
-- 部门表 CREATE TABLE departments ( depID NUMBER(38,0), depName VARCHAR2(20), delFlag NUMBER(1,0) ); -- 员工表 CREATE TABLE employees ( empID NUMBER(38,0), empName VARCHAR2(20), depID NUMBER(38,0), delFlag NUMBER(1,0) ); delete from employees delete from departments INSERT INTO departments VALUES(1,'Finacle',0); INSERT INTO departments VALUES(2,'Marketing',0); INSERT INTO departments VALUES(3,'HR',1); INSERT INTO departments VALUES(4,'IT',0); INSERT INTO employees VALUES(001,'wbq',1,0); INSERT INTO employees VALUES(002,'czh',2,0); INSERT INTO employees VALUES(003,'chh',1,0); INSERT INTO employees VALUES(004,'wal',2,0); INSERT INTO employees VALUES(005,'ddd',3,0); INSERT INTO employees VALUES(006,'ddd',5,0); INSERT INTO employees VALUES(007,'ddd',6,0); COMMIT;
看看左外连接:
--列出部门ID为3的部门和员工信息,不管该部门是否有员工 /*如果员工表作为做外链接的左边,那么所有数据必须是员工表里 存在的数据,即先到员工表查数据,拿到这些数据再到右边的部门表 一条一条查找,有就显示,没有就null*/ -- 测试1 SELECT d.depID,d.depName,e.empid,e.empName FROM departments d LEFT OUTER JOIN employees e ON d.depID = e.depID ORDER BY d.depID; -- 测试2 --和上面等效 SELECT d.depID,d.depName,e.empid,e.empName FROM departments d, employees e WHERE d.depID = e.depID(+) ORDER BY d.depID; -------------------------------------------------
数据结果集如下:
DEPID |
DEPNAME |
EMPID |
EMPNAME |
---|---|---|---|
1 |
Finacle |
1 |
wbq |
1 |
Finacle |
3 |
chh |
2 |
Marketing |
4 |
wal |
2 |
Marketing |
2 |
czh |
3 |
HR |
5 |
ddd |
4 |
IT |
|
|
看看右外连接:
------------------------------------------------- /*如果部门表作为做外链接的左边,那么所有数据必须是部门表里 存在的数据,即先到部门表查数据,拿到这些数据再到右边的员工表 一条一条查找,有就显示,没有就null*/ -- 测试3 SELECT d.depID,d.depName,e.empid,e.empName FROM employees e LEFT OUTER JOIN departments d ON e.depID=d.depID ORDER BY d.depID; --和上面等效 -- 测试4 SELECT d.depID,d.depName,e.empid,e.empName FROM employees e,departments d WHERE e.depID=d.depID(+) ORDER BY d.depID; /* 总结:左外连接就是把左边的数据作为查询条件, 一条一条去右边查询,如果有就显示,没有就为null。*/
结果集:
DEPID |
DEPNAME |
EMPID |
EMPNAME |
---|---|---|---|
1 |
Finacle |
3 |
chh |
1 |
Finacle |
1 |
wbq |
2 |
Marketing |
4 |
wal |
2 |
Marketing |
2 |
czh |
3 |
HR |
5 |
ddd |
|
|
6 |
ddd |
|
|
7 |
ddd |
------------------------------------------------------------- /*如果你学会了做外链接,你就会右外连接,就是把两个表的顺序 换一换而已,所以没必要练习右外链接,练习了反而会觉得乱, 想用的话,把顺序放对用左外连接就行了。 以下是右外链接测试*/ -- 测试1 的左外连接 SELECT d.depID,d.depName,e.empid,e.empName FROM departments d LEFT OUTER JOIN employees e ON d.depID = e.depID ORDER BY d.depID;
DEPID |
DEPNAME |
EMPID |
EMPNAME |
---|---|---|---|
1 |
Finacle |
1 |
wbq |
1 |
Finacle |
3 |
chh |
2 |
Marketing |
4 |
wal |
2 |
Marketing |
2 |
czh |
3 |
HR |
5 |
ddd |
4 |
IT |
|
|
-- 测试1 的右外连接 SELECT d.depID,d.depName,e.empid,e.empName FROM departments d RIGHT OUTER JOIN employees e ON d.depID = e.depID ORDER BY d.depID;
DEPID |
DEPNAME |
EMPID |
EMPNAME |
---|---|---|---|
1 |
Finacle |
3 |
chh |
1 |
Finacle |
1 |
wbq |
2 |
Marketing |
4 |
wal |
2 |
Marketing |
2 |
czh |
3 |
HR |
5 |
ddd |
|
|
6 |
ddd |
|
|
7 |
ddd |
-- 测试1 的右外连接 等价于下面的sql(注意下面是左外连接,可见左外连接和右外连接可以转化) SELECT d.depID,d.depName,e.empid,e.empName FROM employees e LEFT OUTER JOIN departments d ON d.depID = e.depID ORDER BY d.depID;
DEPID |
DEPNAME |
EMPID |
EMPNAME |
---|---|---|---|
1 |
Finacle |
3 |
chh |
1 |
Finacle |
1 |
wbq |
2 |
Marketing |
4 |
wal |
2 |
Marketing |
2 |
czh |
3 |
HR |
5 |
ddd |
|
|
6 |
ddd |
|
|
7 |
ddd |
--如果使用(+),那(+)在哪边,那边就是被连接的表, --它的数据是可选的(有就显示,没有就null,条件是没有(+)的表每一条作为 --查询条件)
--FULL OUTER JOIN:全外关联 SELECT d.depID,d.depName,e.empid,e.empName FROM departments d FULL OUTER JOIN employees e ON d.depID = e.depID ORDER BY d.depID;
DEPID |
DEPNAME |
EMPID |
EMPNAME |
---|---|---|---|
1 |
Finacle |
3 |
chh |
1 |
Finacle |
1 |
wbq |
2 |
Marketing |
2 |
czh |
2 |
Marketing |
4 |
wal |
3 |
HR |
5 |
ddd |
4 |
IT |
|
|
|
|
6 |
ddd |
|
|
7 |
ddd |
看看结果集就很清楚了