[Database]조인과 집합연산(MySQL)

*조인(Join)

-> 조인이란 둘 이상의 테이블에서 데이터가 필요한 경우, 여러 테이블을 조합하여 데이터를 사용하기 위해 사용되는 연산이다.

-> 기본적으로 RDBMS는 중복 데이터를 최소화하기 위하여 데이터를 쪼개서 저장하기 때문에, 이러한 조인 연산이 필요하다.

 

*MySQL Sample Database

-> 아래 데이터베이스는 MySQL에서 기본적으로 제공해주는 연습용 SQL 테이블 데이터이다. 해당 데이터를 이용하여 쿼리를 연습해보도록 하겠다.

www.mysqltutorial.org/mysql-sample-database.aspx/

MySQL Sample Database

*INNER JOIN

-> 가장 일반적인 조인으로써, 조건에 맞는 테이블의 교집합을 추출해내는 조인이라고 생각하면 이해하기 쉽다.

서로 연관된 내용을 검색하고 출력한다.

※ 조인 조건 : 두 테이블을 연결시키는 기준을 설정한다. 이 기준에 따라서 크게 동등 조인과 비동등 조인으로 조인이 분류되기도 한다.

(1) 동등 조인(EQUI JOIN) : 테이블의 기본키(Primary Key), 외래키(Foreign Key) 관계를 기반으로 테이블을 조합하는 방식이다. 쉽게 말해 서로 같은 값을 갖는 조건을 활용하여 테이블을 조합한다.

-- customer 테이블의 기본키인 customerNumber와 orders 테이블의 외래키인 customerNumber를 이용하여 테이블을 조인
select *
from customers join orders
on customers.customerNumber = orders.customerNumber;

 조인은 테이블을 합치는 과정이기 때문에, 중복되는 열에 대하여 어떤 테이블의 소유인지 명시를 해주어야 한다. 위와 같이 테이블이름을 모두 적어주는 방법도 있지만, 별칭을 활용하는 방법도 사용할 수 있다.

-- customer 테이블의 기본키인 customerNumber와 orders 테이블의 외래키인 customerNumber를 이용하여 테이블을 조인
select *
from customers c join orders o
on c.customerNumber = o.customerNumber;

 

 위와 같이 테이블을 조인하는 경우, 해당 조인 조건에 대응하는 모든 경우의 수를 산출한다. 이를 CROSS JOIN 혹은 Cartesian Product라고도 하며 일반적인 경우에는 모든 경우의 수를 산출하는 경우가 거의 없으므로 자주 사용하지 않는다.

customer 테이블

order테이블

Cartesian Product(Cross Join)결과. 고객의 주문 수에 따라 고객의 정보가 중복되는 것을 확인할 수 있다.

 따라서 보통 INNER JOIN은 WHERE 절과 함께 사용하여 원하는 검색 결과만 조회하는 경우가 많다.

-- 103번 고객의 주문 내역
select c.customerNumber, c.customerName, o.orderNumber, o.orderDate
from customers c join orders o
on c.customerNumber = o.customerNumber
where c.customerNumber = 103;

결과

-- 103번 ~ 120번 고객의 주문 내역
select c.customerNumber, c.customerName, o.orderNumber, o.orderDate
from customers c join orders o
on c.customerNumber = o.customerNumber
where c.customerNumber between 103 and 120;

결과

 만일, 두 테이블에 대하여 같은 컬럼명을 갖는 모든 열에 대한 동등 조인 결과 값을 검색하고 싶다면, NATURAL JOIN을 사용하면 된다. MySQL 데이터 베이스에는 해당 조건을 보여 줄만한 예제 데이터가 존재하지 않지만, 예를 들어 A반과 B반 학생 각각의 국영수 성적을 담은 테이블이 있다고 할 때, A, B반에서 모든 성적이 똑같은 학생을 확인해보고자 할때 NATURAL JOIN을 활용할 수 있다. 사실상 사용할 일이 거의 없다.

 

(2) 비동등 조인(NON-EQUI JOIN) : 테이블의 기본키, 외래키외에 다른 column을 사용하여 조인 조건으로 사용하는 조인을 말한다. 

