조인을 원리로 이해하기

-- 취소가 아닌 주문만 붙이되, 주문 없는 고객도 남기고 싶다
-- 올바름: 조인 조건에 넣습니다
SELECT c.customer_nm, o.order_id
FROM   customer c
LEFT JOIN orders o
  ON  o.customer_id = c.customer_id
  AND o.status_cd <> 'CANCEL';

-- 틀림: WHERE로 빼면 아우터 조인이 무력화됩니다
SELECT c.customer_nm, o.order_id
FROM   customer c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE  o.status_cd <> 'CANCEL';  --> 주문 없는 고객은 o.status_cd 가 NULL이라 탈락

아우터 조인에서 오른쪽 테이블 조건을 WHERE에 두면 INNER JOIN과 같아집니다. 실무에서 자주 나오는 실수라 조인 조건과 필터 조건을 의식적으로 구분합니다.

-- 레거시 (+) 구문
SELECT c.customer_nm, o.order_id
FROM   customer c, orders o
WHERE  o.customer_id(+) = c.customer_id
AND    o.status_cd(+) <> 'CANCEL';

-- ANSI 표준
SELECT c.customer_nm, o.order_id
FROM   customer c
LEFT JOIN orders o
  ON  o.customer_id = c.customer_id
  AND o.status_cd <> 'CANCEL';

(+)는 NULL을 채워 넣을 쪽에 붙입니다. 위에서는 orders 쪽 모든 조건에 (+)를 빠짐없이 붙여야 하고, 하나라도 빠뜨리면 INNER JOIN이 됩니다.

-- 1) 조인 + DISTINCT : 중복을 만든 뒤 제거 (비효율)
SELECT DISTINCT c.customer_id, c.customer_nm
FROM   customer c
JOIN   orders o ON o.customer_id = c.customer_id;

-- 2) EXISTS : 첫 짝을 찾으면 멈춤 (권장)
SELECT c.customer_id, c.customer_nm
FROM   customer c
WHERE  EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- 3) IN : 서브쿼리 결과가 작을 때 유리
SELECT c.customer_id, c.customer_nm
FROM   customer c
WHERE  c.customer_id IN (SELECT o.customer_id FROM orders o);

EXISTS 안의 SELECT 목록은 아무 의미가 없어서 관례적으로 1을 씁니다. 무엇을 쓰든 성능 차이는 없습니다.

-- 주문이 한 건도 없는 고객
SELECT c.customer_id, c.customer_nm
FROM   customer c
WHERE  NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- 아우터 조인으로도 가능하지만 의도가 덜 드러납니다
SELECT c.customer_id, c.customer_nm
FROM   customer c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE  o.order_id IS NULL;

Part 2에서 다룬 대로 NOT IN 은 NULL이 섞이면 결과가 통째로 사라집니다. 안티 조인은 NOT EXISTS 를 기본으로 씁니다.

SELECT LEVEL,
       LPAD(' ', (LEVEL - 1) * 2) || dept_nm AS dept_tree,
       SYS_CONNECT_BY_PATH(dept_nm, '/') AS full_path
FROM   department
START WITH parent_id IS NULL      -- 최상위에서 시작
CONNECT BY PRIOR dept_id = parent_id  -- 부모의 id가 자식의 parent_id
ORDER SIBLINGS BY dept_nm;

LEVEL 은 현재 깊이, PRIOR 는 부모 행을 가리킵니다. PRIOR 를 반대로 붙이면 자식에서 부모로 거슬러 올라가는 조회가 됩니다.