OUTER JOIN

-- 회원 테이블
-- user_id | username
-- 1       | 김철수
-- 2       | 이영희
-- 3       | 박민수

-- 주문 테이블
-- order_id | user_id | status
-- 1        | 1       | DELIVERED
-- 2        | 1       | SHIPPED
-- 3        | 2       | PAID

SELECT u.username, o.order_id, o.status
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

username | order_id | status
---------|----------|----------
김철수   | 1        | DELIVERED
김철수   | 2        | SHIPPED
이영희   | 3        | PAID
박민수   | NULL     | NULL       ← 주문이 없지만 결과에 포함

SELECT u.username
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

* 구매 이력이 없는 고객
* 댓글이 없는 게시글
* 담당자가 배정되지 않은 업무
* 최근 30일간 로그인하지 않은 사용자
* 카테고리에 속하지 않은 상품

SELECT u.username
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

SELECT u.username
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.user_id
);

SELECT u.username
FROM users u
WHERE u.user_id NOT IN (
    SELECT user_id FROM orders
    WHERE user_id IS NOT NULL
);

-- orders에 user_id = NULL인 행이 있으면
-- 이 쿼리는 아무 결과도 반환하지 않음!
SELECT u.username
FROM users u
WHERE u.user_id NOT IN (
    SELECT user_id FROM orders  -- NULL 포함 가능
);

SELECT u.username, o.order_id, o.status
FROM orders o
RIGHT JOIN users u ON u.user_id = o.user_id;

SELECT u.username, o.order_id, o.status
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

SELECT e.name AS 직원명, d.dept_name AS 부서명
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

직원명   | 부서명
---------|--------
김철수   | 개발팀      ← 양쪽 매칭
이영희   | 마케팅팀    ← 양쪽 매칭
박민수   | NULL        ← 부서가 없는 직원
NULL     | 경영지원팀  ← 직원이 없는 부서

-- LEFT JOIN 결과
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id

UNION

-- RIGHT JOIN 결과 (LEFT에서 안 나온 행만 추가)
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id

UNION ALL

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;  -- LEFT JOIN에서 이미 나온 행 제외

-- 배송 완료된 주문만 조인하되, 모든 회원은 보존
SELECT u.username, o.order_id, o.status
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
                   AND o.status = 'DELIVERED';

username | order_id | status
---------|----------|----------
김철수   | 1        | DELIVERED   ← 배송 완료 주문만 조인됨
이영희   | NULL     | NULL        ← 주문 있지만 배송완료가 아니므로 매칭 안 됨
박민수   | NULL     | NULL        ← 주문 없음

SELECT u.username, o.order_id, o.status
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'DELIVERED';

username | order_id | status
---------|----------|----------
김철수   | 1        | DELIVERED   ← 이 행만 남음

-- 회원 → 주문 → 주문상세 순서로 조인
SELECT u.username, o.order_id, oi.product_name, oi.quantity
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id;

-- 첫 번째 LEFT JOIN으로 보존한 박민수 행이
-- 두 번째 INNER JOIN에서 제거됨!
SELECT u.username, o.order_id, oi.product_name
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
INNER JOIN order_items oi ON o.order_id = oi.order_id;
-- 박민수: o.order_id = NULL → INNER JOIN 실패 → 행 제거

-- 잘못된 방법
SELECT u.username, COUNT(*) AS order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.username;

SELECT u.username, COUNT(o.order_id) AS order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.username;

SELECT u.username,
       COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.username;

SELECT e.name AS 직원명,
       m.name AS 상사명
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- LEFT JOIN과 동일
SELECT u.username, o.order_id
FROM users u, orders o
WHERE u.user_id = o.user_id(+);

-- RIGHT JOIN과 동일
SELECT u.username, o.order_id
FROM users u, orders o
WHERE u.user_id(+) = o.user_id;

* FULL OUTER JOIN을 표현할 수 없음
* OR 조건과 함께 사용할 수 없음
* 한 컬럼에 (+)를 여러 테이블에 적용할 수 없음
* 서브쿼리와 함께 사용할 수 없음

-- 3명 × 6상품 = 18행
SELECT u.username, p.name
FROM users u
CROSS JOIN products p;

-- 모든 월 × 모든 부서 조합 생성 (보고서 기본 틀)
SELECT m.month_name, d.dept_name,
       COALESCE(s.total_sales, 0) AS total_sales
FROM months m
CROSS JOIN departments d
LEFT JOIN monthly_sales s ON m.month_id = s.month_id
                          AND d.dept_id = s.dept_id;

-- 0~9999까지의 숫자 테이블 생성
WITH digits AS (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2
  UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
  UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
  UNION ALL SELECT 9)
SELECT a.d * 1000 + b.d * 100 + c.d * 10 + d.d AS num
FROM digits a CROSS JOIN digits b
CROSS JOIN digits c CROSS JOIN digits d;

-- 같은 이름의 컬럼으로 자동 조인
SELECT username, order_id
FROM users
NATURAL JOIN orders;
-- user_id라는 같은 이름의 컬럼으로 자동 매칭

SELECT username, order_id
FROM users
JOIN orders USING (user_id);
-- 조인 컬럼 이름이 같을 때 간결하게 작성

-- 각 부서의 최근 주문 3건만 조인
SELECT d.dept_name, recent.order_id, recent.total
FROM departments d
LEFT JOIN LATERAL (
    SELECT o.order_id, o.total
    FROM orders o
    WHERE o.dept_id = d.dept_id
    ORDER BY o.order_date DESC
    LIMIT 3
) recent ON TRUE;

-- SQL Server에서 동일한 기능
SELECT d.dept_name, recent.order_id, recent.total
FROM departments d
OUTER APPLY (
    SELECT TOP 3 o.order_id, o.total
    FROM orders o
    WHERE o.dept_id = d.dept_id
    ORDER BY o.order_date DESC
) recent;

1. 필요할 때만 OUTER JOIN 사용
   → INNER JOIN으로 충분하면 INNER JOIN을 쓸 것

2. 인덱스 확인
   → 조인 컬럼에 인덱스가 있어야 함
   → 특히 비보존 테이블의 조인 컬럼

3. 불필요한 컬럼 제거
   → SELECT *를 피하고 필요한 컬럼만 명시

4. 조건을 ON에 넣을지 WHERE에 넣을지 신중히 결정
   → 잘못된 위치의 조건은 성능과 결과 모두에 영향

5. FULL OUTER JOIN 대신 UNION 고려
   → 일부 DBMS에서 UNION이 더 효율적