Sql Join Corss Join 的根本性分类与关系代数

引申

数据库中很多操作来自关系代数中的一些概念。例如常见的JOIN操作，下面是关系代数中的一些概念。

https://en.wikipedia.org/wiki/Relational_algebra

JOIN本身也分好多种比如EquiJoin , SemiJoin , AntiJoin , Cross Join,Division。

这里的join 并不特指某个关系型数据库里的join关键词，而是泛指数据集的连接操作，具体到rdbms里面 会有 inner join(join) left join right jion full join 实现方式会有

nested loop 、hash join 、merge join

而现在的大数据平台里面 hive sql和 spark sql 也会有inner join(join) left join right jion full join 这些语法关键词

 

正文

 

EQUI JOIN

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

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

用例子最容易说明其中区别：

1 2 3 4 5 6 7 8 9 10

-- 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 ”在拉丁文中是“半”的意思。这种连接方式是只连接目标表的一部分。这是什么意思呢？再想一下上面关于作者和书名的连接。我们想象一下这样的情况：我们不需要作者 / 书名这样的组合，只是需要那些在书名表中的书的作者信息。那我们就能这么写：

1 2 3 4 5 6 7

-- 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 语句写成这样：

1 2 3 4

-- 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 ）需要数据库重复从硬盘中读取数据到内存中。

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

 

ANTI JOIN

这种连接的关系跟 SEMI JOIN 刚好相反。在 IN 或者 EXISTS 前加一个 NOT 关键字就能使用这种连接。举个例子来说，我们列出书名表里没有书的作者：

1 2 3 4 5 6 7

-- 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。

 

CROSS JOIN

这个连接过程就是两个连接的表的乘积：即将第一张表的每一条数据分别对应第二张表的每条数据。我们之前见过，这就是逗号在 FROM 语句中的用法。在实际的应用中，很少有地方能用到 CROSS JOIN，

并且尽量避免，有的时候因为一些嵌套子查询会造成意向不到的笛卡尔积，这就是大家熟知的笛卡尔积，对于开发来说可以说是洪水猛兽，极地的执行效率，错误的执行结果.

，你就可以用这样的 SQL语句表达：

1 2	-- Combine every author with every book author CROSS JOIN book