재귀 조인의 사용

문제

 

 

내가 작성한 코드

 

-- 코드를 입력하세요
SELECT orderNo, status
FROM s_orders
WHERE orderNo = ANY (
    SELECT orderNo
    FROM s_orderDetails
    WHERE (productCode in ('S10_1678')) AND (productCode in ('S10_4698'))
    GROUP BY orderNo
    )

 

이 코드의 결과엔 아무 튜플도 포함되지 않았다. 이유는 무엇일까?

productCode가 두 개의 값 모두인 주문을 찾으려면, 해당 주문의 상세 내역에서 productCode가 두 개의 값 중 하나씩 포함되는지 확인해야 하지만, 내 코드에서 서브쿼리의 WHERE 절이 제대로 작동하고 있지 않은것이다.

 

이에 productCode에 두개의 값이 동시에 포함된 튜플을 찾는 WHERE절을 어떻게 만들까 고민하다가 재귀 조인을 활용하기로 하였다.

 

SELECT o.orderNo, o.status
FROM s_orders o
WHERE o.orderNo IN (
    SELECT od1.orderNo
    FROM s_orderDetails od1
    JOIN s_orderDetails od2 ON od1.orderNo = od2.orderNo
    WHERE od1.productCode = 'S10_1678' AND od2.productCode = 'S10_4698'
)
ORDER BY o.orderNo;

이렇게 orderDetails 테이블을 재귀 조인을 통해 두번 불러온다면 productCode 각각의 특정 값을 모두 포함한 튜플들을 뽑아낼 수 있었다.

 

또다른 문제에 적용해보자

이 문제의 코드 또한 재귀 조인을 통해 작성해 보았다.

SELECT o.city AS 지점,
       COUNT(DISTINCT e1.employeeId) AS 판매담당_직원수,
       COUNT(DISTINCT e2.employeeId) AS 지점_직원수
FROM s_offices o
JOIN s_employees e1 ON o.officeCode = e1.officeCode AND e1.jobTitle = 'Sales Rep'
JOIN s_employees e2 ON o.officeCode = e2.officeCode
GROUP BY o.city
HAVING 판매담당_직원수 >= 3
ORDER BY o.city;

이처럼 재귀 조인은 하나의 테이블의 하나의 컬럼에 걸려있는 두개의 조건을 동시에 만족시키는 값들을 찾아낼때 매우 유용한것 같다. 즉 바로 위의 문제에서는 각 지점별로 포함된 직원의 총 수와 각 지점별로 포함된 직원중 jobTitle이 Sales Rep인 직원의 총 수를 동시에 출력해야 하는데 하나의 컬럼에 묶인 두개의 조건을 재귀 조인을 통해 간단하고 이해하기 쉽게 작성할 수 있었다.