本文摘自  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 操作了。