JOIN

Inner Join

양쪽 모두에 값이 있는 행 반환 (NULL포함 안함)

SELECT * 
FROM [TABLE] T1
JOIN [TABLE2] T2
ON T1.[COL1] = T2.[COL2];

SELF JOIN

같은 테이블끼리 조인

SELECT * 
FROM [TABLE] T1
JOIN [TABLE] T2
ON T1.[COL1] + 1 = T2.[COL1];

Left/Right OUTER JOIN

한쪽에 데이터가 없어도 행 반환

SELECT * 
FROM [TABLE] T1
LEFT JOIN [TABLE2] T2
ON T1.[COL1] + 1 = T2.[COL1];

SELECT * 
FROM [TABLE] T1
RIGHT JOIN [TABLE2] T2
ON T1.[COL1] + 1 = T2.[COL1];

CROSS JOIN

조건 없이 모든 조합 반환 (A * B)

SELECT * 
FROM [TABLE] T1
CROSS JOIN [TABLE2] T2;

UNION

UNION : 중복을 제거한 집합 UNION ALL : 중복을 제거하지 않은 집합

SELECT [COL1] as COL1
FROM [TABLE]
UION
SELECT [COL2] as COL1
FROM [TABLE2];

• 합집합 (A ∪ B)

  SELECT [COL1] as COL1 FROM [TABLE]
  WHERE COL1 > 4
  UION
  SELECT [COL2] as COL1 FROM [TABLE2];
  WHERE COL2 % 2 = 0;
  

• 교집합 (A ∩ B)

  SELECT [COL1]
  FROM [TABLE] T1, [TABLE2] T2
  WHERE 
    T1.[COL1] > 4
    AND T2.[COL2] % 2 = 0
    AND T1.[COL1] = T2.[COL2];
  

• 차집합 (A - B)

  SELECT [COL1]
  FROM [TABLE]
  WHERE
    [COL1] > 4
    AND [COL1] NOT IN (
        SELECT [COL2]
        FROM [TABLE2]
        WHERE [COL2] % 2 = 0;
    );
  

• 대칭 차집합 ((A ∪ B) - (A ∩ B))

  SELECT COL FROM (
    SELECT [COL1] AS COL FROM [TABLE]
    WHERE [COL1] > 4
    UNION ALL
    SELECT [COL2] AS COL FROM [TABLE2]
    WHERE [COL2] % 2 = 0
  ) AS TEMP
  GROUP BY COL HAVING COUNT(*) = 1;