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  where  e.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 JOIN departments  d
             ON  e.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 为外连接任意加入条件