JOIN 连接操作介绍

SQL中的连接JOIN是指将来自两个或多个表、视图或物化视图的行组合在一起的查询；当查询的FROM子句中出现多个表时，数据库就会执行一个连接。 查询的选择列列表可以从这些表中选择任何列，如果其中任意两个表存在同名的列，则必须使用表名限定整个查询中对这些列的引用以避免歧义。

大多数连接查询至少包含一个连接条件，要么在FROM子句中关键字ON后面，要么在WHERE子句中；连接条件比较来自不同表的两个列，多个条件可以用AND或OR连接起来，最终都是返回一个bool值；连接条件中的列不需要也出现在选择列表中。包含连接条件的WHERE子句还可以包含仅引用一个表的列的其他过滤条件，用于进一步限制连接查询返回的行。

SQL查询的基本过程

  • 单表查询：根据WHERE子句中的条件过滤FROM子句中指定表中的记录，形成中间表；如果有GROUP BY、HAVING、WINDOW、ORDER BY、LIMIT等子句则按顺序进一步做相应处理，最后根据SELECT子句中的列与表达式做相应处理后返回最终结果
  • 两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接类型进行过滤形成中间表；然后再根据WHERE条件过滤中间表的记录，最后根据SELECT指定的列返回查询结果
  • 多表连接查询：先对第一个和第二个表按照两表做连接，然后用查询结果和第三个表做连接，以此类推，直到所有的表都连接上为止，形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，最后根据SELECT指定的列返回查询结果

JOIN中ON和WHERE两种条件的区别

连接条件可以出现在ON关键字或者WHERE子句中，但是我们要特别注意 ON条件和WHERE条件生效时机是不一样的，在大数据量情况下消耗的资源可能会存在很大的差异：

  • ON条件：做为过滤两个连接表的笛卡尔积形成中间表的约束条件，生成的中间表已经是过滤后的数据
  • WHERE条件：在有ON条件的两表或多表连接中，是过滤中间表的约束条件，中间表先生成出来再做过滤

此外，INNER JOIN中两种条件的结果是相同的，但是用LEFT JOIN 时（RIGHT JOIN或FULL JOIN类似），因为无论ON的条件是否满足都会返回左表的所有记录，因此下面两个语句是不等价的：

  1. SELECT * FROM tab1 LEFT JOIN tab2 ON (tab1.size = tab2.size) WHERE tab2.name='AAA';
  2. SELECT * FROM tab1 LEFT JOIN tab2 ON (tab1.size = tab2.size AND tab2.name='AAA');

前面我们提到只要是返回bool值表达式都可以做为JOIN的连接条件，因此有人会在ON条件上直接写上形如 column1 != 100 的过滤条件，推荐用法是ON条件只进行连接操作，WHERE则用于过滤中间表的记录。

常见JOIN连接方式

通常我们说的Join的连接方式或者说连接类型有5种，最常用的是前面2~3种：

  • 内连接 INNER JOIN
  • 左外连接 LEFT OUTER JOIN
  • 右外连接 RIGHT OUTER JOIN
  • 全外连接 FULL OUTER JOIN
  • 交叉连接 CROSS JOIN

内连接 INNER JOIN

image.png
内连接INNER JOIN 通常简写为 JOIN，只返回两个表中连接字段相等的行。SQL举例：

  1. SELECT * 
  2. FROM table1 JOIN table2
  3.     ON table1.column_name1 = table2.column_name2;

上面的内连接SQL还有一种等价的写法，有人称为多表联合查询：

  1. SELECT * 
  2. FROM table1, table2
  3. WHERE table1.column_name1 = table2.column_name2;

左外连接 LEFT OUTER JOIN

image.png
左外连接 LEFT OUTER JOIN 通常简写为 LEFT JOIN，返回左表所有记录及右表中连接字段相等的记录；以左表为准右表做匹配，匹配多个则返回多个，如右表中没有匹配，相关输出列置NULL。SQL举例：

  1. SELECT *
  2. FROM table1 LEFT JOIN table2
  3.     ON table1.column_name1 = table2.column_name2;

右外连接 RIGHT OUTER JOIN

image.png
右外连接 RIGHT OUTER JOIN 通常简写为 RIGHT JOIN，返回右表所有记录及左表中连接字段相等的记录；以右表为准左表做匹配，匹配多个则返回多个，如左表中没有匹配，相关输出列置NULL。SQL举例：

  1. SELECT *
  2. FROM table1
  3. RIGHT JOIN table2
  4.     ON table1.column_name1 = table2.column_name2;

