조인 이해하기
- SQL 유용한 기능 중 하나는 데이터 검색 쿼리에서 바로 테이블 조인이 가능하다는 점이다.
- 조인은 SQL SELECT 문을 사용하여 수행할 수 있는데, SQL을 학습할 때 조인과 조인 문법을 잘 이해하는 것은 아주 중요하다.
- 조인을 효과적으로 사용하려면, 먼저 관계형 테이블과 관계형 데이터베이스 디자인을 이해해야 한다.
관계형 테이블 이해하기
관계형 데이터 베이스 디자인의 기본원리
- 같은 데이터를 여러 곳에 저장하는 것은 좋지 않다.
- 관계형 테이블은 정보를 쪼개 여러 개의 테이블에 저장하도록 설계되었다.
- 테이블은 공통 열을 통해 연결된다.
- 유효한 데이터만이 관계형 열에 삽입될 수 있다.
EX)
| 판매처 정보 (Vendors) 테이블 | |
|---|---|
| 판매처1 | 판매처 ID = 식별자1 |
| 판매처2 | 판매처 ID = 식별자2 |
| 판매처3 | 판매처 ID = 식별자3 |
각 행은 판매처를 구별할 수 있는 고유한 식별자를 갖는다. 이런 값을 기본키(Primary Key)라고 부른다.
여기서는 판매처 ID를 기본 키로 사용했다.
| 제품 정보 (Products) 테이블 | |
|---|---|
| 제품 정보1 | 판매처 ID = 식별자1 |
| 제품 정보2 | 판매처 ID = 식별자2 |
| 제품 정보3 | 판매처 ID = 식별자3 |
판매처 ID는 Vendors 테이블과 Products 테이블을 연결하는데, 이 키를 이용해 Vendors 테이블에서 제품 정보를 찾을 수 있다.
- 판매처 정보가 절대 반복되지 않기 때문에 시간과 공간이 낭비되지 않는다.
- 판매처 정보가 변경되더라도 Vendors 테이블에 있는 하나의 행만 업데이트 하면 된다.
관련된 테이블에 있는 데이터는 변경되지 않는다. - 데이터가 반복되지 않기 때문에 사용한 데이터는 늘 일관성을 갖는다. 그렇게 때문에 데이터 보고와 조작이 더 간단해진다.
왜 조인을 사용할까?
조인은 SELECT 문 안에서 테이블을 연결할 때 사용한다.
특별한 문법을 사용해서 여러 개의 테이블을 조인하면, 하나의 결과를 가져올 수 있다.
조인은 각 테이블에서 적절한 행을 서로 연결하는 역할을 한다.
=> 조인은 데이터베이스 테이블에서 실제로 존재하지 않는다. DBMS에서 필요할 때 생성하고, 쿼리가 수행되는 동안에만 유지된다.
조인 생성하기
WHERE 절의 중요성
두 개의 테이블을 조인할 때, 할 일은 첫 번째 테이블의 행과 두 번째 테이블을 행으로 짝을 짓는 것이다. WHERE 절은 필터로 동작해 지정한 조건과 일치하는 행만 가져온다. WHERE 절이 없다면 첫 번째 테이블에 있는 모든 행은 두 번째 테이블에 있는 모든 행과 짝이 된다.
따라서 조인을 사용할 때 WHERE 절이 있는지 꼭 확인하자.
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;여기서 사용한 조인은 동등 조인 혹은 이퀴 조인(Equi-Join)이라고 하는데, 이 조인은 두 개의 테이블에 있는 공통 열의 값이 같은 것을 결과로 가져온다. 이런 종류의 조인을 내부 조인(Inner Join)이라고도 부른다.
조인 타입을 표시할 때는 다른 문법을 사용해야 한다.
내부 조인(Inner Join)
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;여러 개의 테이블 조인하기
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;✔️ 성능에 대한 고려
테이블을 연결하는 조인은 많은 테이블을 조인할 수록 성능이 저하되므로 불필요한 테이블을 조인하지 않도록 주의하자.
도전 과제
- Customers 테이블에서 고객명(cust_name)과 Orders 테이블에서 관련된 주문 번호(order_num)를 가져와, 그 결과를 고객명 그리고 주문 번호순으로 정렬하는 SQL문을 작성하라.
실제로 간단한 이퀴 조인 문법을 사용하여 한 번, 내부 조인을 사용하여 한 번 더 작성해보자.
-- 이퀴 조인
SELECT cust_name, order_num
FROM Customers, Orders
WHERE Customers.cust_id = Orders.cust_id
ORDER BY cust_name, order_num;
-- 내부 조인
SELECT cust_name, order_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name, order_num;- 고객명과 주문 번호 외 OrderTotal이라는 이름의 세 번째 열을 추가해 보자. OrderTotal에는 각 주문의 총가격이 포함되어 있다. 이를 위해 2가지 방법이 있다.
OrderItems테이블에서 서브쿼리를 사용해 OrderTotal 열을 생성하는게 첫 번째 방법이다.
두 번째는 기존 테이블에 OrderItems 테이블을 조인한 다음 그룹 함수를 사용할 수 있다.
완전한 열 이름 사용이 필요할 수도 있다는 점에 유의하자.
⭐열 이름이 같을 경우 오류가 나므로 완전한 열 이름을 사용하자⭐
-- 서브쿼리
SELECT cust_name, order_num, ( SELECT SUM(quantity * item_price)
FROM OrderItems
WHERE Orders.order_num = OrderItems.order_num ) AS OrderTotal
FROM Customers, Orders
WHERE Customers.cust_id = Orders.cust_id
ORDER BY cust_name, order_num;
-- 그룹 함수
SELECT cust_name, Orders.order_num, SUM(quantity * item_price) AS OrderTotal
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num
GROUP BY cust_name, Orders.order_num
ORDER BY cust_name, order_num;- 11장에 있는 도전 과제 2를 다시보자. 제품 BR01이 주문된 날짜를 가져오는 SQL문을 작성하라. 이번에는 조인과 간단한 이퀴 조인 문법을 사용한다. 결과는 11장과 같아야 한다.
SELECT cust_id, order_date
FROM Orders, OrderItems
WHERE Orders.order_num = OrderItems.order_num
AND prod_id = 'BR01'
ORDER BY order_date;- 11장 도전 과제 3을 ANSI 내부 조인 문법을 사용해 다시 작성해 보자. 도전 과제 3에서 작성한 코드는 2개의 중첩된 서브쿼리를 사용하였다. 다시 만들려면, 이 장 앞 예제에서 설명한 것처럼 두 개의 내부 조인이 있어야 한다. 마지막으로 prod_id로 필터링할 WHERE 절을 잊지 말기 바란다.
(11장 도전 과제3: prod_id가 BR01을 구매한 모든 고객의 이메일주소를 가져오자)
⭐두 개의 내부 조인 사용법을 익혀두자⭐
SELECT cust_email
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
WHERE prod_id = 'BR01';- 조인과 그룹 함수, 그룹핑을 모두 섞어 좀 더 재밌게 만들어보자.
10장으로 돌아가 보면 1000 이상의 값을 가진 주문 번호를 찾는 도전 과제가 있었다.
결과는 유용하긴 했지만, 그 주문한 고객의 이름을 찾으면 실용성이 한층 더 높아질 수 있다.
조인을 사용하여 customers 테이블에서 고객명(cust_name), 그리고 OrderItems테이블에서 모든 주문의 합계를 가져오는 SQL 문을 작성하라. 이 테이블을 조인하려면 Orders 테이블도 포함해야 한다. GROUP BY와 HAVING 절을 사용해야 한다는 것과 고객명으로 정렬하는 것도 잊지 말자. 간단한 이퀴 조인이나 ANSI 내부 조인 문법을 사용해보자.
-- 이퀴 조인
SELECT cust_name, SUM(quantity * item_price) AS sum_total
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num
GROUP BY cust_name
HAVING SUM(quantity * item_price) >= 1000
ORDER BY cust_name;
-- 내부 조인
SELECT cust_name, SUM(quantity * item_price) AS sum_total
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
GROUP BY cust_name
HAVING SUM(quantity * item_price) >= 1000
ORDER BY cust_name;