Sql Certificate 简明教程

SQL - Get Data from Multiple Tables

Displaying Data from Multiple Tables

大型数据库的相关表通过外键和主键（通常称为公共列）连接在一起。连接表的功能使您能够为产生的结果表增加更多含义。要在一个查询中连接“n”个表，至少需要 (n-1) 个连接条件。根据连接条件，Oracle 将匹配的行对组合在一起，并显示满足连接条件的行。

连接分类如下：

自然连接（也称为等值连接或简单连接） - 使用一个名称和定义相同的列创建连接。
非等值连接 - 在要连接的表中没有相等行时连接表 - 例如，将一个表中一列的值与另一个表中一系列值进行匹配。
自连接 - 将表自身连接到自身。
外部连接 - 在其他表中没有匹配记录时，将表的记录包含在输出中。
笛卡尔连接（也称为笛卡尔积或交叉连接） - 将第一个表中的每一行都复制到第二个表中的每一行。通过显示每个可能的记录组合来创建表之间的连接。

Natural Join

NATURAL 关键字可以简化等值连接的语法。每当两个（或更多）表具有名称相同的列，并且这些列是连接兼容的（即这些列具有一个共享的值域）时，就可以进行自然连接。连接操作连接具有相同名称列的相等列值的表的行。

考虑DEPARTMENTS 和 EMPLOYEES 表之间的一对多关系。每个表都有一列名为 DEPARTMENT_ID。此列是 DEPARTMENTS 表的主键，EMPLOYEE 表的外键。

SELECT E.first_name NAME,D.department_name DNAME
FROM employees E NATURAL JOIN departments D;

FIRST_NAME DNAME
---------- ------
MILLER     DEPT 1
JOHN       DEPT 1
MARTIN     DEPT 2
EDWIN      DEPT 2

下面的 SELECT 查询通过使用 ON 关键字明确指定连接条件，将两张表连接起来。

SELECT E.first_name NAME,D.department_name DNAME
FROM employees E JOIN departments D
ON (E.department_id = D.department_id);

关于 NATURAL JOIN 有一些限制。你不能使用 NATURAL JOIN 指定 LOB 列。此外，参与连接的列不能通过表名或别名限定。

USING Clause

使用 Natural 连接，Oracle 会隐式识别列以形成连接的基础。许多情况需要显式声明连接条件。在这种情况下，我们使用 USING 子句指定连接条件。由于 USING 子句基于列的相等性连接表，因此也称为等值连接。它们也称为内部连接或简单连接。

Syntax:

SELECT <column list>
FROM   TABLE1   JOIN   TABLE2
USING (column name)

考虑下面的 SELECT 查询，EMPLOYEES 表和 DEPARTMENTS 表使用公用列 DEPARTMENT_ID 进行连接。

SELECT E.first_name NAME,D.department_name DNAME
FROM employees E JOIN departments D
USING (department_id);

Self Join

当关系存在于存储在单个表中的行之间时，SELF-JOIN 操作会生成一个结果表。换句话说，当一张表与其自身连接时，连接称为自连接。

考虑包含员工及其汇报经理的 EMPLOYEES 表。要为一名员工找到经理姓名，需要对 EMP 表本身进行连接。这是自连接的一个典型候选。

SELECT e1.FirstName Manager,e2.FirstName Employee
FROM employees e1 JOIN employees e2
ON (e1.employee_id = e2.manager_id)
ORDER BY e2.manager_id DESC;

Non Equijoins

当相关列无法用相等号连接（意味着要连接的表中没有等效行）时，将使用不等号连接。不等号连接使你能够将某个记录的一列中存储一个范围的最小值，并将最大值存储在另一列中。因此，你可以使用不等号连接判断正在发货的项目是否处于列中的最小值和最大值范围，而不是查找列到列的匹配。如果连接确实为该项目找到匹配的范围，则可以在结果中返回相应的运费。与等值连接的传统方法一样，可以在 WHERE 子句中执行不等号连接。此外，JOIN 关键字可与 ON 子句一起使用，以指定连接的相关列。

