查询可以同时访问多个表,或访问同一表时同时处理该表中的多行。同时访问同一或不同表中的多行的查询称为联接查询。

例如,假设您希望列出所有员工记录以及相关部门的名称和地址。为此,我们需要将 emp 表中每行的 deptno 列与 dept 表中所有行的 deptno 列进行比较,然后选择这些值匹配的行对。这将通过以下查询实现:

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM emp, dept WHERE emp.deptno = dept.deptno;

 ename  |   sal   | deptno |   dname    |   loc
--------+---------+--------+------------+----------
 MILLER | 1300.00 |     10 | ACCOUNTING | NEW YORK
 CLARK  | 2450.00 |     10 | ACCOUNTING | NEW YORK
 KING   | 5000.00 |     10 | ACCOUNTING | NEW YORK
 SCOTT  | 3000.00 |     20 | RESEARCH   | DALLAS
 JONES  | 2975.00 |     20 | RESEARCH   | DALLAS
 SMITH  |  800.00 |     20 | RESEARCH   | DALLAS
 ADAMS  | 1100.00 |     20 | RESEARCH   | DALLAS
 FORD   | 3000.00 |     20 | RESEARCH   | DALLAS
 WARD   | 1250.00 |     30 | SALES      | CHICAGO
 TURNER | 1500.00 |     30 | SALES      | CHICAGO
 ALLEN  | 1600.00 |     30 | SALES      | CHICAGO
 BLAKE  | 2850.00 |     30 | SALES      | CHICAGO
 MARTIN | 1250.00 |     30 | SALES      | CHICAGO
 JAMES  |  950.00 |     30 | SALES      | CHICAGO
(14 rows)

观察有关结果集的两件事情:

  • 没有部门 40 的结果行。这是因为 emp 表中没有部门 40 的匹配条目,因此联接忽略了 dept 表中不匹配的行。稍后,我们将介绍如何修复此问题。
  • 列出按表名限定的输出列更可取,而不是使用 * 或省略资格,如下所示:
    SELECT ename, sal, dept.deptno, dname, loc FROM emp, dept WHERE emp.deptno = dept.deptno;

由于所有列具有不同的名称(deptno 除外,因此必须限定它),分析程序将自动查找它们属于哪个表,但最好在联接查询中完全限定列名称。

到目前为止,上面所示的那种联接查询也可以编写为以下替代形式:

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;

此语法不像上述语法那样常用,但我们在此处显示它是为了帮助您理解以下主题。

您将注意到,在所有上述联接结果中,没有返回属于部门 40 的员工,因此,从未显示部门 40 的记录。现在我们将了解如何可以在结果中获得部门 40 记录,尽管没有匹配的员工。我们希望查询扫描 dept 表中的每行来找到匹配的 emp 行。如果没有找到匹配的行,我们希望使用一些“空”值来替代 emp 表的列。这种查询称为外部联接。(到目前为止,我们看到的联接是内部联接。)此命令如下所示:

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM dept LEFT OUTER JOIN emp ON emp.deptno = dept.deptno;

 ename  |   sal   | deptno |   dname    |   loc
--------+---------+--------+------------+----------
 MILLER | 1300.00 |     10 | ACCOUNTING | NEW YORK
 CLARK  | 2450.00 |     10 | ACCOUNTING | NEW YORK
 KING   | 5000.00 |     10 | ACCOUNTING | NEW YORK
 SCOTT  | 3000.00 |     20 | RESEARCH   | DALLAS
 JONES  | 2975.00 |     20 | RESEARCH   | DALLAS
 SMITH  |  800.00 |     20 | RESEARCH   | DALLAS
 ADAMS  | 1100.00 |     20 | RESEARCH   | DALLAS
 FORD   | 3000.00 |     20 | RESEARCH   | DALLAS
 WARD   | 1250.00 |     30 | SALES      | CHICAGO
 TURNER | 1500.00 |     30 | SALES      | CHICAGO
 ALLEN  | 1600.00 |     30 | SALES      | CHICAGO
 BLAKE  | 2850.00 |     30 | SALES      | CHICAGO
 MARTIN | 1250.00 |     30 | SALES      | CHICAGO
 JAMES  |  950.00 |     30 | SALES      | CHICAGO
        |         |     40 | OPERATIONS | BOSTON
