MySQLでINTERSECTやEXCEPT(MINUS)をJOINを使用せず代替する方法

サンプルデータ

CREATE TABLE T1(ID int, COL1 char(1), COL2 int);
CREATE TABLE T2(ID int, COL1 char(1), COL2 int);
CREATE TABLE T3(ID int, COL1 char(1), COL2 int);

INSERT INTO T1(ID, COL1, COL2)
VALUES
    (1, 'A', 1),
    (2, 'A', 1),
    (3, 'A', 2),
    (4, 'A', 3),
    (5, 'A', 3),
    (6, 'A', 4),
    (7, 'A', 5),
    (8, 'A', null),
    (9, 'B', null);

INSERT INTO T2(ID, COL1, COL2)
VALUES
    (1, 'A', 2),
    (2, 'A', 2),
    (3, 'A', 3),
    (4, 'A', 3),
    (5, 'A', 4),
    (6, 'A', 5),
    (7, 'B', null);

INSERT INTO T3(ID, COL1, COL2)
VALUES
    (1, 'A', 3),
    (2, 'A', 3),
    (3, 'A', 4),
    (4, 'A', 6),
    (5, 'B', null);

T1

id	col1	col2
1	A	1
2	A	1
3	A	2
4	A	3
5	A	3
6	A	4
7	A	5
8	A	(null)
9	B	(null)

T2

id	col1	col2
1	A	2
2	A	2
3	A	3
4	A	3
5	A	4
6	A	5
7	B	(null)

T3

id	col1	col2
1	A	3
2	A	3
3	A	4
4	A	6
5	B	(null)

積集合(INTERSECT)

postgreSQLでは

SELECT COL1,COL2 FROM T1
INTERSECT
SELECT COL1,COL2 FROM T2
INTERSECT
SELECT COL1,COL2 FROM T3

結果

col1	col2
A	3
A	4
B	(null)

MySQLでは

SELECT COL1,COL2
FROM (
    SELECT 
      1  -- 各テーブルのレコードをユニークにするためのコード
    , 1 AS CNT
    , COL1, COL2 FROM T1
    UNION
    SELECT 2, 1, COL1, COL2 FROM T2
    UNION
    SELECT 3, 1, COL1, COL2 FROM T3
    ) AS T
GROUP BY COL1, COL2 
HAVING SUM(CNT) = 3  -- 対象のテーブル数を指定
              -- T1,T2,T3すべてに存在すれば合計で3になる

結果

col1	col2
A	3
A	4
B	(null)

差集合(EXCEPTやMINUS)

postgreSQLでは

SELECT COL1,COL2 FROM T1
EXCEPT
SELECT COL1,COL2 FROM T2
EXCEPT
SELECT COL1,COL2 FROM T3

結果

col1	col2
A	1
A	(null)

MySQLでは

SELECT COL1,COL2
FROM (
    SELECT 
      1  -- 各テーブルのレコードをユニークにするためのコード
    , 1 AS CNT
    , COL1, COL2 FROM T1
    UNION
    SELECT 2, -1, COL1, COL2 FROM T2
    UNION
    SELECT 3, -1, COL1, COL2 FROM T3
    ) AS T
GROUP BY COL1, COL2 
HAVING SUM(CNT) = 1 
              -- T2,T3に存在すれば1未満になります

結果

col1	col2
A	1
A	(null)

応用記事