Set Operators

The union Operator

• union 默认的操作会对合并后的数据进行排序，并删除掉重复的数据; union all 则会将两组数据表合并，不会删除重复的部分。

• example

  # UNION ALL
  SELECT c.first_name, c.last_name
  FROM customer c
  WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
  UNION ALL
  SELECT a.first_name, a.last_name
  FROM actor a
  WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';
  
  # UNION
  SELECT c.first_name, c.last_name
  FROM customer c
  WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
  UNION
  SELECT a.first_name, a.last_name
  FROM actor a
  WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';
  

The intersect Operator

• intersect and intersect all
  • example

    SELECT c.first_name, c.last_name
    FROM customer c
    WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
    INTERSECT
    SELECT a.first_name, a.last_name
    FROM actor a
    WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';
    

• MySQL 8.0之后的版本没有INTERSECT操作
  • 可以使用内联表的方式产生交集
  • example

    SELECT c.first_name, c.last_name
    FROM customer c
    INNER JOIN
    (   SELECT a.first_name, a.last_name
      FROM actor a
      WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
    ) a2
    USING(first_name,last_name)
    WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%';
    

The except Operator

• EXCEPT and EXCEPT ALL

  • example

    SELECT a.first_name, a.last_name
    FROM actor a
    WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';
    EXCEPT
    SELECT c.first_name, c.last_name
    FROM customer c
    WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
    

  • EXCEPT ALL

    A

    | actor_id |
    |----------|
    | 10       |
    | 11       |
    | 12       |
    | 10       |
    | 10       |
    

    B

    | actor_id |
    |----------|
    | 10       |
    | 10       |
    

    A EXCEPT ALL B (根据B里的各元素个数删除，若是EXCEPT则会将属于B的元素全部删除)

    | actor_id |
    |----------|
    | 10       |
    | 11       |
    | 12       |
    

• MySQL 8.0之后的版本没有EXCEPT操作

  • 但可以使用外联表
  • 如：A EXCEPT B可以使用以下query

    SELECT a.str
    FROM a_set a
    LEFT OUTER JOIN b_set bs
    on a.str = bs.str
    WHERE bs.str IS NULL;
    

Set Operations

• ORDER BY 需要先给field定各名字

  • demo(根据field1排序)

    SELECT a.field1 $field_name1, a.field2 $field_name2
  FROM a_table a
    UNION
    SELECT b.field1 $field_name1, b.field2 $field_name2
  FROM b_table b
    ORDER BY $field_name1;