[SQL] 표준 SQL의 조인 연산 - FROM절 JOIN, 재귀 JOIN

FROM절 조인은 WHERE절 조인과 다르게 Inner Join, Natural join, Outer join, Cross join이 모두 가능하다. 재귀 JOIN은 순환적 관계를 이용해, 한 테이블에 적용하는 조인이다. 가장 대표적으로 직원 직원의 상급자를 검색하는 쿼리가 있다.

 

SQL 조인

 

SQL FROM절 조인

 

 

여기서 자연조인(Natural join)은 두 테이블의 동일명 컬럼을 등가조인 해주는 연산이고, 외부 조인(Outer Join)은 조인에서 한쪽의 데이터가 없는 값까지 출력해 주는 연산, Cross join은 카티션 프로덕트 연산자이다.

 

FROM절 조인은 대표적으로 using을 사용하거나 on을 사용하는 방법이 있는데 using은 두 테이블에서 똑같은 이름의 컬럼을 기준으로 조인할 때만 사용할수 있고, on은 두 테이블의 조인할 컬럼의 이름이 달라도 조인할 수 있다.

 

예제1

 

SELECT o.city, c.country, count(c.customerId)
FROM s_offices o
    JOIN s_employees e using(officeCode)
    JOIN s_customers c on employeeId = salesRepId
GROUP BY o.city, c.country
ORDER BY o.city, c.country

 

s_offices 테이블과 s_employees 테이블을 조인할 때는 똑같은 이름의 컬럼이 있어서 using을 사용했고,

다시 s_custromers 테이블과 조인할 때는 조인할 기준컬럼의 이름이 달라 on을 사용하였다. 물론 조인할 컬럼의 이름이 같아도 on을 사용할 수 있다. 다만 이때는 얼리어스를 사용해 주어야 한다.

 

 

예제 2

 

먼저 위 문제를 해결하기 위해 총 6개의 테이블을 조인해야 해서 5번의 조인 연산을 사용해야 한다. 또한 주문 횟수가 많은 상위 50위까지의 상품을 검색하기 위해서 윈도우 함수 rank() 함수를 사용해 해결하기로 하였다.

 

    SELECT p.productCode '상품코드', p.name '상품명', COUNT(d.quantity * od.orderNo) '주문횟수', 
           RANK() OVER (ORDER BY COUNT(d.quantity * od.orderNo) DESC) '순위'
    FROM s_offices o
    JOIN s_employees e using(officeCode)
    JOIN s_customers c on employeeId = salesRepId
    JOIN s_orders od using(customerId)
    JOIN s_orderdetails d using(orderNo)
    JOIN s_products p using(productCode)
    WHERE YEAR(od.orderDate) = 2004 and RANK() OVER (ORDER BY COUNT(d.quantity * od.orderNo) DESC) <= 50
    GROUP BY p.productCode 
    ORDER BY 4, 2

WHERE 절에 RANK() 함수를 사용하는 순간 에러가 났다. 

 

SELECT SubQuery.상품코드, SubQuery.상품명, SubQuery.주문횟수, SubQuery.순위
FROM (
    SELECT p.productCode '상품코드', p.name '상품명', COUNT(d.quantity * od.orderNo) '주문횟수', 
           RANK() OVER (ORDER BY COUNT(d.quantity * od.orderNo) DESC) '순위'
    FROM s_offices o
    JOIN s_employees e using(officeCode)
    JOIN s_customers c on employeeId = salesRepId
    JOIN s_orders od using(customerId)
    JOIN s_orderdetails d using(orderNo)
    JOIN s_products p using(productCode)
    WHERE YEAR(od.orderDate) = 2004 
    GROUP BY p.productCode
) SubQuery
WHERE SubQuery.순위 <= 50
ORDER BY SubQuery.순위, SubQuery.상품명;

 

그래서 어쩔 수 없이 서브쿼리를 사용하였다. 이렇게 하면 실제로 메인 쿼리에서는 윈도우 함수를 사용하지 않았지만, 서브 쿼리를 통해서 윈도우 함수를 사용한 것과 같은 효과를 만들어 낼 수 있었다. 이는 다른 많은 상황에서도 응용할 수 있는 매우 유용한 방법인 것 같다.

 

 

