Oracle链接查询,Oracle连接查询介绍

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

1. 连接种类：

1)oracle特有连接种类：

a.Equijoin 等值连接

SQL> select e.employee_id, e.last_name,

2                   e.department_id, d.department_id,

3                   d.location_id

4  from   employees e, departments d

5  where  e.department_id = d.department_id;

EMPLOYEE_ID LAST_NAME  DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID

----------- ------------------------- -------------        -------------     -----------

198              OConnell                              50                           50         1500

199              Grant                                     50                            50         1500

200              Whalen                                10                            10          1700

201              Hartstein                              20                            20         1800

......

b. Non-Equijoin 非等值连接

SQL> select e.last_name, e.salary, j.grade

2  from   employees e, job_grades j

3  wheree.salary between j.lowest_sal and j.highest_sal;

LAST_NAME                     SALARY G

------------------------- ---------- -

Olson                           2100       A

Philtanker                    2200      A

Markle                          2200      A

...

Feeney                          3000 B

Cabrio                          3000 B

Fleaur                          3100 B

Walsh                           3100 B

...

Hartstein                      13000 C

Partners                       13500 C

Partners                       13500 D

Russell                        14000 D

De Haan                        17000 E

Kochhar                        17000 E

c. Outer join 外连接

SQL> select e.last_name, e.department_id, d.department_name

2  from   employees e, departments  d

3  where e.department_id(+) = d.department_id;

相当于:select e.last_name, e.department_id, d.department_name

from   employees e RIGHT JOINdepartments  d

ONe.department_id = d.department_id;

LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME

------------------------- ------------- ------------------------------

Greenberg                           100 Finance

Sciarra                             100 Finance

Urman                               100 Finance

Popp                                100 Finance

Faviet                              100 Finance

Gietz                               110 Accounting

Higgins                             110 Accounting

Treasury

Corporate Tax

Control And Credit

Shareholder Services

...............

注：此处(+)为连接符，放在等号左边代表右连接(相当于RIGHT JOIN)，放在等号右边代表左连接(相当于LEFT JOIN)。

此处为右连接，表示查找所有部门表ID和部门名称以及员工表中对应部门ID的员工姓名，没有的返回NULL。

d. Self   join  自连接

SQL> select worker.last_name || 'works for '|| manager.last_name

2  from employees worker, employees manager

3  where  worker.manager_id = manager.employee_id;

WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME

------------------------------------------------------------

OConnellworks for Mourgos

Grantworks for Mourgos

Whalenworks for Kochhar

Hartsteinworks for King

Fayworks for Hartstein

Mavrisworks for Kochhar

............

2)符合1999规范的连接：

Cross joins 交叉连接

Natural joins  自然连接

Using clause   使用条件

Full or two sided outer joins 完全连接

Arbitrary join conditions for outer joins 为外连接任意加入条件