[SQL] 12장. 테이블 조인

김상현·2022년 10월 2일
sql
0

SQL

목록 보기
12/22
post-thumbnail

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

📍 조인 이해하기

  • 조인을 효과적으로 사용하려면 관계형 테이블과 관계형 데이터베이스 디자인을 이해해야 한다.

📌 관계형 테이블 이해하기

  • 같은 데이터를 여러 곳에 중복 저장하는 것은 좋지 못한 설계이다.
  • 관계형 테이블은 정보를 쪼개 여러 개의 테이블에 저장하도록 설계되었다.
  • 쪼개어진 데이터는 테이블의 공통 열을 통해 연결할 수 있다.

🧷 관계형 테이블의 장점

  • 관계형 테이블은 데이터가 절대 반복되지 않기 때문에 시간과 공간이 낭비되지 않는다.
  • 데이터가 변경되더라도 해당 테이블에 있는 하나의 행만 업데이트하면 관련된 테이블에 있는 데이터는 변경하지 않아도 된다.
  • 데이터가 반복되지 않기 때문에 사용한 데이터는 늘 일관성을 갖고 데이터 보고와 조작이 더욱 간단해진다.
  • 위의 이유 때문에 비관계형 데이터베이스보다 관계형 데이터베이스가 확장성(scale)이 훨씬 좋다.

📒 확장성

  • 증가하는 양의 데이터를 적절히 처리하는 것. 잘 설계된 데이터베이스나 응용 프로그럄은 확장성이 좋다고 말한다.

📌 왜 조인을 사용할까?

  • 데이터가 여러 개의 테이블에 저장된 경우 데이터를 가져올 때 사용한다.
  • 간단히 말해서 조인은 SELECT 문 안에서 테이블을 연결할 때 사용하는 키워드이다.
  • 조인은 각 테이블에서 적적한 행을 서로 연결하는 역할을 한다.

💡 대화형 DBMS 도구 사용하기

  • 조인이 물리적인 객체(Entity)가 아니라는 것을 이해해야 한다. 조인은 데이터베이스 테이블에는 실제로 존재하지 않는다. 조인은 DBMS에서 필요할 때 생성하고, 쿼리가 수행되는 동안에만 유지된다.
  • 관계형 테이블에서는 유효한 데이터만이 관계형 열에 삽입될 수 있다는 점이 중요하다.
  • 참조 무결성은 DBMS가 데이터 무결성 규칙을 따른다는 것을 의미한다.

📍 조인 생성하기

  • 조인은 포함하려는 모든 테이블과 각 테이블 사이의 관계를 명시하면 된다.
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

❗️ 완전한 열 이름

  • 참고하는 열을 해석할 때 중복되는 이름이 존재한다면, 테이블 이름과 열 이름을 마침표로 구분하여 모두 적어주는 완전한 열 이름을 사용해야 한다. 대부분의 DBMS는 모호한 쿼리인 경우에 완전한 열 이름을 쓰지 않으면 에러가 발생한다.

📌 WHERE 절의 중요성

  • SELECT 문에서 테이블을 조인할 때, 관계가 그 즉시 생성된다.
  • 데이터베이스 테이블의 정의에는 테이블을 어떻게 조인할지에 대한 내용이 아무것도 없다.
  • 따라서 2개의 테이블을 조인하려면 WHERE 절을 통해 첫 번째 테이블과 두 번째 테이블의 행의 짝을 지어야 한다.
  • WHERE 절은 필터로 동작해 지정한 조건과 일치하는 행만 가져온다.
  • WHERE 절이 없다면 논리적으로 맞는지와 관계없이, 첫 번째 테이블에 있는 모든 행은 두 번째 테이블에 있는 모든 행과 짝이 된다.

📒 카티전 곱(Cartesian Product)

  • 조인 조건 없이 테이블 관계에 의해 반환된 결과. 검색된 행의 수는 첫 번째 테이블의 행수와 두 번재 테이블의 행 수를 곱한 값이다. 카티전 곱은 두 개 이상의 테이블에서 연결 가능한 행을 모두 결합하는 조인 방법으로 WHERE 절에서 조인 조건절을 생략하거나 조인 조건을 잘못 설정해 양쪽 테이블을 연결하는 조건이 하나도 없는 경우에 발생한다.

❗️ WHERE 절을 잊지 말자

  • 조인을 사용할 때 WHERE 절이 있는지 꼭 확인하자. 그렇지 않으면 DBMS는 여러분이 원하는 것보다 훨씬 많은 데이터를 가져올 수 있다.
  • WHERE 절이 올바른지도 항상 확인하자. 잘못된 필터 조건은 DBMS가 엉뚱한 데이터를 가져오게 하는 원인이 된다.
  • 대용랑 테이블에서는 조인 조건절을 생략하여 카티전 곱이 발생한 경우, SQL 명령문의 처리 속도가 현저히 저하되므로 주의해야 한다.

💡 상호 조인

  • 카티전 곱을 반환하는 조인 타입을 상호 조인(Cross Join)이라고 부르기도 한다.

📌 내부 조인

  • FROM Vendors, Products 와 같은 조인은 동등 조인 혹은 이쿼 조인(Equi-Join)이라고 한다. 이 조인은 2개의 테이블에 있는 공통 열의 값이 같은 것을 결과를 가져온다.
  • 이런 종류의 조인을 내부 조인(Inner Join)이라고도 부른다.
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

