MySQL中的并、差、交和除运算

-- 创建a和b表
CREATE TABLE a (x INT, y VARCHAR(5));
CREATE TABLE b (x INT, y VARCHAR(5));
-- 插入数据到a表
INSERT INTO a(x,y) VALUES(1,'A');
INSERT INTO a(x,y) VALUES(2,'B');
INSERT INTO a(x,y) VALUES(3,'C');
INSERT INTO a(x,y) VALUES(4,'D');
-- 插入数据到b表
INSERT INTO b(x,y) VALUES(1,'A');
INSERT INTO b(x,y) VALUES(3,'C');

SELECT * FROM a;
+------+------+
| x    | y    |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
+------+------+

SELECT * FROM b;
+------+------+
| x    | y    |
+------+------+
|    1 | A    |
|    3 | C    |
+------+------+

SELECT * FROM a UNION SELECT * FROM b;
+------+------+
| x | y |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
+------+------+

SELECT * FROM a UNION ALL SELECT * FROM b;
+------+------+
| x | y |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 1 | A |
| 3 | C |
+------+------+

SELECT * FROM a WHERE (x,y) NOT IN (SELECT * FROM b);
+------+------+
| x | y |
+------+------+
| 2 | B |
| 4 | D |
+------+------+

SELECT * FROM a
WHERE NOT EXISTS (SELECT * FROM b WHERE b.x = a.x AND b.y = a.y);
+------+------+
| x | y |
+------+------+
| 2 | B |
| 4 | D |
+------+------+

-- 最高效的方式
SELECT DISTINCT a.x AS x, a.y AS y
FROM a LEFT OUTER JOIN b USING (x, y) WHERE b.x IS NULL;
+------+------+
| x | y |
+------+------+
| 2 | B |
| 4 | D |
+------+------+

SELECT * FROM a WHERE (x,y) IN (SELECT * FROM b)
+------+------+
| x | y |
+------+------+
| 1 | A |
| 3 | C |
+------+------+

SELECT * FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.x=a.x AND b.y = a.y);
+------+------+
| x | y |
+------+------+
| 1 | A |
| 3 | C |
+------+------+

-- 最高效的方式
SELECT DISTINCT a.x AS x, a.y AS y
FROM a INNER JOIN b USING (x,y);
+------+------+
| x | y |
+------+------+
| 1 | A |
| 3 | C |
+------+------+

-- 创建c和d表
CREATE TABLE c (x INT, y VARCHAR(5));
CREATE TABLE d (x INT);
-- 插入数据到c表
INSERT INTO c(x,y) VALUES(1,'A');
INSERT INTO c(x,y) VALUES(2,'A');
INSERT INTO c(x,y) VALUES(3,'A');
INSERT INTO c(x,y) VALUES(1,'B');
INSERT INTO c(x,y) VALUES(2,'B');
INSERT INTO c(x,y) VALUES(3,'C');
INSERT INTO c(x,y) VALUES(3,'D');
-- 插入数据到d表
INSERT INTO d(x) VALUES(1);
INSERT INTO d(x) VALUES(2);
INSERT INTO d(x) VALUES(3);

SELECT DISTINCT c1.y AS y
FROM c c1
WHERE NOT EXISTS
 (SELECT d.x FROM d
 WHERE d.x NOT IN (SELECT c2.x FROM c c2 WHERE c2.y = c1.y));
+------+
| y |
+------+
| A |
+------+

SELECT DISTINCT c1.y AS y
FROM c c1
WHERE NOT EXISTS
 (SELECT * FROM d
 WHERE NOT EXISTS (SELECT * FROM c c2 WHERE c2.y = c1.y AND c2.x = d.x));
+------+
| y |
+------+
| A |
+------+

-- does not work;
SELECT DISTINCT c1.y AS y
FROM c c1
WHERE NOT EXISTS
 (SELECT d.x FROM d LEFT OUTER JOIN c c2 USING (x)
 WHERE c2.y = c1.y AND c2.x IS NULL );
+------+
| y |
+------+
| A |
| B |
| C |
| D |
+------+
-- does not work;
SELECT DISTINCT c1.y AS y
FROM c c1
WHERE NOT EXISTS
 (SELECT * FROM d LEFT OUTER JOIN c c2
 ON (d.x = c2.x) WHERE c2.y = c1.y AND c2.x IS NULL );
+------+
| y |
+------+
| A |
| B |
| C |
| D |
+------+
-- does not work;
SELECT DISTINCT c1.y AS y
FROM c c1
WHERE NOT EXISTS
 (SELECT * FROM d LEFT OUTER JOIN c c2
 ON (d.x = c2.x AND c2.y = c1.y AND c2.x IS NULL) );
ERROR 1054 (42S22): Unknown column 'c1.y' in 'on clause'