다음은 외부 조인 (Outer join)에 대해 생각해 볼 건데, 사실 어렵게 생각할 것 없이 '남기고 싶은 데이터가 있는 쪽 방향으로 조인'을 하면 된다. 즉, 고객과 주문 테이블을 조인한다고 했을 때, 일반적인 조인 연산을 하면 주문을 하지 않은 고객은 결과에 나타나질 않는다. 주문을 하지 않은 고객까지 모두 출력하고 싶다면, 고객 (LEFT JOIN) 주문의 형태로 쿼리를 작성하면 된다.

 

 

 

예제 3

 

SELECT o.city "근무지점", concat(e.firstName, ' ',e.lastName ) "성명" ,
    sum(d.priceEach * d.quantity) '주문액 합계'
FROM s_offices o
    JOIN s_employees e USING (officeCode)
    LEFT JOIN s_customers c ON salesRepId = employeeId
    LEFT JOIN s_orders od USING (customerId)
    LEFT JOIN s_orderdetails d USING (orderNo)
GROUP BY e.employeeId
ORDER BY 3 DESC, 2

 

위의 코드를 보면, 직원의 정보를 남기고 싶어서 직원과 고객을 레프트 조인 한 뒤에 계속해서 레프트 조인을 해 s_employees테이블(직원 정보 테이블)을 끌고 가는 것을 알 수 있다.

 

 

또한 outer join을 할 때, 주의해야 할 점이 있다. outer join은 해당 정보가 null 값이라고 해도 살리고 싶을 때 쓰는 방법이다. 예를 들어 inner join은 두 테이블을 어떤 컬럼 기준으로 조인할 때 양쪽 컬럼에 만족하는 값이 한쪽에라도 없으면 결과를 출력하지 않는다. 하지만 outer join은 한쪽에 null 값이 나오더라도 결과를 출력하고 싶을 때 사용한다. 예를 들면 주문을 하지 않은(주문 관련 컬럼이 null임) 고객의 정보도 출력하고 싶다는 것이다.

이때 WHERE절을 사용하면 문제가 생길수 있다. 주문 관련 컬럼이 null인 값을 포함해서 조인한 결과에 주문과 관련해서 WHERE절을 사용하면 null인 데이터들은 모두 WHERE절에 의해 사라져, outer join을 한 이유가 사라지게 된다.

 

이에 두 가지 방법을 사용할 수 있는데 하나는 서브쿼리를 통해 먼저 where절을 적용해 원하는 값들을 뽑아낸 이후에 메인 쿼리에서 outer join을 사용하면 된다.

 

두번째 방법은 FROM절을 통해 join을 할 때 AND로 조건을 함께 묶어주는 것이다. 즉, outer join을 사용할때 where절도 함께 적용하는 것이다.

 

 

예제 4

 

SELECT c.name '성명', concat(e.firstName, ' ', e.lastName) '성명', IFNULL(sum(p.amount), 0) '결재액'
FROM s_employees e
    RIGHT JOIN s_customers c ON employeeId = salesRepId
    LEFT JOIN s_payments p on (c.customerId = p.customerId AND YEAR(p.paymentDate) = 2004)

GROUP BY c.name, e.firstName, e.lastName
ORDER BY c.name ;

이렇게 on을 통해 join을 할 때 AND를 통해 조건을 함께 적어주면, 원하는 대로 outer join을 사용할 수 있다.

 

 

SQL 재귀 JOIN

SELECT	CONCAT(mgr.firstName, ' ', mgr.lastName) AS 성명, 
	mgr.jobTitle AS 직책, 
	COUNT(emp.employeeId) AS 부하직원수,
    GROUP_CONCAT(CONCAT(emp.firstName, ' ', emp.lastName ) ORDER BY CONCAT(emp.firstName, ' ', emp.lastName )  SEPARATOR ', ') '부하직원 명단'
FROM 	s_employees mgr 
LEFT JOIN s_employees emp ON mgr.employeeId = emp.managerId
GROUP 	BY mgr.employeeId
ORDER 	BY 1 ;

출력 결과

재귀 조인은 하나의 테이블 내부에 순환적인 구조를 활용할 때 사용한다. 예를 들어 직원테이블에서는 employeeId 컬럼과 managerId 컬럼이 있는데 두 컬럼을 재귀조인하여 직원들과 각 직원의 직속상관 간의 관계를 활용해 하나의 테이블에서 각 직원이 어떤 관계를 맺고 있는지 확인할 수 있다. 이때 하나의 테이블이라 할지라도 조인하기 위해선 구분이 필요한데 얼리어스를 통해 s_emplyees테이블 하나를 mgr, emp로 두 테이블처럼 설정할 수 있고, mgr과 emp를 조인하여 사용한다.