💡 올바른 문법

  • ANSI SQL 규격에서는 이전에 사용한 간단한 동등 조인 문법보다는 내부 조인 문법을 권한다.

📌 여러 개의 테이블 조인하기

  • SQL은 SELECT 문에서 조인할 수 있는 테이블의 수에 제한을 두지 않는다.
  • 여러 개의 조인을 생성할 때도 기본 규칙은 같다. 먼저 조인할 모든 테이블을 적고, 테이블 간의 관계를 정의한다.
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;

❗️ 성능에 대한 고려

  • DBMS는 프로그램 실행 중에 지정된 테이블을 연결하는 조인을 처리한다.
  • 이런 프로세스는 자원을 매우 많이 소비하기 때문에 불필요한 테이블을 조인하지 않도록 주의를 기울여야 한다.
  • 더 많은 테이블을 조인할수록 성능은 저하된다는 점을 기억하자.

❗️ 조인할 수 있는 테이블의 최대 수

  • SQL 자체에서는 조인하는 테이블의 수에 제한을 두지 않지만, 실제로 많은 DBMS에서는 제한을 두고 있다.

  • 11장에서 RGAN01 제품을 주문한 고객 목록을 가져오는 서브쿼리를 이용한 SQL 문이다.

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id In (SELECT cust_id
                  FROM Orders
                  WHERE order_num IN ( SELECT order_num
                                       FROM OrderItems
                                       WHERE prod_id = 'RGAN01'))
  • 서브쿼리는 복잡한 SELECT 연산을 수행하는데 언제나 효과적인 방법은 아니다.
  • 조인을 이용하면 더욱 효과적인 SQL 문을 작성할 수도 있다.
SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num
AND prod_id = 'RGAN01';

  • WHERE 절의 처음 2개의 조건은 테이블을 연결하고, 마지막 조건은 RGAN01 제품의 데이터를 필터링하기 위해 사용하였다.

💡 실험정신을 기르자.

  • SQL 연산을 수행하기 위한 방법은 보통 한 가지 이상 존재한다.
  • 어떤 방법을 사용할지엔 절대적인 옳고 그름이 없다.
  • 사용하는 연산의 유형, DBMS의 종류, 테이블에 있는 데이터의 양, 인덱스나 키의 존재 여부, 다른 조건 등에 따라 성능이 좌우된다.
  • 어떤 것이 최적으로 동작하는지 알아보기 위해 여러 방식으로 실험해보는 것이 좋다.

📍 도전 과제

  1. 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;

  1. 고객명과 주문 번호 외 OrderTotal 이라는 이름의 세 번째 열을 추가해 보자. OrderTotal 에는 각 주문의 총가격이 포함되어 있다. 이를 위해 2가지 방법이 있다. OrderItems 테이블에서 서브쿼리를 사용해 OrderTotal 열을 생성하는 게 첫 번째 방법이다. 두 번째는 기존 테이블에 Orderitems 테이블을 조인한 다음 그룹 함수를 사용할 수 있다. 완전한 열 이름(Fully Qualified Column Name) 사용이 필요할 수도 있다는 점에 유의하자.
-- 서브 쿼리
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;

  1. 11장에 있는 도전과제 2를 다시 보자, 제품 BR01 이 주문된 날짜를 가져오는 SQL 문을 작성하라. 이번에는 조인과 간단한 이퀴 조인 문법을 사용한다.
SELECT cust_id, order_date
FROM Orders, OrderItems
WHERE Orders.order_num = OrderItems.order_num
AND prod_id = 'BR01'
ORDER BY order_date;

  1. 11장 도전과제 3장을 ANSI 내부 조인 문법을 사용해 다시 작성해 보자. 도전 과제 3에서 작성한 코드는 2개의 중첩된 서브쿼리를 사용하였다. 다시 만들려면, 2개의 내부 조인이 있어야 한다. 마지막으로 prod_id 로 필터링할 WHERE 절을 잊지 말기 바란다.
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 OrderItems.prod_id = 'BR01';

  1. 10장으로 돌아가보면 1000 이상의 값을 가진 주문 번호를 찾는 도전 과제가 있었다. 결과는 유용했지만, 주문한 고객의 이름을 찾으면 실용성이 한층 더 높아질 수 있다. 조인을 사용하여 Customers 테이블에서 고객명(cust_name), 그리고 OrderItems 테이블에서 모든 주문의 합계를 가져오는 SQL 문을 작성하라. 이 테이블을 조인하려면 Orders 테이블도 포함해야 한다(Customers 테이블은 Orders 와는 연관되지만 OrderItems 와는 직접 연관되어 있지 않기 때문이다). GROUP BY 와 HAVING 절을 사용해야 한다는 것과 고객명으로 정렬하는 것도 잊지 말자. 간단한 이퀴 조인이나 ANSI 내부 조인 문법을 사용해보자. 욕심이 난다면 두 방법으로 모두 작성해 보자.
-- 이퀴 조인
SELECT cust_name, SUM(item_price*quantity) AS total_price
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num
GROUP BY cust_name
HAVING SUM(item_price*quantity) >= 1000
ORDER BY cust_name;

-- 내부 조인
SELECT cust_name, SUM(item_price*quantity) AS total_price
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(item_price*quantity) >= 1000
ORDER BY cust_name;

profile
김상현
목적 있는 글쓰기
이전 포스트

[SQL] 11장. 서브쿼리 사용하기

다음 포스트

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

0개의 댓글