SQL集合运算总结

一.表的加减法

1. 集合运算

集合在数学领域表示“（各种各样的）事物的总和”，在数据库领域表示记录的集合。具体来说，表、视图和查询的执行结果都是记录的集合。

2. 表的加法

• 表的加法：UNION（并集）：集合运算符会除去重复的记录。
  

SELECT product_id, product_name
  FROM Product
 UNION
SELECT product_id, product_name
  FROM Product2;

【例子】找出 Product 和 Product2 中售价高于 500 的商品的基本信息.

SELECT
	purchase_price 
FROM
	Product
	WHERE purchase_price>500 
	UNION
SELECT
	purchase_price 
FROM
	Product2
	WHERE purchase_price>500

这里发现使用UNION对两个查询结果取并集和在一个查询结果中使用WHERE子句，然后使用OR连接两个查询条件的结果相同。
这并不意味着UNION没有必要，当需要对两个不同的表取并集的时候，UNION就派上用场了。

• 若想要在集合运算结果中包含重复值，需在UNION后加ALL关键字。

-- 保留重复行
SELECT product_id, product_name
  FROM Product
 UNION ALL
SELECT product_id, product_name
  FROM Product2;

【例子】商店决定对product表中利润低于50%和售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集. 查询结果类似下表:

SELECT * 
FROM Product 
WHERE
sale_price < 1000 UNION ALL SELECT * FROM Product WHERE sale_price > 1.5 * purchase_price

3. 差集 补集

• 记录的减法EXCEPT，使用方法也和UNION相同，但不适用于MySQL。但使用NOT IN可以达到相同的效果。
  
  【例子】找出只存在于Product表但不存在于Product2表的商品.

SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM Product2)

【例子】使用NOT谓词进行集合的减法运算, 求出Product表中, 售价高于2000,但利润低于30%的商品, 结果应该如下表所示.

SELECT * 
  FROM Product
 WHERE sale_price > 2000 
   AND product_id NOT IN (SELECT product_id 
                            FROM Product 
                           WHERE sale_price<1.3*purchase_price)

• 选取表中公共部分用INTERSECT，使用和UNION相同，但不适用于MySQL。但对于同一个表两个查询结果，它们的交集可以等价将两个查询条件适用AND连接实现
  
  【例子】
  使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品,查询结果如下所示
  

SELECT *
FROM 	product
WHERE sale_price<1500
AND sale_price >1.5*purchase_price

4. 对称差

• 对称差是指那些仅属于A或仅属于B的元素构成的集合.
• 求对称差：先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差.
  【例子】使用Product表和Product2表的对称差来查询哪些商品只在其中一张表, 结果类似于:
  

SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id FROM Product2)
UNION
SELECT * 
  FROM Product2
 WHERE product_id NOT IN (SELECT product_id FROM Product)

1. 作为运算对象的记录的列数必须相同；
2. 作为运算对象的记录中列的类型必须一致；
3. 可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次。

二.联结(JOIN)

什么是联结：简单来说，联结是一种机制，用来在一条SELECT语句中关联表，通过单条SELECT语句检索出存储在多个表中的数据。

1.使用表别名

别名除了用于列名和字段外，还可以给表名起别名，这么做的理由是：1. 缩短SQL语句
2. 允许在单条SELECT语句中多次使用相同的表。需要注意的是表别名只在查询中使用

2. 内联结

• 内联结 INNER JOIN：在 ON 之后指定两张表联结所使用的列（联结键）
  进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间。

【找出每个商店的商店编号, 商店名称, 商品编号, 商品名称, 商品类别, 商品售价,商品数量信息.】

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON SP.product_id = P.product_id;

注意：

1. 进行连结时需要在 FROM 子句中使用多张表.
2. 必须使用 ON 子句来指定连结条件.
3. SELECT 子句中的列最好按照 表名.列名 的格式来使用.

• 如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边.
  对于上个查询要求，加入WHERE语句。
  

SELECT *
  FROM (-- 第一步查询的结果
        SELECT SP.shop_id
               ,SP.shop_name
               ,SP.product_id
               ,P.product_name
               ,P.product_type
               ,P.sale_price
               ,SP.quantity
          FROM ShopProduct AS SP
         INNER JOIN Product AS P
            ON SP.product_id = P.product_id) AS STEP1
 WHERE shop_name = '东京'
   AND product_type = '衣服' ;

3. 外联结

• 许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行，
  例如：列出所有产品以及订购数量，包括没有人订购的产品。
  联结包含了那些在相关表中没有关联行的行，称为外联结。
  
• 左外联结
  左外联结的结果集中包含左表(JOIN关键字左边的表)中所有记录，然后左表按照联结条件与右表进行联结，如果右表中没有满足联结条件的记录，则结果集中右表中的相应行数数据填充为NULL。
• 右外联结
  右外联结的结果集中包含满足联结条件的所有数据和右表(JOIN关键字右边的表)中不满足条件的数据，左表中的相应行数据为NULL

4. 多表联结

SQL对一条SELECT语句中可以联结的表的数量没有限制，创建联结的基本规则也相同，先列出所有表，然后定义表之间的关系。

• 【例子】
  显示编号为20005的订单的所有物品，订单物品存在orderitems表。这里FROM子句列出三个表，而WHERE子句定义了这两个联结条件，第三个联结条件用来过滤出订单20005中的产品。
  
  性能考虑 ：MySQL在运行时关联指定的每个表以处理联结，但这种处理可能非常消耗资源，不要联结不必要的表，联结的表越多，性能下降的越厉害。

• 不等值联结
  在WHERE子句中用来连接两个表的条件称为连接条件。如果连接条件中的连接运算符是=时，称等值连接，如果是其他的运算符，则是不等值连接。

5. 交叉联结

交叉连接不带 WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积。从一张表中循环取出每一条记录，每条记录都会去另一张表中匹配每一条记录，匹配结果一定保留（因为无条件，如果有条件，则只保留满足条件的结果）。

假设 A 表有 n 条记录，B 表有 m 条记录，则结果为 n * m 条记录。

SELECT * FROM 	product CROSS JOIN product2