SQL 语句中不同的连接操作

cmkzjw

　　本文摘自  www.open-open.com   详细链接为：http://www.open-open.com/solution/view/1389339225820
　　SQL 语句中，表连接的方式从根本上分为五种：

• 　　EQUI JOIN
  
• 　　SEMI JOIN
  
• 　　ANTI JOIN
  
• 　　CROSS JOIN
  
• 　　DIVISION
  

　　EQUI JOIN
　　这是一种最普通的 JOIN 操作，它包含两种连接方式：

• 　　INNER JOIN（或者是 JOIN ）
  
• 　　OUTER JOIN（包括： LEFT 、 RIGHT、 FULL OUTER JOIN）
  

　　用例子最容易说明其中区别：
-- This table reference contains authors and their books.　　
-- There is one record for each book and its author.
　　
-- authors without books are NOT included
　　
author JOIN book ON author.id = book.author_id
　　

　　
-- This table reference contains authors and their books
　　
-- There is one record for each book and its author.
　　
-- ... OR there is an "empty" record for authors without books
　　
-- ("empty" meaning that all book columns are NULL)
　　
author LEFT OUTER JOIN book ON author.id = book.author_id
　　SEMI JOIN
　　这种连接关系在 SQL 中有两种表现方式：使用 IN，或者使用 EXISTS。“ SEMI ”在拉丁文中是“半”的意思。这种连接方式是只连接目标表的一部分。这是什么意思呢？再想一下上面关于作者和书名的连接。我们想象一下这样的情况：我们不需要作者 / 书名这样的组合，只是需要那些在书名表中的书的作者信息。那我们就能这么写：
-- Using IN　　
FROM author
　　
WHERE author.id IN (SELECT book.author_id FROM book)
　　

　　
-- Using EXISTS
　　
FROM author
　　
WHERE EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)
　　尽管没有严格的规定说明你何时应该使用 IN ，何时应该使用 EXISTS ，但是这些事情你还是应该知道的：

• 　　IN比 EXISTS 的可读性更好
  

• 　　EXISTS 比IN 的表达性更好（更适合复杂的语句）
  

• 　　二者之间性能没有差异（但对于某些数据库来说性能差异会非常大）
  

　　因为使用 INNER JOIN 也能得到书名表中书所对应的作者信息，所以很多初学者机会认为可以通过 DISTINCT 进行去重，然后将 SEMI JOIN 语句写成这样：
-- Find only those authors who also have books　　
SELECT DISTINCT first_name, last_name
　　
FROM author
　　
JOIN book ON author.id = book.author_id
　　这是一种很糟糕的写法，原因如下：

• 　　SQL 语句性能低下：因为去重操作（ DISTINCT ）需要数据库重复从硬盘中读取数据到内存中。（译者注： DISTINCT 的确是一种很耗费资源的操作，但是每种数据库对于 DISTINCT 的操作方式可能不同）。
  

• 　　这么写并非完全正确：尽管也许现在这么写不会出现问题，但是随着 SQL 语句变得越来越复杂，你想要去重得到正确的结果就变得十分困难。
  

　　更多的关于滥用 DISTINCT 的危害可以参考这篇博文
　　（http://blog.jooq.org/2013/07/30/10-common-mistakes-java-developers-make-when-writing-sql/）。
　　ANTI JOIN
　　这种连接的关系跟 SEMI JOIN 刚好相反。在 IN 或者 EXISTS 前加一个 NOT 关键字就能使用这种连接。举个例子来说，我们列出书名表里没有书的作者：
-- Using IN　　
FROM author
　　
WHERE author.id NOT IN (SELECT book.author_id FROM book)
　　

　　
-- Using EXISTS
　　
FROM author
　　
WHERE NOT EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)
　　关于性能、可读性、表达性等特性也完全可以参考 SEMI JOIN。
　　这篇博文介绍了在使用 NOT IN 时遇到 NULL 应该怎么办，因为有一点背离本篇主题，就不详细介绍，有兴趣的同学可以读一下
　　（http://blog.jooq.org/2012/01/27/sql-incompatibilities-not-in-and-null-values/）。
　　CROSS JOIN
　　这个连接过程就是两个连接的表的乘积：即将第一张表的每一条数据分别对应第二张表的每条数据。我们之前见过，这就是逗号在 FROM 语句中的用法。在实际的应用中，很少有地方能用到 CROSS JOIN，但是一旦用上了，你就可以用这样的 SQL语句表达：
-- Combine every author with every book　　
author CROSS JOIN book
　　DIVISION
　　DIVISION 的确是一个怪胎。简而言之，如果 JOIN 是一个乘法运算，那么 DIVISION 就是 JOIN 的逆过程。DIVISION 的关系很难用 SQL 表达出来，介于这是一个新手指南，解释 DIVISION 已经超出了我们的目的。但是有兴趣的同学还是可以来看看这三篇文章
　　（http://blog.jooq.org/2012/03/30/advanced-sql-relational-division-in-jooq/）
　　（http://en.wikipedia.org/wiki/Relational_algebra#Division）
　　（https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/）。
推荐阅读 →_→ 《画图解释SQL联合语句》
　　我们学到了什么？
　　学到了很多！让我们在脑海中再回想一下。 SQL 是对表的引用， JOIN 则是一种引用表的复杂方式。但是 SQL 语言的表达方式和实际我们所需要的逻辑关系之间是有区别的，并非所有的逻辑关系都能找到对应的 JOIN 操作，所以这就要我们在平时多积累和学习关系逻辑，这样你就能在以后编写 SQL 语句中选择适当的 JOIN 操作了。