MySQL実装差分(Minus)と交差(Intersect)

MySqlはUnion(パラレル)集合演算のみをサポートしており、4.0以降にもあるようです.しかし,交差Intersect,差分Exceptについては実現しなかった.
一般的なソリューションはinとnot inで解決され、小量のデータはまあまあですが、データ量が大きくなると効率が低下しtable 1の作成

/*DDL   */------------

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table 2の作成

/*DDL   */------------

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

挿入

INSERT INTO t1 VALUES(1,'  ',10);
INSERT INTO t1 VALUES(2,'  ',20);
INSERT INTO t1 VALUES(3,'  ',30);
INSERT INTO t1 VALUES(4,'hello',40);


INSERT INTO t2 VALUES(1,'  ',10);
INSERT INTO t2 VALUES(2,'  ',22);
INSERT INTO t2 VALUES(3,'  ',31);
INSERT INTO t2 VALUES(4,'hello',40);

	SELECT t1.* FROM t1 
	
	id	name	age
	1	  	    10
	2	  	    20
	3	  	    30
	4	hello	40

	SELECT t2.* FROM t2 
	
	id	name	age
	1	  	10
	2	  	22
	3	  	31
	4	hello	40

not inを使用して差セットを求めるが、効率が低い

	SELECT t1.* FROM t1 
	WHERE 
	name NOT IN
	(SELECT name FROM t2)
	
	id	name	age
	3	  	    30

    SELECT t1.id, t1.name, t1.age
    FROM t1 
    LEFT JOIN t2 
    ON t1.id = t2.id
    WHERE t1.name != t2.name
    
       OR t1.age != t2.age;
    
    
    id	name	age
    2	  	    20
    3	  	    30

交差を求めて、この時id name ageだけがすべて同じで要求に合致します

SELECT  id,  NAME,  age, COUNT(*)
    FROM (SELECT id, NAME, age
        FROM t1
        
        UNION ALL
        
        SELECT id, NAME, age
        FROM t2
        ) a
    GROUP BY id, NAME, age
    HAVING COUNT(*) > 1
    
    id	NAME	age	COUNT(*)
    1	  	    10	2
    4	hello	40	2

union allとunionの違い
UNIONとUNIONALLのキーワードは、両方の結果セットを1つに統合しますが、どちらも使用と効率が異なります.
1、重複結果に対する処理:UNIONは表リンクを行った後に重複記録を選別し、Union Allは重複記録を除去しない.
2、並べ替えの処理:Unionはフィールドの順序によって並べ替える;ユニオンALLは単純に2つの結果をマージして返すだけです.
効率的に言えばユニオンALLはユニオンよりずっと速いので、マージされた2つの結果セットに重複データが含まれておらず、ソートが不要であることが確認できればユニオンALLを使用します.