Database/SQL

고급 테이블 조인 생성

onddd 2023. 9. 29. 17:21
728x90

목표

  • 외부 조인에 대해 학습한다.

목차

  1. [테이블 별칭 사용하기](#테이블 별칭 사용하기)
  2. [다른 조인 타입](#다른 조인 타입)
  3. [그룹 함수와 조인](#그룹 함수와 조인)
  4. [조인과 조인 조건](#조인과 조인 조건)

테이블 별칭 사용하기

SQL에서는 열 이름과 계산 필드 그리고 테이블 이름에도 별칭을 사용할 수 있다.
테이블 이름에 별칭을 사용하는 이유는 크게 두 가지다.

  • 사용하는 SQL 명령문의 수를 줄이기 위해
  • 하나의 SELECT 문 내에서 같은 테이블을 여러 번 사용하기 위해
SELECT cust_name, cust_contact 
FROM Customers AS C, Orders AS O, OrderItems AS OI 
WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';

위 예제에서는 WHERE 절에서 테이블 별칭을 사용했지만, WHERER 절에서만 사용할 수 있다는 의미는 아니다. SELECT 문의 ORDER BY절이나 그 외 다른 절에서도 테이블 별칭을 사용할 수 있다.

[!NOTE] Oracle에서는 AS를 쓸 수 없다.
Oracle에서는 테이블에 별칭을 붙일 때 AS 키워드를 지원하지 않는다.
즉, Customers AS C가 아니라 Customers C로 사용한다.

테이블 별칭은 쿼리가 수행되는 동안에만 사용할 수 있다. 열 별칭과 달리 테이블 별칭은 클라이언트에게 절대 반환될 일이 없기 때문이다.

다른 조인 타입

셀프 조인(Self Join)

특정 인물과 같은 회사에서 일하는 모든 직원에게 메일을 보내려고 한다면 먼저 해당 인물이 어느 회사에 일하는지 알아내는 쿼리가 필요하고, 그 다음 그 회사에 일하는 직원 연락처를 알아내는 쿼리가 필요하다.

 

이를 해결하기 위해 서브 쿼리 혹은 조인을 사용할 수 있다.

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

이 쿼리에서 필요한 두 테이블은 실제로는 같은 테이블이다. 그렇기 때문에 FROM 절에서 같은 테이블이 두 번 나타난다.
문법적으론 문제가 없지만 DBMS 입장에선 어떤 테이블을 참조해야 할지 혼란이 생길 수 있기 때문에 별칭을 사용한다.

  • 두 테이블이 같을지라도 동일한 열 이름이 두 개씩 존재하기 때문에 에러가 발생한다.
  • WHERE 절에서 테이블을 먼저 조인하고, 그 다음 원하는 데이터만 가져오기 위해 두 번째 테이블에서 데이터를 필터링한다.

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

자연 조인(Natural Join)

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

 

자연 조인은 여러 번 반복되는 열을 제거하여 각 열이 한 번만 반환되는 것을 말한다.

일반적으로 중복되는 열을 제거하는 방법은 DBMS에서 처리하지 않고, 직접 중복되는 열이 없도록 쿼리를 만드는 것이다. 한 테이블에서는 와일드카드(SELECT *)를 사용하고, 다른 테이블은 가져올 열을 명시하여 만든다.

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

와일드카드가 첫 번째 테이블에만 사용되었다. 나머지 모든 열은 열 이름으로 나열하였기 때문에 중복된 열은 가져오지 않는다.

외부 조인(Outer Join)

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

다음과 같은 작업을 수행할 때 조인을 사용할 수 있다.

  • 고객의 주문 수량을 계산할 때 아직 주문하지 않은 고객도 포함한다.
  • 제품과 주문 수량을 함께 나열하며, 아무도 주문하지 않은 제품도 포함한다.
  • 주문하지 않은 고객도 고려하여, 평균 세일 규모를 계산한다.

위 세가지 작업을 수행하려면 연결된 테이블에서 관련이 없는 행을 가져와야 하고 이는 외부 조인을 통해 수행할 수 있다.

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

[!NOTE] 외부 조인 유형
외부 조인에는 두 가지 기본적인 형태인 왼쪽 외부 조인과 오른쪽 외부 조인이 있고,
이 두가지의 차이점은 관련된 테이블의 순서이다.
FROM 절이나 WHERE 절에서 테이블의 순서를 바꿔 간단히 왼쪽 외부 조인을 오른쪽 외부 족인으로 바꿀 수 있다.

조인에는 다른 종류가 하나 더 있는데, 전체 외부 조인이다. 전체 외부 조인은 두 개의 테이블에서 모든 행을 가져오고 관련된 행은 연결한다. 전체 외부 조인은 두 개의 테이블 모두에서 관련되지 않은 행을 포함할 수 있다.

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

# 전체 외부 조인은 MariaDB, MySQL, SQLite에서는 지원하지 않음

그룹 함수와 조인

그룹 함수는 조인과 함께 사용할 수 있다.

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

이 SELECT 문은 Customers와 Orders 테이블을 연결하기 위해 내부 조인을 사용했다.
GROUP BY 절에서 고객으로 그룹핑하기 때문에 COUNT(Order.order_num)를 이용해 각 고객이 주문한 수량을 셀 수 있고,

그 수량은 num_ord로 반환된다.

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

조인과 조인 조건

아래는 조인을 사용하는데 있ㅎ어서 몇 가지 중요한 포인트를 요약한 내용이다.

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

추가 예제

# INNER JOIN  
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord 
FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id 
GROUP BY Customers.cust_id;
# LEFT OUTER JOIN  
SELECT Customers.cust_name, Orders.order_num 
FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id 
ORDER BY cust_name;
# LEFT OUTER JOIN & ORDER BY  
SELECT prod_name, order_num 
FROM Products LEFT OUTER JOIN OrderItems ON Products.prod_id = OrderItems.prod_id 
ORDER BY prod_name;
# LEFT OUTER JOIN & GROUP BY, ORDER BY, GROUP FUNCTION 
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;  

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