테이블 조인

목표

• 조인이 무엇이고 어떻게 사용할 수 있는지 알아본다.

목차

1. 조인_이해하기
2. 조인_생성하기

조인_이해하기

• SQL의 유용한 기능 가운데 하나는 데이터 검색 쿼리에서 바로 테이블 조인이 가능하다는 점이다.
• 조인을 효과적으로 사용하려면, 관계형 테이블과 관계형 데이터베이스 디자인의 이해가 필요하다.

관계형 테이블 이해하기

제품 목록을 저장하는 데이터베이스 테이블이 있다고 가정하고, 하나의 행 마다 제품을 저장한다.
일반적으로 저장하길 원하는 정보는 제품에 대한 설명, 가격, 제품을 생산하는 판매처 정보이고 이제 한 판매처에서 여러 개의 제품을 만들었다고 가정한다.

회사명, 주소, 담당자 정보 등의 판매처 정보는 어디에 저장해야 할까?

• 한 회사가 다수의 제품을 생산한 경우, 같은 패마처 정보를 제품마다 반복해 저장하는 것은 저장 공간과 시간의 낭비이다.
• 판매처 정보가 변경되면, 모든 판매처 정보를 업데이트해야 한다.
• 데이터가 반복되면 매번 정확히 같은 방식으로 삽입되지 않을 가능성이 커진다. 불일치한 데이터는 보고하는 데 사용하기 어렵다.

여기서 중요한 점은 같은 데이터를 여러 곳에 저장하는 것은 불필요한 일이라는 것이고,

이는 관계형 데이터베이스의 디자인의 기본 원리이다.

 

관계형 테이블은 정보를 쪼개 여러 개의 테이블에 저장하도록 설계되었다. 테이블은 공통 열을 통해 연결된다.

테이블과 테이블을 연결하는데 사용되는 고유한 식별자가 존재하고 이런 값을 키본 키(Primary Key)라고 부른다.

 

즉, 기본 키를 통해 테이블간 연결 할 수 있고, 이로 인해 각 테이블은 중복되지 않은 데이터를 저장할 수 있게 되는 것이다.

이렇게 사용해서 얻는 이득은 다음과 같다.

• 정보가 절대 반복되지 않기 때문에 시간과 공간이 낭비되지 않는다.
• 정보가 변경되더라도 해당 테이블에 있는 하나의 행만 업데이트하면 된다. 관련된 테이블에 있는 데이터는 변경되지 않는다.
• 데이터가 반복되지 않기 때문에 사용한 데이터는 늘 일관성을 갖는다. 그렇기 때문에 데이터 보고와 조작이 더 간단해진다.

중요한 것은 관계형 데이터는 효율적으로 저장되고 쉽게 조작할 수 있다는 점이다.

이 때문에 관계형 데이터베이스가 비관계형 데이터베이스보다 확장성(Scale)이 훨씬 좋다.

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

 

왜 조인을 사용할까?

간단히 말해서 조인은 SELECT 문 안에서 테이블을 연결할 때 사용하는 메커니즘이다. 특별한 문법을 사용해서 여러 개의 테이블을 조인하면, 하나의 결과를 가져올 수 있다.

조인은 각 테이블에서 적절한 행을 서로 연결하는 역할을 한다.

[!NOTE] 대화형 DBMS 도구 사용하기
조인이 물리적인 객체(entity)가 아니라는 것을 이해해야 한다.
즉, 조인은 데이터베이스 테이블에는 실제로 존재하지 않는다는 의미이다.
조인은 DBMS에서 필요할 때 생성하고, 쿼리가 수행되는 동안에만 유지된다.
많은 DBMS가 테이블 관계를 쉽게 정의할 수 있도록 그래픽 인터페이스를 제공하고,
이 도구는 참조 무결성을 유지하는 데 유용한 도움을 준다.

관계형 테이블에서는 유효한 데이터만이 관계형 열에 삽입될 수 있다는 점이 중요한다.
참조 무결성은 DBMS가 데이터 무결성 규칙을 따른다는 것을 의미한다.
보통 DBMS에서 제공하는 인터페이스를 통해 이런 규칙을 관리할 수 있다.

 

조인_생성하기

조인을 생성하는 것은 매우 간단하다.

SELECT vend_name, prod_price, prod_name 
FROM Vendors, Products 
WHERE Vendors.vend_id = Products.vend_id;

SELECT 문에서 가져오려는 열을 명시하면서 시작하는건 동일하지만, 명시된 열 가운데 두 개는 같은 테이블에 있고, 하나는 다른 테이블에 존재한다. FROM 절 역시 두 개의 테이블이 있다.

 

