联接与集合操作

联接和集合操作

联接查询

联接查询是在两张表(或更多表)中进行行匹配的操作，一般称之为水平操作，这是因为对几张表进行联接操作所产生的结果集可以包含这几张表中所有的列，对应于联接的水平操作，一般将集合操作视为垂直操作。
MySQL数据库支持如下的联接查询：

• CROSS JOIN(交叉联接)
• INNER JOIN(内联接)
• OUTER JOIN(外联接)

新旧查询语法

MySQL数据库支持两种不同的联接操作语法。

#第一种
select ...
from a,b
where a.x = b.x
#第二种
select ...
from a 
inner join b
on a.x = b.x

CROSS JOIN

cross join对两个表执行笛卡尔积，返回两个表中所有列的组合。若左表有m行数据，右表有n行数据，则cross join将返回m*n行的表。

INNER JOIN

通过inner join用户可以根据一些过滤条件来匹配表之间的数据。inner join不会添加外部行，指定过滤条件在on子句和where子句中是没有任何区别的。inner关键字可以省略。如果on子句中的列具有相同的名称，可以使用USING子句来进行简化，得到的结果和上述两语法结果是一样的。

OUTER JOIN

通过OUTER JOIN 用户可以按照一些过滤条件来匹配表之间的数据。与inner join不同的是，在通过outer join添加的保留表中存在未找到的匹配数据。MySQL数据库支持left outer join和right outer join。与inner关键字一样，可以省略outer关键字。对于outer join同样可以使用using 来简化on子句。
与inner join不同的是，对于outer join，必须制定on子句，否则MySQL数据库会抛出异常。

NATURAL JOIN

ANSI SQL 还支持NATURAL JOIN，即自然联接。NATURAL JOIN等同于INNER JOIN 与 USING的组合，它隐含的作用是将两个表中具有相同名称的列进行匹配。

STRAIGHT_JOIN

STRAIGHT_JOIN等同于JOIN，通过STRAIGHT_JOIN，MySQL数据库会强制先读取左边的表。

其他联接分类

SELF JOIN

self join是同一个表的两个实例之间的join操作。

select min(a.a+1)
from t x
left outer join t y
on x.a + 1 = y.a
where y.a is null

显然是表t自己对自己进行join操作。再次提醒，对同一个表进行联接操作必须制定表的别名。

NONEQUI JOIN

EQUAL JOIN(等值联接)，即联接条件是基于“等于”运算符的联接操作。NONEQUI JOIN的联接条件包含“等于”运算符之外的运算符。

SEMI JOIN和ANTI SEMI JOIN

SEMI JOIN是根据一个表中存在的相关记录找到另一个表中相关数据的联接。如果从左表返回记录，该联接被称为左表半联接，如果从右表返回记录，该联接被称为右半联接。
实现SEMI JOIN的方法有多种，如内部联接、子查询、集合操作等。在使用内部联接方式时，只从一个表中选择记录，然后应用distinct。
与SEMI JOIN相反的是ANTI SEMI JOIN，它根据一个表中不存在的记录从另一个表中返回记录。使用OUTER JOIN 并过滤外部行，可以实现ANTI SEMI JOIN。

多表联接

多表联接是查询涉及三张或者更多张表之间的连接查询操作。对于多表查询可以随意安排表的顺序，而不会影响查询的结果。但可以通过前面的STRAIGHT_JOIN来强制联接处理的顺序。

联接算法

目前MySQL数据库仅支持Nested-Loops Join算法，而MariaDB还支持Classic Hash Join算法。 Nested-Loops Join有两种，Simple Nested-Loops Join(NLJ)算法，Block Nested-Loops Join(BNL)算法。
Simple Nested-Loops Join从第一张表中每次读取一条记录，然后将记录与嵌套表中的记录进行比较。
Block Nested-Loops Join先把对外部表每次读取的10行记录放入join buffer，然后在内部表中直接匹配这10行数据。

Batched Key Access Join算法

结合索引和group这两种方法来提高search-for-match的操作。

Classic Hash Join算法

集合操作

集合操作一般被看成是垂直操作。MySQL数据库支持两种集合操作，UNION ALL 和 UNION DISTINCT。
集合操作与一般select的区别：

• 只有最后一个select可以应用into outfile，但是整个集合的操作将被输出到文件中。
• 不能在select 语句中使用high_priority关键字。

UNION DISTINCT 和 UNION ALL

UNION DISTINCT 组合两个输入，并应用distinct过滤重复项。一般省略distinct关键字，直接用union。
UNION ALL组合两个输入中所有项的结果集，并包含重复的选项。

EXCEPT

MySQL数据库并不原生支持EXCEPT的语法，但可以通过其他一些手段来实现。EXPECT集合操作允许用户找出位于第一个输入中但不位于第二个输入中的行数据。同UNION一样，EXCEPT可分为EXCEPT DISTINCT和EXCEPT ALL。
EXCEPT DISTINCT返回位于第一个输入中但不位于第二个输入中的不重复行。

INTERSECT

INTERSECT返回在两个输入中都出现的行，同样MySQL也不原生支持。