SQL - 从多个表获取数据

上一页
下一页

从多个表显示数据

大型数据库的相关表通过使用外键和主键或通常称为公共列来链接。联接表的能力将使您能够为生成的输出表添加更多含义。对于在查询中联接的“n”个表，至少需要 (n-1) 个联接条件。根据联接条件，Oracle 将匹配的行对组合起来，并显示满足联接条件的行。

联接分类如下

  • 自然联接（也称为等值联接或简单联接） - 通过使用一个共同命名和定义的列来创建联接。

  • 非等值联接 - 当要联接的表中没有等效行时联接表 - 例如，将表中一列的值与另一表中的一系列值匹配。

  • 自联接 - 将表联接到自身。

  • 外联接 - 当另一个表中没有匹配记录时，将表的记录包含在输出中。

  • 笛卡尔积联接（也称为笛卡尔积或交叉联接） - 将第一个表中的每一行与第二个表中的每一行复制。通过显示所有可能的记录组合在表之间创建联接。

自然联接

NATURAL 关键字可以简化等值联接的语法。只要两个（或多个）表具有相同名称的列，并且这些列是联接兼容的，即这些列具有共享的值域，就可以进行 NATURAL JOIN。联接操作将具有相同命名列的相等列值的表的行联接起来。

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

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 子句

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

语法

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 操作会生成一个结果表。换句话说，当一个表联接到自身时，该联接称为自联接。

考虑 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;

非等值联接

当相关列无法使用等号联接时，使用非等值联接 - 意味着要联接的表中没有等效行。非等值联接使您能够在一个记录的一列中存储范围的最小值，并在另一列中存储最大值。因此，您可以使用非等值联接来确定要运送的项目是否位于列中的最小值和最大值之间，而不是查找列与列的匹配项。如果联接确实找到了项目的匹配范围，则可以在结果中返回相应的运费。与传统的等值联接方法一样，可以在 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。

外联接

外联接用于识别一个表中的行与第二个表中的行不匹配的情况，即使这两个表是相关的。

外联接有三种类型：LEFT、RIGHT 和 FULL OUTER JOIN。它们都以 INNER JOIN 开始，然后添加回一些已删除的行。LEFT OUTER JOIN 添加回联接条件中从第一个（左侧）表中删除的所有行，并且第二个（右侧）表中的输出列设置为 NULL。RIGHT OUTER JOIN 添加回联接条件中从第二个（右侧）表中删除的所有行，并且第一个（左侧）表中的输出列设置为 NULL。FULL OUTER JOIN 添加回两个表中删除的所有行。

右外联接

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.

笛卡尔积或交叉联接

对于两个实体 A 和 B，A * B 称为笛卡尔积。笛卡尔积包含每个表中所有行的所有可能组合。因此，当一个表有 10 行与一个表有 20 行联接时，笛卡尔积为 200 行 (10 * 20 = 200)。例如，将包含 8 行的员工表与包含 3 行的部门表联接将生成一个包含 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;
打印页面
上一页
下一页
广告