oracle之多表查询

从多个表中获取数据
oracle之多表查询
笛卡尔集
笛卡尔集会在下面条件下产生:
省略连接条件
连接条件无效
所有表中的所有行互相连接

为了避免笛卡尔集, 可以在 WHERE 加入有效的连接条件。
oracle之多表查询
Oracle 连接
使用连接在多个表中查询数据。

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column1 = table2.column2;

在 WHERE 子句中写入连接条件。
在表中有相同列时,在列名之前加上表名前缀

等值连接
oracle之多表查询

SQL> SELECT employees.employee_id, employees.last_name,
  2         employees.department_id, departments.department_id,
  3         departments.location_id
  4  FROM   employees, departments
  5  WHERE  employees.department_id = departments.department_id;

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        100 King                                 90            90        1700
        101 Kochhar                              90            90        1700
        102 De Haan                              90            90        1700
        103 Hunold                               60            60        1400
        104 Ernst                                60            60        1400
        107 Lorentz                              60            60        1400
        124 Mourgos                              50            50        1500
        141 Rajs                                 50            50        1500
        142 Davies                               50            50        1500
        143 Matos                                50            50        1500
        144 Vargas                               50            50        1500
        149 Zlotkey                              80            80        2500
        174 Abel                                 80            80        2500

多个连接条件与 AND 操作符

区分重复的列名
使用表名前缀在多个表中区分相同的列。

在不同表中具有相同列名的列可以用表的别名加以区分。

使用别名可以简化查询。
使用表名前缀可以提高执行效率。

SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;

连接 n个表,至少需要 n-1个连接条件。 例如:连接三个表,至少需要两个连接条件。
oracle之多表查询
非等值连接
oracle之多表查询

SQL> SELECT e.last_name, e.salary, j.grade_level
  2  FROM   employees e, job_grades j
  3  WHERE  e.salary
  4         BETWEEN j.lowest_sal AND j.highest_sal;

LAST_NAME                     SALARY GRADE_LEVEL
------------------------- ---------- -----------
Olson                        2100.00 A
Markle                       2200.00 A
Philtanker                   2200.00 A
Gee                          2400.00 A
Landry                       2400.00 A
Marlow                       2500.00 A
Patel                        2500.00 A
Perkins                      2500.00 A
Colmenares                   2500.00 A
Sullivan                     2500.00 A
Vargas                       2500.00 A
Grant                        2600.00 A

外连接
oracle之多表查询
内连接和外连接
内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL). 外连接的 WHERE 子句条件类似于内部连接, 但连接条件中没有匹配行的表的列后面要加外连接运算符, 即用圆括号括起来的加号(+).

外连接语法
使用外连接可以查询不满足连接条件的数据。
外连接的符号是 (+)。
右外连接

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column(+) = table2.column;

左外连接

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column = table2.column(+);

外连接

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 ;

LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------- ------------------------------
King                                 90 Executive
Kochhar                              90 Executive
De Haan                              90 Executive
Hunold                               60 IT
Ernst                                60 IT
Lorentz                              60 IT

自连接
oracle之多表查询```c
SELECT worker.last_name || ’ works for ’ || manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id
WORKER.LAST_NAME||‘WORKSFOR’||MANAGER.LAST_NAME

Cambrault works for King
Errazuriz works for King
Partners works for King
Russell works for King
Vollman works for King
Kaufling works for King
Fripp works for King
Weiss works for King


使用SQL: 1999 语法连接(使用连接从多个表中查询数据:)
```c
SELECT	table1.column, table2.column
FROM	table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2 
  ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2 
  ON (table1.column_name = table2.column_name)];

叉 集(了解)
使用CROSS JOIN 子句使连接的表产生叉集。
叉集和笛卡尔集是相同的。

SELECT last_name, department_name
FROM   employees
CROSS JOIN departments ;

自然连接
NATURAL JOIN 子句,会以两个表中具有相同名字的列为条件创建等值连接。
在表中查询满足等值条件的数据。
如果只是列名相同而数据类型不同,则会产生错误。

返回的是,两个表中具有相同名字的列的“且、交集”,而非“或,并集”。即:比如employee类和department类都有department_id和manager_id,返回二者都相同的结果。

自然连接

