[SQL] 13장. 고급 테이블 조인 생성하기

[손에 잡히는 10분 SQL - 벤 포터 지음, 박남혜 옮김] 책의 학습 후 정리자료입니다.

---

📍 테이블 별칭 사용하기

• SQL 에서는 열 이름과 계산필드 그리고 테이블 이름에도 별칭을 사용할 수 있다.
• 테이블 이름에 별칭을 사용하는 이유는 크게 2가지가 있다.
  • 사용하는 SQL 명령문의 수를 줄이기 위해서
  • 하나의 SELECT 문 내에서 같은 테이블을 여러 번 사용하기 위해서

SELECT cust_name, cust_contact
FROM Customers C, Orders O, OrderItems OI
WHERE C.cust_id = O.cust_id
AND O.order_num = OI.order_num
AND prod_id = 'RGAN01';

• 테이블의 별칭은 쿼리가 수행하는 동안에만 사용할 수 있다.

---

📍 다른 조인 타입 사용하기

---

📌 셀프 조인(Self Join)

• 테이블 별칭을 사용하는 주된 이유는 SELECT 문에서 같은 테이블을 두 번 이상 참조하기 위함이다.

🧷 셀프 조인(Self Join) 예제

• Jim Jones 라는 사람과 같은 회사에서 일하는 모든 직원에게 메일을 보내는 방법

• 서브 쿼리를 이용한 방법

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
				   FROM Customers
				   WHERE cust_contact = 'Jim Jones');

• 셀프 조인을 이용한 방법

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers c1, Customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

• 1개의 SELECT 문 내에서 똑같은 테이블을 참조하는 것은 모호해질 우려가 있다.
• DBMS가 어떤 테이블을 참조해야 하는지 알지 못하기 때문이다.
• 이러한 문제를 해결하기 위해서 테이블의 별칭을 사용한다.

💡 서브쿼리보다는 셀프 조인이 빠르다.

• 같은 테이블에서 데이터를 가져오는 서브쿼리 대신에 종종 셀프 조인이 사용된다. 최종 결과는 같지만, 많은 DBMS에서 서브쿼리로 처리하는 것보다 조인으로 처리하는 속도가 훨씬 빠르다.

---

📌 자연 조인(Natural Join)

• 테이블을 조인할 때, 한 개 이상의 테이블에서 최소한 하나의 열은 있어야 한다(조인을 생성할 때 그 열을 사용한다).
• 표준적인 조인(내부 조인...)은 같은 열이 여러 번 나타나더라도 모든 데이터를 반환한다,
• 자연 조인은 여러 번 반복되는 열을 제거하여 각 열이 한 번만 반환되는 것을 말한다.

SELECT C.*,
       O.order_num,
       O.order_date,
       OI.prod_id,
       OI.quantity,
       OI.item_price
FROM Customers C, Orders O, OrderItems OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';

---

📌 외부 조인(Outer Join)

• 대부분의 조인은 한 테이블의 행과 다른 테이블의 행과 관계가 있지만, 때로는 관련되지 않은 행을 포함해야 할 때도 있다.

🧷 외부 조인(Outer Join) 예제

• 내부 조인(Inner Join)으로 모든 고객 목록과 고객의 주문 내역 호출

SELECT Customers.cust_id, Orders.order_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

• 외부 조인(Outer Join)으로 모든 고객 목록과 고객의 주문 내역 호출

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

• 내부 조인은 두 테이블과 관련 있는 행만 가져오지만, 외부 조인은 관련이 없는 행(cust_id = 1000000002)도 포함한다.
• OUTER JOIN 문법을 사용할 때는 반드시 RIGHT 나 LEFT 키워드를 사용하여 어떤 테이블에 있는 행을 모두 가져올지 지정해야 한다.
• RIGHT 는 OUTER JOIN 기준 오른쪽 테이블을, LEFT 는 왼쪽 테이블을 의미한다.

---

📍 그룹 함수와 조인 사용하기

🧷 그룹 함수와 조인 사용하기 예제

• 모든 고객 목록(cust_id)과 각각의 고객이 주문한 수량(order_num 의 개수)을 호출

• 내부 조인(Inner Join)을 이용한 방법

SELECT Customers.cust_id,
       Count(Orders.order_num) AS ord_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

• 외부 조인(Outer Join)을 이용한 방법

SELECT Customers.cust_id,
       Count(Orders.order_num) AS ord_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

---

📍 조인과 조인 조건 올바르게 사용하기

• 사용할 조인 유형을 신중히 결정하라, 내부 조인을 사용하는 것이 좀 더 수월하겠지만, 외부 조인이 적합할 때도 많다.
• 올바른 조인 조건을 사용했는지 확인하자. 그렇지 않으면 문법이 올바르더라도 잘못된 데이터를 가져올 것이다.
• 조인 조건을 쓰는 것을 잊지 말고 항상 확인하자. 그렇지 않으면 카티전 곱의 결과가 반환될 것이다.
• 하나의 조인에 여러 개의 테이블을 포함하거나 테이블별로 조인 유형을 다르게 할 수도 있다. 문법적으로는 이렇게 사용하는 게 문제가 없고 때로는 유용하지만, 같이 묶어서 테스트하기 전에 각각의 조인을 따로따로 테스트해야 한다. 혹시 문제가 발생했을 때 훨씬 쉽게 해결할 수 있다.

---

📍 도전 과제

1. 내부 조인을 사용해 고객명(Customers 테이블에 있는 cust_name)과 고객의 모든 주문 번호(Orders 테이블 안에 있는 order_num)를 가져오는 SQL 문을 작성하라.

SELECT cust_name, order_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

2. 방금 작성한 SQL 문을 수정하여 주문한 적이 없는 고객까지 포함하여 모든 고객의 목록을 가져오는 SQL 문을 작성하라.

SELECT cust_name, order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

3. 외부 조인을 사용해 Products 와 OrderItems 테이블을 결합하고 제품명(prod_name)으로 정렬된 목록과 연관된 주문 번호(order_num)를 가져오는 SQL 문을 작성하라.

SELECT prod_name, order_num
FROM Products LEFT OUTER JOIN OrderItems
ON Products.prod_id = OrderItems.prod_id
ORDER BY prod_name;

4. 제품별 총주문 수(주문 번호 X)를 가져오는 SQL 문을 도전 과제 3에서 작성한 SQL 문을 수정하여 작성하라.

SELECT prod_name, COUNT(order_num) AS orders
FROM Products LEFT OUTER JOIN OrderItems
ON Products.prod_id = OrderItems.prod_id
GROUP BY prod_name
ORDER BY prod_name;

5. 제품이 없는 판매처를 포함하여 판매처 ID(Vendors 테이블에 있는 vend_id) 목록과 판매처별로 구매 가능한 제품의 수를 가져오는 SQL 문을 작성하라. Products 테이블에서 각 제품의 수를 계산하기 위해 그룹 함수가 필요할 것이다. vend_id 열은 여러 테이블에 나타나므로 해당 열을 참조할 때는 충분한 주의를 기울이자.

SELECT Vendors.vend_id, COUNT(prod_id)
FROM Products RIGHT OUTER JOIN Vendors
ON Products.vend_id = Vendors.vend_id
GROUP BY Vendors.vend_id;