全外连接 FULL OUTER JOIN

image.png
全外连接 FULL OUTER JOIN通常简写为 FULL JOIN，返回包括左表和右表中的所有记录，当某行在另一个表中没有匹配行时，则另一个表的输出列置NULL。如果表之间有匹配行，则整个结果集行包含基表的数据值；SQL举例：

  1. SELECT *
  2. FROM table1 FULL JOIN table2
  3. ON table1.column_name1 = table2.column_name2;

交叉连接 CROSS JOIN

交叉连接 CROSS JOIN ，返回左表中的所有行，且左表中的每一行与右表中的所有行组合；交叉连接也称作两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积；CROSS JOIN 不带ON关键字，其和 INNER JOIN ON (TRUE) 等效；两个表连接查询单没有指定连接条件时，就会产生交叉连接。 SQL举例：

  1. SELECT *
  2. FROM table1 CROSS JOIN table2;

常见JOIN连接相关概念

自连接 SELF JOIN

自连接 SELF JOIN 是指一个表自己连接自己，通常需要取两个不同的表别名；例如一个支持层级的分类表category有3个字段：id、name、parent_id，想要同时查询出分类ID、分类名、父分类ID和父分类名就可以用自连接：

  1. SELECT c.id, c.name, pc.id AS parent_id, pc.name AS parent_name
  2. FROM category AS c LEFT JOIN category AS pc 
  3.     ON c.parent_id = pc.id

自然连接 NATURAL JOIN

自然连接 NATURAL JOIN 是指在两张表中寻找列名相同的字段，然后自动地将他们连接起来，且对于列名相同的连接列只会返回其中一列；使用自然连接就不能灵活指定连接条件。例如下面两个表中存在两个相同的字段type和status，则下面两个语句等价：

  1. SELECT * FROM table1 NATURAL JOIN table2;
  2. SELECT * FROM table1 JOIN table2 ON table1.type = table2.type AND table1.status = table1.status;

半连接 SEMI JOIN

半连接 SEMI JOIN 是指在两表关联时，当第二个表中存在一个或多个匹配记录时，返回第一个表的记录。与普通JOIN不同，SEMI JOIN中第一个表里的记录最多只返回一次。SEMI JOIN 通常无法直接用SQL语句来表示，而是由 IN 或 EXISTS 子查询转换得到。SQL举例：

  1. SELECT * FROM employees WHERE dept_name IN (
  2.    SELECT dept_name FROM departments
  3. )
  4. SELECT * FROM employees WHERE EXISTS (
  5.   SELECT * FROM departments WHERE employees.dept_name = departments.dept_name
  6. )

反连接 ANTI JOIN

反连接 ANTI JOIN 与半连接 SEMI JOIN 相反，是指在两表关联时，当第二个表中不存在匹配记录时，返回第一个表的记录。ANTI JOIN 通常无法直接用SQL语句来表示，而是由 NOT IN 或 NOT EXISTS 子查询转换得到。SQL举例：

  1. SELECT * FROM employees WHERE dept_name NOT IN (
  2.    SELECT dept_name FROM departments
  3. )
  4. SELECT * FROM employees WHERE NOT EXISTS (
  5.   SELECT * FROM departments WHERE employees.dept_name = departments.dept_name
  6. )

显式连接与隐式连接

我们在SQL查询语句中显示指定JOIN关键字的连接通常称之为显示连接，而不显示指定JOIN关键字的多表查询则可以称之为隐式连接；例如，我们在内连接INNER JOIN 里提到的FROM关键字后面指定多个表的写法就是隐式连接，EXIST/NOT EXIST/IN/NOT IN 相关的用法也是隐式连接。

等值连接与非等值连接

我们在前面的例子中关键字ON后面的连接条件都是形如 column1 = column2 这样的单个等值判断条件，实际上也可以是形如 column1 > column2 或 column1 != column2 甚至是 column1 < 1000 这样的一个或多个条件组成的表达式。

连接条件都是两个表中列的相等判断组成的表达式通常称为等值连接，否则就是非等值连接；等值连接是最常用的，因为从业务使用上来说通常都是将一个表的主键在另外一个表里冗余存储（可以声明为外键也可以不声明）以表示两个表中数据记录的相关性。而非等值连接通常容易返回不符合业务相关性的、太多的记录。

参考资料