(15 rows)

此查询称为左外部联接,因为联接运算符左侧提及的表将在输出中具有其每一行至少一次,而右侧的表将只具有与左侧表的某行匹配的那些行输出。当选择的左侧表行没有右侧表匹配时,将使用NULL替代右侧表列。

外部联接的替代语法是在 WHERE 子句中的联接条件中使用外部联接运算符“(+)”。对于应使用 null 值替代其不匹配的行的表,将外部联接运算符放在该表的列名称之后。因此,对于 dept 表中在 emp 表中没有匹配行的所有行,PolarDB-O将为包含 emp 的列的任何选择列表表达式返回 null。因此,上述示例可重新编写为:

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM dept, emp WHERE emp.deptno(+) = dept.deptno;

 ename  |   sal   | deptno |   dname    |   loc
--------+---------+--------+------------+----------
 MILLER | 1300.00 |     10 | ACCOUNTING | NEW YORK
 CLARK  | 2450.00 |     10 | ACCOUNTING | NEW YORK
 KING   | 5000.00 |     10 | ACCOUNTING | NEW YORK
 SCOTT  | 3000.00 |     20 | RESEARCH   | DALLAS
 JONES  | 2975.00 |     20 | RESEARCH   | DALLAS
 SMITH  |  800.00 |     20 | RESEARCH   | DALLAS
 ADAMS  | 1100.00 |     20 | RESEARCH   | DALLAS
 FORD   | 3000.00 |     20 | RESEARCH   | DALLAS
 WARD   | 1250.00 |     30 | SALES      | CHICAGO
 TURNER | 1500.00 |     30 | SALES      | CHICAGO
 ALLEN  | 1600.00 |     30 | SALES      | CHICAGO
 BLAKE  | 2850.00 |     30 | SALES      | CHICAGO
 MARTIN | 1250.00 |     30 | SALES      | CHICAGO
 JAMES  |  950.00 |     30 | SALES      | CHICAGO
        |         |     40 | OPERATIONS | BOSTON
(15 rows)

我们还可以使表与自身联接。这称为自联接。例如,假设我们希望查找每个员工的姓名以及该员工的经理的姓名。因此我们需要将每个 emp 行的 mgr 列与所有其他 emp 行的 empno 列进行比较。

SELECT e1.ename || ' works for ' || e2.ename AS "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno;

 Employees and their Managers
------------------------------
 FORD works for JONES
 SCOTT works for JONES
 WARD works for BLAKE
 TURNER works for BLAKE
 MARTIN works for BLAKE
 JAMES works for BLAKE
 ALLEN works for BLAKE
 MILLER works for CLARK
 ADAMS works for SCOTT
 CLARK works for KING
 BLAKE works for KING
 JONES works for KING
 SMITH works for FORD
(13 rows)

在这里,emp 表已重新标记为 e1 以在选择列表和联接条件中表示员工行,还重新标记为 e2 以在选择列表和联接条件中表示担任经理的匹配的员工行。这些类型的别名可用于其他查询以减少输入,例如:

SELECT e.ename, e.mgr, d.deptno, d.dname, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno;

 ename  | mgr  | deptno |   dname    |   loc
--------+------+--------+------------+----------
 MILLER | 7782 |     10 | ACCOUNTING | NEW YORK
 CLARK  | 7839 |     10 | ACCOUNTING | NEW YORK
 KING   |      |     10 | ACCOUNTING | NEW YORK
 SCOTT  | 7566 |     20 | RESEARCH   | DALLAS
 JONES  | 7839 |     20 | RESEARCH   | DALLAS
 SMITH  | 7902 |     20 | RESEARCH   | DALLAS
 ADAMS  | 7788 |     20 | RESEARCH   | DALLAS
 FORD   | 7566 |     20 | RESEARCH   | DALLAS
 WARD   | 7698 |     30 | SALES      | CHICAGO
 TURNER | 7698 |     30 | SALES      | CHICAGO
 ALLEN  | 7698 |     30 | SALES      | CHICAGO
 BLAKE  | 7839 |     30 | SALES      | CHICAGO
 MARTIN | 7698 |     30 | SALES      | CHICAGO
 JAMES  | 7698 |     30 | SALES      | CHICAGO
(14 rows)

将相当频繁地遇到这种样式的缩写。