1 集合运算

在数据库中, 所有的表以及查询结果都可以视为集合, 因此对它们进行一些集合运算。在标准SQL中, 可以使用求并集(UNION)、求交集(INTERSECT)、求差集(EXCEPT)运算。

1) 求并集(UNION)

UNION的具体用法:

1
2
3
4
5
SELECT product_id, product_name
  FROM product
 UNION
SELECT product_id, product_name
  FROM product2;

UNION等集合运算符通常都会除去重复的记录。上述查询是对不同的两张表进行求并集运算. 对于同一张表, 实际上也是可以进行求并集的。

使用UNION对两个查询结果取并集，与在一个查询中使用WHERE子句然后使用OR谓词连接两个查询条件，能够得到相同的结果。但要将两个不同的表中的结果合并在一起, 就不得不使用UNION了。而且，即便是对于同一张表, 有时也会出于查询效率方面的因素来使用UNION。

有些情况下需要不去重地进行并集, 那么只需要在UNION后面添加ALL关键字就可以保留重复行。

保留重复行的UNION ALL使用如下：

1
2
3
4
5
SELECT product_id, product_name
  FROM Product
 UNION ALL
SELECT product_id, product_name
  FROM Product2;

2) 求交集

MySQL 8.0不支持交运算INTERSECT，要用INNER JOIN来求得交集，方式如下：

1
2
3
4
SELECT p1.product_id, p1.product_name
  FROM Product p1
INNER JOIN Product2 p2
ON p1.product_id=p2.product_id

3) 求差集

MySQL 8.0还不支持求差集EXCEPT运算，可以使用NOT IN谓词实现表的减法。方式如下：

1
2
3
4
SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM Product2)

2 连结(JOIN)

使用上面所介绍的集合运算可以对记录（行）进行增加或者减少，对字段（列）没有影响。连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词=), 将其他表中的列添加过来, 进行“添加列”的集合运算。

在MySQL中主要有交叉连接、内连接和外连接。

1) 交叉连结（CROSS JOIN）

交叉连结（CROSS JOIN）一般用来返回连结表的笛卡尔积。

首先，笛卡尔积是指两个集合的乘积，可以写作A×B。例如，有 A 和 B 两个集合，它们的值如下：

A = {1,2}

B = {3,4,5}

那么集合A×B和B×A的结果集分别表示为：

1
2
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };

以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。

接着继续来看交叉连结的语法格式：

1
2
3
4
SELECT <字段名> 
FROM <表1> 
CROSS JOIN <表2> 
[WHERE子句]

交叉连结所得到的数据量是非常巨大的，根据表的数据量谨慎使用。

多个表交叉连结时，在FROM后连续使用CROSS JOIN。

2）内连结（INNER JOIN）

内连结（INNER JOIN）主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接。简单来说，就是利用条件表达式来消除交叉连接的某些数据行。

内连接使用INNER JOIN关键字连接两张表，并使用ON子句来设置连接条件。如果没有连接条件，INNER JOIN和CROSS JOIN在语法上是等同的，两者可以互换。

内连接的语法格式如下：

1
2
3
4
SELECT <字段名> 
FROM <表1> 
INNER JOIN <表2> 
[ON子句]

多个表内连接时，在FROM后连续使用INNER JOIN或JOIN即可。

内连接中可以省略INNER关键字，只用关键字JOIN。

简单例子：

1
2
3
4
SELECT SP.shop_id ,SP.shop_name,SP.product_id,P.product_name
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON SP.product_id = P.product_id;

3）外连接（OUTER JOIN）

内连接的查询结果都是符合连接条件的记录，而外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。外连接可以分为左外连接和右外连接。

a. 左外连接

左外连接又称为左连接，使用LEFT OUTER JOIN关键字连接两个表，并使用ON子句来设置连接条件。

左连接的语法格式如下：

1
2
3
4
SELECT <字段名>
 FROM <表1> 
 LEFT OUTER JOIN <表2> 
 ON子句

上述sql语句中，“表1”为基表，“表2”为参考表。左连接查询时，可以查询出“表1”中的所有记录和“表2”中匹配连接条件的记录。如果“表1”的某行在“表2”中没有匹配行，那么在返回结果中，“表2”的字段值均为空值（NULL）。

左连接中可以省略OUTER关键字，只使用关键字LEFT JOIN。

b. 右连接

右外连接又称为右连接，右连接是左连接的反向连接。使用RIGHT OUTER JOIN关键字连接两个表，并使用ON子句来设置连接条件。

右连接的语法格式如下：

1
2
3
4
SELECT <字段名>
 FROM <表1>
 RIGHT OUTER JOIN <表2> 
 ON子句

上述sql语句中，与左连接相反，右连接以“表2”为基表，“表1”为参考表。右连接查询时，可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。如果“表2”的某行在“表1”中没有匹配行，那么在返回结果中，“表1”的字段值均为空值（NULL）。

右连接中可以省略 OUTER 关键字，只使用关键字RIGHT JOIN。

使用外连接查询时，一定要分清需要查询的结果，是需要显示左表的全部记录还是右表的全部记录，然后选择相应的左连接和右连接。

参考

https://github.com/datawhalechina/wonderful-sql

http://m.biancheng.net/view/7463.html