SELECT E.first_name,
            J.job_hisal,
            J.job_losal,
            E.salary
     FROM employees E JOIN job_sal J
     ON (E.salary BETWEEN J.job_losal AND J.job_losal);

我们可以利用前面讨论的所有比较参数，例如相等和不等操作符、BETWEEN、IS NULL、IS NOT NULL 和 RELATIONAL。

Outer Joins

外部连接用于识别以下情况：即使两张表相关，一张表中的行也不匹配另一张表中的行。

外部连接有三种类型：左外部连接、右外部连接和完全外部连接。它们都以内部连接开头，然后添加回一些已被删除的行。左外部连接添加回连接条件中从第一个（左）表删除的所有行，并且第二个（右）表中的输出列设置为 NULL。右外部连接添加回连接条件中从第二个（右）表删除的所有行，并且第一个（左）表中的输出列设置为 NULL。完全外部连接添加回从两张表中删除的所有行。

Right Outer Join

右外部连接添加回连接条件中从第二个（右）表删除的所有行，并且第一个（左）表中的输出列设置为 NULL。请注意，下面的查询列出了员工及其相应部门。此外，没有员工被分配到部门 30。

SELECT E.first_name, E.salary, D.department_id
FROM employees E, departments D
WHERE E.DEPARTMENT_ID (+) = D.DEPARTMENT_ID;

FIRST_NAME SALARY     DEPARTMENT_ID
---------- ---------- ----------
JOHN       6000       10
EDWIN      2000       20
MILLER     2500       10
MARTIN     4000       20
                      30

Left Outer Join

左外部连接添加回连接条件中从第一个（左）表删除的所有行，并且第二个（右）表中的输出列设置为 NULL。通过交换 (+) 符号的位置，可以用上面演示的查询来演示左外部连接。

SELECT E.first_name, E.salary, D.department_id
FROM employees E, departments D
WHERE   D.DEPARTMENT_ID = E.DEPARTMENT_ID (+);

FIRST_NAME SALARY     DEPARTMENT_ID
---------- ---------- ----------
JOHN       6000       10
EDWIN      2000       20
MILLER     2500       10
MARTIN     4000       20
                      30

Full Outer Join

完全外部连接添加回从两张表中删除的所有行。下面的查询列出了员工及其部门。请注意，员工“MAN”到目前为止尚未被分配任何部门（为 NULL），并且未将部门 30 分配给任何员工。

SELECT  nvl (e.first_name,'-') first_name, nvl (to_char (d.department_id),'-') department_id
FROM employee e FULL OUTER JOIN department d
ON e. depARTMENT_ID = d. depARTMENT_ID;

FIRST_NAME DEPARTMENT_ID
---------- --------------------
MAN        -
JOHN       10
EDWIN      20
MILLER     10
MARTIN     20
-          30

6 rows selected.

Cartesian product or Cross join

对于两个实体 A 和 B，A * B 称为笛卡尔积。笛卡尔积包含每个表中行的所有可能组合。因此，当具有 10 行的表与具有 20 行的表连接时，笛卡尔积为 200 行（10 * 20 = 200）。例如，将具有八行的员工表与具有三行的部门表连接，将生成 24 行的笛卡尔积表（8 * 3 = 24）。

交叉连接是指两张表的笛卡尔积。它生成两张表的交叉积。可以使用 CROSS JOIN 子句编写上面的查询。

笛卡尔积结果表通常不是很有用的。事实上，这样的结果表可能极具误导性。如果你对 EMPLOYEES 和 DEPARTMENTS 表执行下面的查询，结果表意味着每个员工都与每个部门有关系，而我们知道事实并非如此！

SELECT E.first_name, D.DNAME
FROM employees E,departments D;

SELECT E.first_name, D.DNAME
FROM employees E CROSS JOIN departments D;