集合运算

7-1 表的加减法

Ⅰ 表的加法–UNION(并集)

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

集合运算符除去重复的记录

注意事项:

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

举例:

SELECT product_id, product_name
  FROM Product
 WHERE product_type = '厨房用具'
UNION
SELECT product_id, product_name
  FROM Product2
 WHERE product_type = '厨房用具'
ORDER BY product_id;

Ⅱ 包含重复行的集合运算–ALL选项

保留重复行的语法,就是在UNION后面添加ALL关键字即可

Ⅲ 选取公共部分–INTERSECT

SELECT product_id, product_name
  FROM Product
INTERSECT
SELECT product_id, product_name
  FROM Product2
ORDER BY product_id;

Ⅳ 记录的减法–EXCEPT(差集)

SELECT product_id, product_name
  FROM Product
EXCEPT
SELECT product_id, product_name
  FROM Product2
ORDER BY product_id;

7-2 联结(以列为单位对表进行联结)

学习重点:

1. 联结(JOIN)就是将其他表中的列添加进来;
2. 联结大体分为内联结和外联结两种

Ⅰ 内联结(INNER JOIN)

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

Ⅱ 外联结(OUTER JOIN)

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
  FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P
    ON SP.product_id = P.product_id;

Ⅲ 三张以上的表的联结

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price, IP.inventory_quantity
  FROM ShopProduct AS SP INNER JOIN Product AS P
    ON SP.product_id = P.product_id
               INNER JOIN InventoryProduct AS IP
                   ON SP.product_id = IP.product_id
WHERE IP.inventory_id = 'S001';