UNION : 위아래로 이어서 정렬

연산자	설명
UNION	중복을 제거한 집합
UNION ALL	중복을 제거하지 않은 집합

SELECT CustomerName AS Name, City, Country, 'CUSTOMER'
FROM Customers

UNION

SELECT SupplierName AS Name, City, Country, 'SUPPLIER'
FROM Suppliers

ORDER BY Name;

Gourmet Lanchonetes	Campinas	Brazil	CUSTOMER
Grandma Kelly's Homestead	Ann Arbor	USA	SUPPLIER
Great Lakes Food Market	Eugene	USA	CUSTOMER

합집합

• UNION : 중복 제거
• UNION ALL : 중복 허용

SELECT CategoryID AS ID FROM Categories
WHERE CategoryID > 4

UNION

SELECT EmployeeID AS ID FROM Employees
WHERE EmployeeID % 2 = 0;

-- UNION ALL로 바꾸면 중복을 허용하며 모든 것들이 다 나옴!!

ID

---

5

---

6

---

7

---

8

---

2

---

4

---

교집합

SELECT CategoryID AS ID
FROM Categories C, Employees E
WHERE 
  C.CategoryID > 4
  AND E.EmployeeID % 2 = 0
  AND C.CategoryID = E.EmployeeID;

ID

---

6

---

8

---

차집합

SELECT CategoryID AS ID
FROM Categories
WHERE 
  CategoryID > 4
  AND CategoryID **NOT IN** (
    SELECT EmployeeID
    FROM Employees
    WHERE EmployeeID % 2 = 0
  );

ID

---

5

---

7

---

대칭 차집합

SELECT ID FROM (
  SELECT CategoryID AS ID 
  FROM Categories
  WHERE CategoryID > 4
  
  **UNION ALL**
  
  SELECT EmployeeID AS ID 
  FROM Employees
  WHERE EmployeeID % 2 = 0
) AS Temp
 
**GROUP BY ID 
HAVING COUNT(*) = 1;**

→ 서브쿼리를 마치 테이블처럼 사용함!

→ HAVING을 써서 가져왔음

ID

---

2

---

4

---

5

---

7

---