SQL> SELECT department_id, department_name,
  2         location_id, city
  3  FROM   departments
  4  NATURAL JOIN locations ;

DEPARTMENT_ID DEPARTMENT_NAME                LOCATION_ID CITY
------------- ------------------------------ ----------- ------------------------------
           10 Administration                        1700 Seattle
           20 Marketing                             1800 Toronto
           30 Purchasing                            1700 Seattle
           40 Human Resources                       2400 London
           50 Shipping                              1500 South San Francisco
           60 IT                                    1400 Southlake
           70 Public Relations                      2700 Munich
           80 Sales                                 2500 Oxford
           90 Executive                             1700 Seattle

使用 USING 子句创建连接

在NATURAL JOIN 子句创建等值连接时,可以使用 USING 子句指定等值连接中需要用到的列。
使用 USING 可以在有多个列满足条件时进行选择。
不要给选中的列中加上表名前缀或别名。
JOIN 和 USING 子句经常同时使用。



select last_name,department_name  
from employees
natural  join departments ; 
select  last_name,department_name 
from employees 
join departments using (department_id);

USING 子句

SQL> SELECT e.employee_id, e.last_name, d.location_id
  2  FROM   employees e JOIN departments d
  3  USING (department_id) ;

EMPLOYEE_ID LAST_NAME                 LOCATION_ID
----------- ------------------------- -----------
        100 King                             1700
        101 Kochhar                          1700
        102 De Haan                          1700
        103 Hunold                           1400
        104 Ernst                            1400
        107 Lorentz                          1400
        124 Mourgos                          1500
        141 Rajs                             1500

使用ON 子句创建连接(常用)
自然连接中是以具有相同名字的列为连接条件的。
可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
ON 子句使语句具有更高的易读性。

SQL> SELECT e.employee_id, e.last_name, e.department_id,
  2         d.department_id, d.location_id
  3  FROM   employees e JOIN departments d
  4  ON     (e.department_id = d.department_id);

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        100 King                                 90            90        1700
        101 Kochhar                              90            90        1700
        102 De Haan                              90            90        1700
        103 Hunold                               60            60        1400
        104 Ernst                                60            60        1400
        107 Lorentz                              60            60        1400
        124 Mourgos                              50            50        1500
        141 Rajs                                 50            50        1500
        142 Davies                               50            50        1500

使用 ON 子句创建多表连接

SQL> SELECT employee_id, city, department_name
  2  FROM   employees e
  3  JOIN   departments d
  4  ON     d.department_id = e.department_id
  5  JOIN   locations l
  6  ON     d.location_id = l.location_id;

EMPLOYEE_ID CITY                           DEPARTMENT_NAME
----------- ------------------------------ ------------------------------
        100 Seattle                        Executive
        101 Seattle                        Executive
        102 Seattle                        Executive
        103 Southlake                      IT
        104 Southlake                      IT
        107 Southlake                      IT

内连接和外连接
在SQL: 1999中,内连接只返回满足连接条件的数据
两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行,这种连接称为左(或右) 外连接。
两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ,这种连接称为满 外连接。

左外连接

SQL> SELECT e.last_name, e.department_id, d.department_name
  2  FROM   employees e
  3  LEFT OUTER JOIN departments d
  4  ON   (e.department_id = d.department_id) ;

LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------- ------------------------------
Whalen                               10 Administration
Fay                                  20 Marketing
Hartstein                            20 Marketing
Colmenares                           30 Purchasing
Himuro                               30 Purchasing
Tobias                               30 Purchasing
Grant                                   

右外连接

SQL> SELECT e.last_name, e.department_id, d.department_name
  2  FROM   employees e
  3  RIGHT OUTER JOIN departments d
  4  ON    (e.department_id = d.department_id) ;

LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------- ------------------------------
King                                 90 Executive
Kochhar                              90 Executive
De Haan                              90 Executive
Hunold                               60 IT
Ernst                                60 IT
Lorentz                              60 IT
Mourgos                              50 Shipping
Rajs                                 50 Shipping
Davies                               50 Shipping
	                                 50 Shipping

满外连接

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
FULL OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;


LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------- ------------------------------
King                                 90 Executive
 	                                 10 Administratio
Whalen                                
上一篇:SpringBoot项目实战-准备工作


下一篇:连接查询经典案例