【转】oracle连接查询详解

转自：http://blog.csdn.net/indexman/article/details/7768811

连接查询是数据库查询语句中使用频率很高的查询方式，下面根据Oracle提供的官方文档学习一下连接查询：

一 ??Equijoins 等值连接：

? ? ?等值连接是条件连接在连接运算符为“=”号时的特例。（相对于非等值连接，如：between...and条件）[sql]?view plaincopy?

1. SQL>?select?e.employee_id,?e.last_name,??
2. ??2???????????????????e.department_id,?d.department_id,??
3. ??3???????????????????d.location_id??
4. ??4??from???employees?e,?departments?d??
5. ??5??where??e.department_id?=?d.department_id;??
6. EMPLOYEE_ID?LAST_NAME??DEPARTMENT_ID?DEPARTMENT_ID?LOCATION_ID??
7. -----------?-------------------------?-------------????????-------------?????-----------??
8. ????????198??????????????OConnell??????????????????????????????50???????????????????????????50?????????1500??
9. ????????199??????????????Grant?????????????????????????????????????50????????????????????????????50?????????1500??
10. ????????200??????????????Whalen????????????????????????????????10????????????????????????????10??????????1700??
11. ????????201??????????????Hartstein??????????????????????????????20????????????????????????????20?????????1800??
12. ......??

?

?

二 ? ?Self?? joins ?自连接：

自连接(self join)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

e.g.

?

[sql] view plaincopy?

1. SELECT?e1.last_name||'?works?for?'||e2.last_name???
2. ???"Employees?and?Their?Managers"??
3. ???FROM?employees?e1,?employees?e2???
4. ???WHERE?e1.manager_id?=?e2.employee_id??
5. ??????AND?e1.last_name?LIKE?'R%'??
6. ???ORDER?BY?e1.last_name;??
7. ??
8. Employees?and?Their?Managers?????
9. -------------------------------??
10. Rajs?works?for?Mourgos??
11. Raphaely?works?for?King??
12. Rogers?works?for?Kaufling??
13. Russell?works?for?King??

三 ?Cartesian Products?笛卡尔积：

? ??If two tables in a join query have no join condition, then Oracle Database returns their?Cartesian product.

? ? 如果2个表连接查询而没有连接条件，则oracle返回他们的笛卡尔积。即oracle返回一个表里每一行与另一个表每一行的组合（15 X 4）。

e.g.

?

[sql]?view plaincopy?

1. SCOTT@orcl>?select?count(1)?from?emp;??
2. ??
3. ??COUNT(1)??
4. ----------??
5. ????????15??
6. SCOTT@orcl>?select?count(1)?from?dept;??
7. ??
8. ??COUNT(1)??
9. ----------??
10. ?????????4??
11. SCOTT@orcl>?select?count(1)?from?emp,?dept;??
12. ??
13. ??COUNT(1)??
14. ----------??
15. ????????60??

?

四 ?Inner Joins ?内连接：
??? ?An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.
? ? ?内连接也叫简单连接，是2个或更多表的关联并且仅返回那些满足连接条件的行。
e.g.

?

[html]?view plaincopy?

1. <p?style="font-weight:?bold;?font-size:?18px;"></p><pre?name="code"?class="sql"><span?style="font-size:12px;">select?e.employee_id,?e.last_name,??
2. ????????????????e.department_id,?d.department_id,??
3. ??????????????????d.location_id??
4. FROM???employees?e?JOIN?departments?d??
5. ?ON??e.department_id?=?d.department_id;</span></pre><pre?name="code"?class="html"></pre>??

?

?

五 ??Outer joins 外连接：

? 简单连接的扩展。分为左外连接（Left ?outer joins）、右外连接（Right ?outer joins）、全外连接（Full ?outer ?joins）

?和Partitioned Outer Joins（分区外连接，用于数据仓库）

Listing 1:

?

SELECT d.department_id, e.last_name

[sql] view plaincopy?

1. FROM?departments?d?LEFT?OUTER?JOIN?employees?e??
2. ON?d.department_id?=?e.department_id??
3. ORDER?BY?d.department_id,?e.last_name;??

另外一种写法：

?

[sql] view plaincopy?

1. SELECT?d.department_id,?e.last_name??
2. ???FROM?departments?d,?employees?e??
3. ???WHERE?d.department_id?=?e.department_id(+)??
4. ???ORDER?BY?d.department_id,?e.last_name;??

?

Oracle 官方推荐使用第一种写法。

Listing ?2:

?

[sql] view plaincopy?

1. SELECT?d.department_id,?e.last_name??
2. ???FROM?departments?d?RIGHT?OUTER?JOIN?employees?e??
3. ???ON?d.department_id?=?e.department_id??
4. ???ORDER?BY?d.department_id,?e.last_name;??
5. ??
6. DEPARTMENT_ID?LAST_NAME??
7. -------------?-------------------------??
8. .?.?.??
9. ??????????110?Higgins??
10. ??????????110?Gietz??
11. ??????????????Grant??
12. ??????????????Zeuss??

?

Listing ?3:?

?

[sql] view plaincopy?

1. SELECT?d.department_id?as?d_dept_id,?e.department_id?as?e_dept_id,??
2. ??????e.last_name??
3. ???FROM?departments?d?FULL?OUTER?JOIN?employees?e??
4. ???ON?d.department_id?=?e.department_id??
5. ???ORDER?BY?d.department_id,?e.last_name;??
6. ??
7. ?D_DEPT_ID??E_DEPT_ID?LAST_NAME??
8. ----------?----------?-------------------------??
9. ??.?.?.??
10. ???????110????????110?Gietz??
11. ???????110????????110?Higgins??
12. ??.?.?.??
13. ???????260??
14. ???????270??
15. ??????????????????999?Zeuss??
16. ??????????????????????Grant??

