MySQL学习04：集合运算

4.1 表的加减法

表的加法–UNION，UNION 等集合运算符通常都会除去重复的记录

SELECT product_id, product_name
  FROM product
 UNION
SELECT product_id, product_name
  FROM product2;

包含重复行的集合运算 UNION ALL

-- 保留重复行
SELECT product_id, product_name
  FROM product
 UNION ALL
SELECT product_id, product_name
  FROM product2;

4.2 连结 (JOIN)

• 内连结(INNER JOIN)

-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

关于内连结,需要注意以下三点:
要点一: 进行连结时需要在 FROM 子句中使用多张表.

FROM shopproduct AS SP INNER JOIN product AS P

要点二:必须使用 ON 子句来指定连结条件.
要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用.

• 外连结(OUTER JOIN)
  按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结.
  三种外连结的对应语法分别为:

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

• 多表连结
  多表进行内连结

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 = 'P001';

多表进行外连结

SELECT P.product_id
       ,P.product_name
       ,P.sale_price
       ,SP.shop_id
       ,SP.shop_name
       ,IP.inventory_quantity
  FROM product AS P
  LEFT OUTER JOIN shopproduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id