[SQL] 교집합 & 차집합 (INTERSECT, MINUS)

안녕하세요.

오늘은 SQL별 교집합과 차집합에 대한 차이를 예시를 통해 알아보겠습니다.

 

 MySQL vs Oracle 교집합 & 차집합 차이

▣ 예시 데이터

 

- table1

+----+---------+-------+
| id |  name   | score |
+----+---------+-------+
|  1 | Alice   |    95 |
|  2 | Bob     |    82 |
|  3 | Charlie |    78 |
|  4 | David   |    91 |
+----+---------+-------+

- table2

+----+--------+-------+
| id |  name  | score |
+----+--------+-------+
|  2 | Bob    |    82 |
|  3 | Alice  |    75 |
|  5 | Edward |    87 |
+----+--------+-------+

 

: 교집합 (Intersection)

- MySQL

SELECT * FROM table1
INNER JOIN table2 ON table1.id = table2.id

- Oracle

SELECT * FROM table1
INTERSECT
SELECT * FROM table2;

+----+------+-------+
| id | name | score |
+----+------+-------+
|  2 | Bob  |    82 |
+----+------+-------+

 

: 차집합 (Difference)

- MySQL

SELECT * FROM table1
WHERE NOT EXISTS (
  SELECT * FROM table2
  WHERE table1.id = table2.id
)

- Oracle

SELECT * FROM table1
MINUS
SELECT * FROM table2;

+----+---------+-------+
| id |  name   | score |
+----+---------+-------+
|  1 | Alice   |    95 |
|  4 | David   |    91 |
+----+---------+-------+