YiDaoJ's Blog

Join in SQL / SQL中的连接（交叉连接，内连接，外连接）

Posted on | In |

SQL中的连接可分为交叉连接，内连接和外连接，其中又可以细化：

  • 交叉连接 (CROSS JOIN)
  • 内连接 (INNER JOIN)
    • 等值连接 (EQUI JOIN)
    • 不等值连接 (THETA JOIN)
    • 自然连接 (NATURAL JOIN)
  • 外连接 (OUTER JOIN)
    • 左外连接 (LEFT JOIN)
    • 右外连接 (RIGHT JOIN)
    • 全外连接 (FULL JOIN)

用Oracle数据库中的emp, dept和salgrade三张表为例，对以上各种连接进行解释和演示。

三张表结构如下：

emp表：

emp表

​ 上图来源：http://nuijten.blogspot.de/2013/07/apex-tree-based-on-multiple-tables.html

dept表：

DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

salgrade表：

GRADE LOSAL HISAL
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

1. 交叉连接 (CROSS JOIN)

交叉连接不需要任何关联条件，它返回的是两个集合的笛卡尔积，也就是表一中每一行与表二中所有行的组合。交叉连接返回数据的行数为两表行数的乘积，所以交叉连接又叫笛卡尔连接(cartesian join)或叉乘(Product)，它是所有类型的内连接的基础。

  • 语法：
1
SELECT * FROM table1, table2;
  • 范例：
1
SELECT * FROM emp, dept;

emp表中又14条记录，dept表中有4行记录，返回数据14*4 = 56行记录。（篇幅原因就不上图了。）

​

2. 内连接 (INNER JOIN)

内连接基于连接谓词将两张表(如 A 和 B)的列组合在一起，产生新的结果表。查询会将 A 表的每一行和 B 表的每一行进行比较，并找出满足连接谓词的组合。当连接谓词被满足，A 和 B 中匹配的行会按列组合(并排组合)成结果集中的一行。连接产生的结果集，可以定义为首先对两张表做笛卡尔积(交叉连接) – 将 A 中的每一行和 B 中的每一行组合，然后返回满足连接谓词的记录。实际上 SQL 产品会尽可能用其他方式去实现连接，笛卡尔积运算是非常没效率的。

内连接可以进一步被分为：相等连接，自然连接，和交叉连接。

via : 维基百科-连接（SQL）

2.1 相等连接 / 等值连接 (EQUI JOIN)

等值连接中，在关联条件中使用等号（=）运算符进行不同表和字段的连接，满足关联条件后会显示相应的数据。

  • 范例：查询员工的编号、姓名、部门编号、部门名称。

  • 分析：员工的编号、姓名、部门编号在emp表中，部门编号、部门名称在dept表中，部门编号为两表共有字段，关联条件即从这里入手。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    -- Syntax 1
    SELECT e.empno, e.ename, e.deptno, d.dname
    FROM emp e, dept d
    WHERE e.deptno = d.deptno;
    -- Syntax 2
    SELECT e.empno, e.ename, e.deptno, d.dname
    FROM emp e JOIN dept d
    ON e.deptno = d.deptno;

    执行结果：

​

  • 等值连接还有一种简易的语法规则，使用USING关键字；需要注意的是使用USING关键字时SELECT子句不能有限定词，否则会出现报错。方法如下：

    1
    2
    3
    SELECT *
    FROM emp e [INNER] JOIN dept d
    USING (deptno);

2.2 不等值连接 (THETA JOIN)

很好理解，即在关联条件中使用等号以外的运算符，来比较左右两个关联字段或者值。这些运算符有：>, <, >=, <=, !=, <>。

  • 范例：查询员工的编号、姓名、工作、工资及其工资等级。

  • 分析：此时我们需要emp表和salgrade表，员工的工资等级 –> 工资大于某等级的最低工资 并且小于该等级的最高工资。

    1
    2
    3
    4
    SELECT e.empno, e.ename, e.job, e.sal, s.grade
    FROM emp e, salgrade s
    WHERE e.sal > s.losal AND e.sal < s.hisal;
    -- 此处关联条件也可用BETWEEN AND

    执行结果：

2.3 自然连接(NATURAL JOIN)

自然连接是一种特殊的等值连接。在自然连接的语法中不需要出现关联条件的字段名，因为连接的两张表必须有相同的字段名（字段名和数据类型都相同）。使用自然连接所得的查询结果中，两表中名称相同的列只出现一次；不可使用ON关键字。

  • 语法：
1
2
SELECT Attribute  
FROM table1 NATURAL JOIN table2;
  • 将emp表和dept表自然连接进行查询：

由上图可以看出emp表和dept表中共有的字段DEPTNO只出现了一次，且位于第一列。

3. 外连接

外连接可以实现让等值判断左右两边因不满足查询条件而未显示的数据显示出来，分别是左外连接，右外连接和全外连接，它们的不同点是：

  • 左外连接还返回左表中不符合关联条件单符合查询条件的数据行。
  • 右外连接还返回右表中不符合关联条件单符合查询条件的数据行。
  • 全外连接其实是左外连接和右外连接查询结果的合集（每条记录只出现一词，并不会重复出现）。

在语法中OUTER关键字可以省略。

3.1 左外连接 (LEFT OUTER JOIN)

为了方便理解，我向emp表中添加了一条ename为HANS，empno为8000记录，其他字段数据均没有。现在emp表结构如下图所示：

  • 范例：查询所有工作人员的信息及其所在部门的名称。
  • 语法：
1
2
3
4
5
6
7
8
9
-- Syntax 1
SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno(+); -- 左外连接，+号在右表
-- Syntax 2
SELECT *
FROM emp LEFT JOIN dept
ON emp.deptno = dept.deptno; -- 使用的是ON关键字

查询结果：

HANS不属于任何部门，但是仍然显示。

3.2 右外连接 (RIGHT OUTER JOIN)

  • 范例：查询所有部门的信息，以及各部门所拥有的工作人员信息。
  • 语法：
1
2
3
4
5
6
7
8
9
-- Syntax 1
SELECT *
FROM emp RIGHT OUTER JOIN dept
ON emp.deptno = dept.deptno;
-- Syntax 2
SELECT *
FROM emp, dept
WHERE emp.deptno(+) = dept.deptno; -- 右外连接， +号在左表

查询结果：

从查询结果可以看出，40部门没有任何工作人员，但仍然显示。

3.3 全外连接 (FULL OUTER JOIN)

直接将emp表和dept表进行全外连接查询：

  • 语法：
1
2
3
SELECT *
FROM emp FULL OUTER JOIN dept
ON emp.deptno = dept.deptno;

查询结果：

没有部门的HANS和没有成员的40部门都显示出来了。

欢迎交流与指正。

注：此文章为本人原创，转载时烦请必注明出处。