MySQL 합집합, 교집합, 차집합, 대칭차

MySQL 집합 연산
MySQL 합집합, 교집합, 차집합, 대칭차
SQL 합집합, 교집합, 차집합, 대칭차

1 테스트 환경[ | ]

tableA의 원소는 A, B, C, D
tableB의 원소는 C, D, E, F

테이블 생성 및 데이터 입력

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');

2 합집합[ | ]

SELECT str FROM tableA
UNION
SELECT str FROM tableB;
-- A
-- B
-- C
-- D
-- E
-- F

3 교집합[ | ]

SELECT a.str FROM tableA a, tableB b WHERE a.str = b.str;
-- C
-- D

4 차집합[ | ]

SELECT str FROM tableA WHERE str NOT IN (
	SELECT DISTINCT str FROM tableB
); 
-- A
-- B

SELECT a.str FROM tableA a LEFT JOIN tableB b ON a.str = b.str
WHERE b.str IS NULL;
-- A
-- B

5 대칭차집합[ | ]

SELECT str FROM (
	SELECT str FROM tableA
	UNION ALL
	SELECT str FROM tableB
) AS tmp
GROUP BY str HAVING COUNT(*)=1
-- A
-- B
-- E
-- F

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 )

6 같이 보기[ | ]

7 참고[ | ]

http://phpschool.com/link/tipntech/42371