[DB, SQL] UNION

UNION, UNION ALL

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;
# 두개의 테이블의 data가 같은 Column에서 조회될 수 있도록 합침

UNION ALL

SELECT CategoryID AS ID FROM Categories
WHERE CategoryID > 4
UNION ALL
SELECT EmployeeID AS ID FROM Employees
WHERE EmployeeID % 2 = 0;

---

교집합

SELECT CategoryID AS ID
FROM Categories C, Employees E
WHERE 
  C.CategoryID > 4
  AND E.EmployeeID % 2 = 0
  AND C.CategoryID = E.EmployeeID;
# Categories 테이블과 Employees 테이블에 동일하게 존재하는 data만 출력

---

차집합

SELECT CategoryID AS ID
FROM Categories
WHERE 
  CategoryID > 4
  AND CategoryID NOT IN (
    SELECT EmployeeID
    FROM Employees
    WHERE EmployeeID % 2 = 0
  );
# Categories테이블에서 Empoyees테이블의 값이 제외된 data를 출력

---

대칭 차집합

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;

두 테이블의 합집합에서 교집합을 제외한 결과값을 출력한다.