집합 연산자 정리(MSSQL 문법 기준)

CREATE TABLE EMPLOYEES(
EMP_ID INT PRIMARY KEY,
EMP_NAME VARCHAR(50),
DEPT_ID INT
);

CREATE TABLE DEPARTMENTS(
DEPT_ID INT PRIMARY KEY,
DEPT_NAME VARCHAR(50)
);

INSERT INTO EMPLOYEES(EMP_ID, EMP_NAME, DEPT_ID)
VALUES
(1, 'ALICE', 1),
(2, 'BOB', 2),
(3, 'CHARLIE', 3),
(4, 'DAVID', 3),
(5, 'EVA', 2),
(6, 'FRANK', 4),
(7, 'GEORGE', 4);

INSERT INTO DEPARTMENTS (DEPT_ID, DEPT_NAME)
VALUES
(1, 'HR'),
(2, 'ENGINEERING'),
(3, 'MARKETING'),
(4, 'SALES'),
(5, 'ALICE'),
(6, 'CHARLIE');

SELECT *
FROM EMPLOYEES;

SELECT * 
FROM DEPARTMENTS;

UNION

SELECT EMP_NAME AS NAME
FROM EMPLOYEES
UNION
SELECT DEPT_NAME AS NAME
FROM DEPARTMENTS;

UNION ALL

SELECT EMP_NAME AS NAME
FROM EMPLOYEES
UNION ALL
SELECT DEPT_NAME AS NAME
FROM DEPARTMENTS;

INTERSECT

SELECT EMP_NAME AS NAME
FROM EMPLOYEES
INTERSECT
SELECT DEPT_NAME AS NAME
FROM DEPARTMENTS;

MINUS(EXCEPT)

SELECT EMP_NAME AS NAME
FROM EMPLOYEES
EXCEPT
SELECT DEPT_NAME AS NAME
FROM DEPARTMENTS;