mysql - 2개의 테이블 간 (교, 합, 차집합) 구하기

mysql : 2개의 테이블 간 (교, 합, 차집합) 구하기  

 
 
테스트 환경 {
CREATE TABLE `tablea` (
  `no` int(10) unsigned NOT NULL auto_increment,
  `id` varchar(16) NOT NULL default '',
  `name` varchar(16) NOT NULL default '',
  PRIMARY KEY  (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `tablea` VALUES (1, '100', 'value 100');
INSERT INTO `tablea` VALUES (2, '101', 'value 101');
INSERT INTO `tablea` VALUES (3, '102', 'value 102');
INSERT INTO `tablea` VALUES (4, '103', 'value 103');
INSERT INTO `tablea` VALUES (5, '104', 'value 104');
INSERT INTO `tablea` VALUES (6, '105', 'value 105');
INSERT INTO `tablea` VALUES (7, '106', 'value 106');
INSERT INTO `tablea` VALUES (8, '107', 'value 107');

CREATE TABLE `tableb` (
  `no` int(10) unsigned NOT NULL auto_increment,
  `id` varchar(16) NOT NULL default '',
  `name` varchar(16) NOT NULL default '',
  PRIMARY KEY  (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `tableb` VALUES (1, '101', 'value 101');
INSERT INTO `tableb` VALUES (2, '103', 'value 103');
INSERT INTO `tableb` VALUES (3, '106', 'value 106');
INSERT INTO `tableb` VALUES (4, '107', 'value 107');
INSERT INTO `tableb` VALUES (5, '110', 'value 110');
}

A ∩ B {
SELECT tableA.id, tableA.name, tableB.name FROM tableA, tableB WHERE tableA.id = tableB.id GROUP BY tableA.id;
또는
SELECT tableA.id, tableA.name, tableB.name FROM tableA LEFT JOIN tableB ON tableA.id=tableB.id WHERE tableB.id IS NOT NULL
또는
SELECT tableB.id, tableA.name, tableB.name FROM tableB LEFT JOIN tableA ON tableB.id=tableA.id WHERE tableA.id IS NOT NULL
}

A ∪ B {
SELECT tableA.id AS id, tableA.name AS name FROM tableA UNION SELECT tableB.id AS id, tableB.name AS name FROM tableB;
}

A - B {
SELECT tableA.id, tableA.name FROM tableA LEFT JOIN tableB ON tableA.id=tableB.id WHERE tableB.id IS NULL;

참고::흔히 행하는 잘못된 퀘리
SELECT tableA.id, tableA.name FROM tableA, tableB WHERE tableA.id <> tableB.id GROUP BY tableA.id;
}

B - A {
SELECT tableB.id, tableB.name FROM tableB LEFT JOIN tableA ON tableB.id=tableA.id WHERE tableA.id IS NULL
}

해석 설명 {
LEFT OUTER JOIN을 응용하여 가능하다.

LEFT OUTER JOIN 결과는 다음과 같으므로
----------------------------------------
tableA id  tableB id 
100          NULL
101          101
102          NULL
103          103
104          NULL
105          NULL
106          106
107          107
----------------------------------------
WHERE 절에서 tableB.id 가 NULL 인것만 고르면 A - B가 가능하고...
B - A 역시 마찬가지 원리.
A ∩ B는 tableB.id가 NULL이 아닌것을 골라내면 됨.