更好的写法，使用USING clause

?

?

[sql] view plaincopy?

1. SELECT?department_id?AS?d_e_dept_id,?e.last_name??
2. ???FROM?departments?d?FULL?OUTER?JOIN?employees?e??
3. ???USING?(department_id)??
4. ???ORDER?BY?department_id,?e.last_name;??
5. ??
6. D_E_DEPT_ID?LAST_NAME??
7. -----------?-------------------------??
8. ??.?.?.??
9. ????????110?Higgins??
10. ????????110?Gietz??
11. ??.?.?.??
12. ????????260??
13. ????????270??
14. ????????999?Zeuss??
15. ????????????Grant??

?

?

Listing ?4:

?

[sql] view plaincopy?

1. CREATE?TABLE?inventory?(time_id????DATE,??
2. ????????????????????????product????VARCHAR2(10),??
3. ????????????????????????quantity???NUMBER);??
4. ??
5. INSERT?INTO?inventory?VALUES?(TO_DATE('01/04/01',?'DD/MM/YY'),?'bottle',?10);??
6. INSERT?INTO?inventory?VALUES?(TO_DATE('06/04/01',?'DD/MM/YY'),?'bottle',?10);??
7. INSERT?INTO?inventory?VALUES?(TO_DATE('01/04/01',?'DD/MM/YY'),?'can',?10);??
8. INSERT?INTO?inventory?VALUES?(TO_DATE('04/04/01',?'DD/MM/YY'),?'can',?10);??
9. ??
10. SELECT?times.time_id,?product,?quantity?FROM?inventory???
11. ???PARTITION?BY??(product)???
12. ???RIGHT?OUTER?JOIN?times?ON?(times.time_id?=?inventory.time_id)???
13. ???WHERE?times.time_id?BETWEEN?TO_DATE('01/04/01',?'DD/MM/YY')???
14. ??????AND?TO_DATE('06/04/01',?'DD/MM/YY')???
15. ???ORDER?BY??2,1;???
16. ??
17. TIME_ID???PRODUCT??????QUANTITY??
18. ---------?----------?----------??
19. 01-APR-01?bottle?????????????10??
20. 02-APR-01?bottle??
21. 03-APR-01?bottle??
22. 04-APR-01?bottle??
23. 05-APR-01?bottle??
24. 06-APR-01?bottle?????????????10??
25. 06-APR-01?bottle??????????????8??
26. 01-APR-01?can????????????????10??
27. 01-APR-01?can????????????????15??
28. 02-APR-01?can??
29. 03-APR-01?can??
30. 04-APR-01?can????????????????10??
31. 04-APR-01?can????????????????11??
32. 05-APR-01?can??
33. 06-APR-01?can??
34. ??
35. 15?rows?selected.??

?

?

更好的写法：

?

[sql] view plaincopy?

1. SELECT?time_id,?product,?LAST_VALUE(quantity?IGNORE?NULLS)???
2. ???OVER?(PARTITION?BY?product?ORDER?BY?time_id)?quantity???
3. ???FROM?(?SELECT?times.time_id,?product,?quantity???
4. ?????????????FROM?inventory?PARTITION?BY??(product)???
5. ????????????????RIGHT?OUTER?JOIN?times?ON?(times.time_id?=?inventory.time_id)???
6. ???WHERE?times.time_id?BETWEEN?TO_DATE('01/04/01',?'DD/MM/YY')???
7. ??????AND?TO_DATE('06/04/01',?'DD/MM/YY'))???
8. ???ORDER?BY??2,1;???
9. ??
10. TIME_ID???PRODUCT??????QUANTITY??
11. ---------?----------?----------??
12. 01-APR-01?bottle?????????????10??
13. 02-APR-01?bottle?????????????10??
14. 03-APR-01?bottle?????????????10??
15. 04-APR-01?bottle?????????????10??
16. 05-APR-01?bottle?????????????10??
17. 06-APR-01?bottle??????????????8??
18. 06-APR-01?bottle??????????????8??
19. 01-APR-01?can????????????????15??
20. 01-APR-01?can????????????????15??
21. 02-APR-01?can????????????????15??
22. 03-APR-01?can????????????????15??
23. 04-APR-01?can????????????????11??
24. 04-APR-01?can????????????????11??
25. 05-APR-01?can????????????????11??
26. 06-APR-01?can????????????????11??
27. ??
28. 15?rows?selected.??

?

?

?六???Anti-joins 反连接：

??返回不满足子查询条件（NOT IN）的左表记录。

e.g.

?

[sql] view plaincopy?

1. SELECT?*?FROM?employees???
2. ???WHERE?department_id?NOT?IN???
3. ???(SELECT?department_id?FROM?departments???
4. ???????WHERE?location_id?=?1700)??
5. ???ORDER?BY?last_name;??

?

??七?? Semi-joins 半连接：

? ?返回匹配exists子查询的左侧没有重复的行。

e.g.

?

[sql] view plaincopy?

1. SELECT?*?FROM?departments???
2. ???WHERE?EXISTS???
3. ???(SELECT?*?FROM?employees???
4. ???????WHERE?departments.department_id?=?employees.department_id???
5. ???????AND?employees.salary?>?2500)??
6. ???ORDER?BY?department_name;??