[MySQL, Oracle] FULL JOIN과 특수 조인 (SELF JOIN, NATURAL JOIN, SEMI JOIN, ANTI JOIN)

FULL (OUTER) JOIN

두 테이블의 모든 행을 포함하는 JOIN을 제공하며, 매칭되지 않는 행은 NULL로 채움

특징

• 두 테이블 간의 합집합
• MySQL에서는 지원하지 않고, 직접 구현해야함 (PostgreSQL, Oracle, SQL Server는 지원)
• 한쪽 테이블에만 존재하는 행은 반대쪽 컬럼이 NULL

예시

-- 테이블 데이터 예시
-- customer: (id=1, name='김철수'), (id=2, name='이영희'), (id=3, name='박민수')
-- orders:  (cust_id=1, order_date='2025-01-01'), (cust_id=2, order_date='2025-01-02'), (cust_id=4, order_date='2025-01-03')

SELECT 
       c.id AS cust_id
     , c.name
     , o.order_date
  FROM customer c
  FULL OUTER JOIN orders o 
    ON c.id = o.cust_id;

결과

cust_id	name	order_date
1	김철수	2025-01-01
2	이영희	2025-01-02
3	박민수	NULL
NULL	NULL	2025-01-03

MySQL에서 FULL JOIN 구현

SELECT 
       c.id AS cust_id
     , c.name
     , o.order_date
  FROM customer c
  LEFT JOIN orders o 
    ON c.id = o.cust_id

UNION

SELECT 
       c.id AS cust_id
     , c.name
     , o.order_date
  FROM customer c
 RIGHT JOIN orders o
    ON c.id = o.cust_id

단, 위 방법은 두 번의 JOIN을 수행하므로 주의가 필요

성능 개선 방법

성능을 크게 높이는 방법은 없지만 다음과 같이 개선 시킬 수 있다.

1) 서브쿼리를 이용한 배타적 합집합 (성능 최적화) - 성능 중심

-- 모든 왼쪽 데이터 + 교집합
SELECT 
       c.id AS cust_id
     , c.name
     , o.order_date
  FROM customer c
  LEFT JOIN orders o 
    ON c.id = o.cust_id

UNION ALL -- 중복 체크를 하지 않는 UNION ALL을 써서 비용 절감

-- 오른쪽 테이블에만 존재하는 데이터 (교집합 제외)
SELECT 
       o.cust_id
     , c.name
     , o.order_date
  FROM customer c
 RIGHT JOIN orders o
    ON c.id = o.cust_id
 WHERE c.id IS NULL; -- 이미 위에서 나온 데이터는 제외

2) 집계 함수를 이용한 방식 - 테이블이 여러 개인 경우 연산량 감소

SELECT 
       cust_id
     , MAX(name) AS name
     , MAX(order_date) AS order_date
FROM (
    -- customer 테이블 데이터 준비
    SELECT id AS cust_id
         , name
         , NULL AS order_date 
    FROM customer
    
    UNION ALL
    
    -- orders 테이블 데이터 준비
    SELECT cust_id
         , NULL AS name
         , order_date 
    FROM orders
) AS combined
GROUP BY cust_id;

SELF JOIN

자기 자신을 JOIN하는 방식으로 계층 구조나 자기 참조 데이터에 유용

특징

• 테이블에 다른 별칭을 부여한 후 공통 컬럼으로 연결

SELECT e1.name
     , e2.name 
 FROM employees e1
 JOIN employees e2
   ON e1.manager_id = e2.id;

NATURAL JOIN

두 테이블의 동일한 이름과 데이터 타입을 가진 컬럼을 자동으로 찾아 동등 JOIN

특징

• 같은 이름의 컬럼이 있다면, 별도의 ON 조건 없이도 연결됨

SELECT *
  FROM customers NATURAL JOIN cities;

SEMI JOIN

왼쪽 테이블에서 오른쪽 테이블에 존재하는 행만 반환하며, 오른쪽 컬럼은 결과에 포함하지 않음

특징

• 실제로 JOIN을 수행하기보다, 조건에 맞는 데이터가 있는지 확인하는 용도
• EXIST나 IN 연산자 사용

-- EXISTS 사용
SELECT *
  FROM Customers c
 WHERE EXISTS (
    			SELECT 1
                 FROM Orders o
                WHERE o.customer_id = c.id
              );

-- IN 사용
SELECT *
  FROM Customers
  WHERE id IN (
  				SELECT customer_id
                  FROM Orders
              );

vs. INNER JOIN

ex. 한 명의 고객이 100번 주문한다면

• INNER JOIN : 결과에 동일한 고객 이름이 100번 반복하여 나옴. (중복 제거 위한 DISTINCT 필요)
• SEMI JOIN : 결과에 고객 이름이 1번만 나옴.

SEMI JOIN 용도

• 존재 여부 확인 : 상세 내역은 궁금하지 않지만, 특정 조건에 해당되는 대상자 명단 필요시
• 성능 최적화 : INNER JOIN + DISTINCT 사용하는 것보다 빠를때가 많음.
• 데이터 무결성 확인 : 특정 기준 만족하는 데이터 셋만 필터링하고 싶을 때.

ANTI JOIN

왼쪽 테이블에서 오른쪽 테이블에 일치하는 행이 없는 데이터만 반환하는 JOIN 기법.

<=> SEMI JOIN

특징

• 차집합 추출 : 두 집합 사이 차집합을 구하는 것과 같음.
• 부재 여부 확인 : 특정 기록이 없는 대상을 필터링 시 사용
• 중복 제거 효과
• 대표적으로 NOT EXISTS나 NOT IN 연산자를 사용

-- NOT EXISTS 사용
SELECT *
  FROM Customers c
 WHERE NOT EXISTS (
    				SELECT 1 
    				  FROM Orders o 
    				 WHERE o.customer_id = c.id
				  );

-- NOT IN 사용
SELECT *
  FROM Customers
  WHERE id NOT IN (
    				SELECT customer_id
                      FROM Orders
				  );

-- LEFT JOIN + NULL 체크
SELECT c.*
  FROM Customers c
  LEFT JOIN Orders o
    ON c.id = o.customer_id
 WHERE o.customer_id IS NULL;

NOT IN 사용 시 오른쪽 테이블에 NULL이 있으면 전체 결과가 누락될 수 있음.