두 테이블은 SELECT 문에서 조인한 테이블 이름으로 WHERE 절에서 두 테이블에 있는 기본 키를 매치 시키기 때문에 이 두 테이블이 조인된다. 완전한 열 이름을 사용했는데, 이는 두 테이블에 동일한 열 이름이 존재하기 때문에 DBMS가 에러를 발생할 수 있기 때문이다.

WHERE 절의 중요성

조인 관계를 설정하기 위해 WHERE 절을 이용하는 게 이상해 보일 수 있다.
데이터베이스 테이블의 정의에는 테이블을 어떻게 조인할지에 대한 내용이 아무것도 없기 때문에 사용자가 명시해야하는 부분이다.

 

두 개의 테이블을 조인할 때, 할 일은 첫 번째 테이블의 행과 두 번째 테이블을 행으로 짝을 짓는 것이다.
WHERE 절은 필터로 동작해 지정한 조건과 일치하는 행만 가져온다. WHERE 절이 없다면 논리적으로 맞는지와 관계 없이 첫 번째 테이블에 있는 모든 행은 두 번째 테이블에 있는 모든 행과 짝이 된다.

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

카티전 곱을 원하는 경우는 매우 드물다.
WHERE 절을 잊지 말자 조인을 사용할 때 WHERE 절이 있는지, 올바른지 항상 확인하자
잘못된 필터 조건은 DBMS가 엉뚱한 데이터를 가져오게 하고, 대용량 테이블에서는 카티전 곱이 발생하는 경우
sql 명령문의 처리 속도가 현저히 저하되므로 주의가 필요하다.

[!info]
카티전 곱을 반환하는 조인 타입을 상호 조인(Cross Join)이라고도 부른다.

내부 조인

위 예시에서 살펴본 조인은 동등 조인 혹은 이퀴 조인(Equi-Join)이라고 하고는데, 이 조인은 두 개의 테이블에 있는 공통 열의 값이 같은 것을 결과로 가져온다. 이런 종류의 조인을 내부 조인(Inner Join)이라고도 부른다.

조인 타입을 표시할 때는 이전과 조금 다른 문법을 사용해야 한다.

SELECT vend_name, prod_name, prod_price 
FROM Vendors INNER JOIN Products 
ON Vendors.vend_id = Products.vend_id;

두 테이블 간의 관계를 FROM 절에서 INNER JOIN으로 명시했다. 이 문법을 사용할 때는 WHERE 절 대신 ON 절을 사용하여 조인 조건을 지정한다.

[!NOTE] ‘올바른’ 문법
DBMS는 두 개의 문법을 모두 지원하지만 ANSI SQL 규격에서는 간단한 동등 조인 문법보다는 내부 조인 문법을 권한다.

여러 개의 테이블 조인하기

• SQL SELECT 문에서는 조인할 수 있는 테이블의 수를 제한하지 않는다. (SQL 자체에서는 조인하는 테이블 수에 제한을 두지 않지만, 많은 DBMS에서는 제한을 두고 있다.)
  여러 개의 조인을 생성할 때도 기본 규칙은 같다. 먼저 조인할 모든 테이블을 적고, 테이블 간의 관계를 정의한다.

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

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 cust_name, cust_contact 
 FROM Customers, Orders, OrderItems 
 WHERE Customers.cust_id = Orders.cust_id 	
 AND OrderItems.order_num = Orders.order_num     
 AND prod_id = 'RGAN01';

서브 쿼리를 이용해 복잡하게 데이터를 가져오던 방식을 개선해 조인을 통해 간단하게 테이블을 연결하고, 데이터를 가져올 수 있다.

SQL 연산을 수행하기 위한 방법은 보통 한 가지 이상 존재한다. 그리고 방법에 대한 절대적인 옳고 그름은 없다.

사용하는 연산의 유형, DBMS의 종류, 테이블에 있는 데이터의 양, 인덱스나 키의 존재 여부, 다른 조건 등에 따라 성능이 좌우되므로 최적의 동작을 찾기 위해 여러 방식으로 실험해보는 것이 좋다.

도전_과제

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;   

SELECT cust_name,Orders.order_num, SUM(item_price * quantity) AS OrderTotal 
FROM Customers, Orders, OrderItems 
WHERE Customers.cust_id = Orders.cust_id 
AND OrderItems.order_num = Orders.order_num
GROUP BY cust_name, Orders.order_num 
ORDER BY cust_name, order_num;  

SELECT cust_id, order_date 
FROM OrderItems, Orders 
WHERE OrderItems.order_num = Orders.order_num 
AND OrderItems.prod_id = 'BR01' 
ORDER BY order_date;  

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';   

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;

---