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