-> 예를 들어 다음과 같은 상품의 등급을 가격에 따라 분류한 테이블이 존재한다고 생각해보자

여기에 비동등 조인을 이용하여 상품별 등급을 출력해줄 수 있다.

-- 각 상품의 퀄리티를 다음과 같은 조인 조건을 만족하며 조합하라.
select productCode, productName, buyPrice, grade
from products join quality
on buyPrice >= low and buyPrice <= high
order by buyPrice;

위의 기준과 같이 상품의 등급이 분류되었음을 확인할 수 있다.

 

*OUTER JOIN

-> INNER JOIN의 경우 교집합을 추출하는 방식이기 때문에, 다른 쪽 테이블에 데이터가 존재하지 않는 경우 해당 데이터는 고려하지 않는 문제점이 존재한다. 이를 해결하기 위해 사용할 수 있는 것이 OUTER JOIN이다.

-> 예를 들어 MySQL의 데이터에서 고객(customers)에 대한 담당 직원(employees)이 존재하지 않는 경우가 존재한다.

select customerNumber, customerName, salesRepEmployeeNumber
from customers;

담당 직원이 존재하지 않는 고객

 따라서, 고객 테이블과 직원 테이블을 INNER JOIN하는 경우, Havel & Zbyszek Co에 대한 정보가 사라진 것을 확인할 수 있다.

select customerNumber, customerName, salesRepEmployeeNumber
from employees join customers
on employeeNumber = salesRepEmployeeNumber
order by customerNumber;

 이때, OUTER JOIN을 활용하면 해당 고객도 포함하여 검색 결과를 도출할 수 있다.

select customerNumber, customerName, salesRepEmployeeNumber
from customers left outer join employees
on employeeNumber = salesRepEmployeeNumber
order by customerNumber;

담당직원이 존재하지 않는 경우도 출력해준다.

-> 이처럼 OUTER JOIN은 다음과 같은 벤다이어 그램을 가진다고 생각하면 된다.

 다만 MySQL에서는 FULL OUTER JOIN을 지원하지 않기 때문에, 집합 연산자를 활용하여 FULL OUTER JOIN을 검색 결과를 도출할 수 있다.

 

*SELF JOIN

-> 자기 자신의 테이블에 대해서도 조인이 가능하다. 쉽게 생각하면 자신의 테이블이 2개 존재한다고 생각하고 조인을 진행하면 SELF JOIN이 된다.

-- 직원과 직원의 매니저(보고하는 대상)
select e.employeeNumber, e.lastName, m.employeeNumber as "Manager", m.lastName
from employees e join employees m
on e.reportsTo = m.employeeNumber;

 

*집합 연산자(SET Operator)

-> 앞서 조인은 집합 연산과 유사한 방식으로 동작한다는 사실을 확인할 수 있었다. 마찬가지로, SQL에서는 여러가지 집합 연산을 제공하는데 MySQL의 경우에는 UNION 연산만을 지원한다(그 외의 집합 연산은 여러 연산자 및 조인을 활용하여 구현할 수 있다).

-> UNION 연산과 FULL OUTER JOIN : MySQL에서는 FULL OUTER JOIN을 제공하지 않는다고 하였다. 따라서, LEFT OUTER JOIN과 RIGHT OUTER JOIN을 UNION(합집합)하면, FULL OUTER JOIN을 구현할 수 있다.

select customerNumber, customerName, salesRepEmployeeNumber
from customers left outer join employees
on employeeNumber = salesRepEmployeeNumber
UNION
select customerNumber, customerName, salesRepEmployeeNumber
from customers right outer join employees
on employeeNumber = salesRepEmployeeNumber
order by customerNumber;

-> UNION vs UNION ALL : 이 둘의 차이는 중복되는 행의 제거(distinct)여부 이다. 기본적으로 UNION연산은 합집합 과정에서 중복되는 요소는 제거하면서 두 테이블을 합친다. 반면 UNION ALL연산은 두 테이블의 중복되는 요소를 제거하지 않고 테이블을 합친다.