SQL 합집합, 교집합, 차집합, 대칭차집합

CREATE TABLE tablea (str varchar(8), PRIMARY KEY (str));
CREATE TABLE tableb (str varchar(8), PRIMARY KEY (str));
INSERT INTO tablea (str) VALUES ('A'), ('B'), ('C'), ('D');
INSERT INTO tableb (str) VALUES ('C'), ('D'), ('E'), ('F');

-- 합집합

SELECT str FROM tablea
UNION
SELECT str FROM tableb;

-- 교집합

SELECT a.str FROM tablea a, tableb b WHERE a.str = b.str;
SELECT a.str 
FROM tablea a
INNER JOIN tableb b ON a.str = b.str;

-- 차집합

SELECT str FROM tablea WHERE str NOT IN (
    SELECT DISTINCT str FROM tableb
);

-- 대칭차집합

SELECT str FROM tablea WHERE str NOT IN ( SELECT DISTINCT str FROM tableb )
UNION ALL
SELECT str FROM tableb WHERE str NOT IN ( SELECT DISTINCT str FROM tablea )
;

SELECT str FROM (
    SELECT str FROM tablea
    UNION ALL
    SELECT str FROM tableb
) AS tmp
GROUP BY str HAVING count(1) = 1
;

source: https://zetawiki.com/wiki/MySQL_%ED%95%A9%EC%A7%91%ED%95%A9,_%EA%B5%90%EC%A7%91%ED%95%A9,_%EC%B0%A8%EC%A7%91%ED%95%A9,_%EB%8C%80%EC%B9%AD%EC%B0%A8

appkr/sql.md