[데이터 모델과 SQL] 7. JOIN 연산 - INNER JOIN
쿼리 결과인 결과 셋들에서 공통된 데이터, 결과 셋들 사이의 차이, 그리고 결과 셋들의 합을 구하기 위한 INTERSECT, EXCEPT, UNION 연산에 대해 살펴보았다. 하지만 이들 SQL 집합 연산은 구조가 같은 결과 셋 사이에서만 사용될 수 있다. 즉, 이들 연산을 사용해 구조가 다른 여러 테이블들에서 공통된 테이터를 추출하기는 어렵다.
여러 테이블에서 데이터를 가져오기 위한 일반적인 방법은 JOIN 연산을 사용하는 것이다. JOIN 연산에는 INNER JOIN과 OUTER JOIN이 있다.
· INNER JOIN: INNER의 의미는 'ins', 두 집합에 모두 존재하는 아이템들이라는 뜻이다. 예를 들어 고객 명단 테이블과 주소 및 연락처 테이블에 모두 정보가 있는 고객 정보들만 연결된다. 어느 한 쪽 테이블에 정보가 없다면 해당 정보는 INNER JOIN 결과에는 반영되지 않는다.
· OUTER JOIN: OUTER의 의미는 'outs', JOIN 대상 집합 중 어느 한 쪽에만 데이터가 있는 아이템들 까지도 포함한다는 의미를 가지고 있다. 예를 들어 고객 명단 테이블과 주소 및 연락처 테이블 어느 한 쪽에 정보가 없는 경우에도 '고객 이름 - 주소 및 연락처 정보 없음'의 형태로 결과 셋에 포함된다. OUTER JOIN과 함께 사용되는 LEFT/RIGHT 키워드는 기준 테이블을 무엇(명령어의 왼쪽/오른쪽 테이블)으로 할 것인가 결정하는 키워드다.
● INNER JOIN
이들 중 먼저 INNER JOIN 연산에 대해 살펴보자.
아래와 같은 구조를 가진 두 테이블이 있다.
employees = {employeeID, empFirstName, empLastName, empHiredDate, departmentID}
departments = {departmentID, departmentName}
두 테이블에서 departmentID는 공통 항목이므로 이를 기준으로 두 테이블을 아래와 같이 하나로 합칠 수 있다.
employeesdepartments = {employeeID, empFirstName, empLastName, empHiredDate, departmentName}
위 과정을 SQL의 INNER JOIN 연산을 이용해서 아래와 같이 수행할 수 있다.
SELECT employeeID, empFirstName, empLastName, departments.departmentID, deparmentName FROM employees INNER JOIN departments ON employees.departmentID=departments.departmentID;
INNER JOIN 구문에서 ON 키워드에는 대상 테이블들을 결합할 조건을 지정한다. 위의 예에서는 employees 테이블과 departments 테이블에 공통으로 포함되어 있는 departmentID 항목을 기준으로 두 테이블을 합칠 것을 지정하였다.
INNER JOIN 구문의 기본적인 문법은 아래와 같다.
문법: SELECT <컬럼, 표현식 등> FROM <대상 테이블 1 이름> INNER JOIN <대상 테이블 2 이름> ON <대상 테이블 1.매칭 컬럼>=<대상 테이블 2.매칭 컬럼>
만약 결합 대상이 되는 두 테이블의 매칭 컬럼 이름이 같다면 USING 키워드를 이용해서 매칭시킬 수 있다. 하지만 매칭 조건을 보다 명확하게 지정할 수 있는 ON 키워드 사용을 추천한다.
SELECT employeeID, empFirstName, empLastName, departments.departmentID, deparmentName FROM employees INNER JOIN departments USING departmentID;
INNER JOIN 연산은 방향이 없다. 무슨 말인가 하면 A INNER JOIN B와 B INNER JOIN A는 서로 같다.
● 테이블의 별칭
테이블의 별칭을 설정해서 테이블 이름을 대신할 수 있다. 테이블 별칭을 SQL 표준에서는 Correlation name이라고 부르고 일반적으로는 Alias라고 부른다. 테이블 이름이 긴 경우 유용하다.
문법: ... FROM <테이블 이름> AS <테이블 별칭> INNER JOIN <테이블 이름> AS <테이블 별칭>
*이제 부터 살펴볼 검색식 예들은 3개 이상의 테이블이 INNER JOIN되어 검색식 길이가 길다. 검색식을 이해하려 하기 보다는 무엇에 대한 예인지 정도를 알고 넘어갔으면 한다.
예) 주 요리(main course) 혹은 후식(dessert)과 관련한 음식이름, 준비물, 음식의 구분을 알고 싶다.
SELECT recipes.RecipeTitle, ingredients.IngredientName, recipe_classes.RecipeClassDescription FROM
((recipes INNER JOIN recipe_ingredients ON recipes.RecipeID=recipe_ingredients.RecipeID)
INNER JOIN ingredients ON recipe_ingredients.IngredientID=ingredients.IngredientID)
INNER JOIN recipe_classes ON recipes.RecipeClassID=recipe_classes.RecipeClassID
WHERE recipe_classes.RecipeClassDescription='main course' OR recipe_classes.RecipeClassDescription='dessert';
위 쿼리의 recipes, ingredients, recipeclasses, 그리고 recipeingredients 테이블을 각각 R, I, RC, 그리고 RI를 별칭으로 설정한 쿼리는 아래와 같다.
SELECT R.RecipeTitle, I.IngredientName, RC.RecipeClassDescription FROM
((recipes AS R INNER JOIN recipe_ingredients AS RI ON R.RecipeID=RI.RecipeID)
INNER JOIN ingredients AS I ON RI.IngredientID=I.IngredientID)
INNER JOIN recipe_classes RC ON R.RecipeClassID=RC.RecipeClassID
WHERE RC.RecipeClassDescription='main course' OR RC.RecipeClassDescription='dessert';
3개 이상의 테이블을 결합해서 데이터를 얻어야 한다면 먼저 사용하는 데이터베이스와 테이블들 간의 관계를 명확하게 알고 있어야 한다. 시간이 좀 걸리더라도 먼저 관심의 대상이 되는 테이블들의 관계를 명확하게 이해하도록 노력하자. 그 다음 테이블들을 차례로 어떻게 결합하면 결과를 얻을 수 있을지 계획을 세우자.
● 가상 테이블(virtual table)
검색 요청식 결과 셋을 또 다른 검색 요청식의 대상으로 사용할 수 있다. 이 경우 대상으로 사용된 검색 요청식 결과 셋을 가상 테이블(virtual table) 또는 논리 테이블(logical table)이라고 부른다.
위에서 살펴보았던 예를 가상 테이블을 이용해서 다시 구성할 수 있다.
예) 주 요리(main course) 혹은 후식(dessert)과 관련한 음식이름, 준비물, 음식의 구분을 알고 싶다.
SELECT filteredRecipes.RecipeTitle, ingredients.IngredientName, filteredRecipes.RecipeClassDescription FROM
((SELECT recipes.RecipeID, recipes.RecipeTitle, recipe_classes.RecipeClassDescription FROM recipes
INNER JOIN recipe_classes ON recipes.RecipeClassID=recipe_classes.RecipeClassID
WHERE recipe_classes.RecipeClassDescription='main course' OR recipe_classes.RecipeClassDescription='dessert') AS filteredRecipes
INNER JOIN recipe_ingredients ON filteredRecipes.RecipeID=recipe_ingredients.RecipeID)
INNER JOIN ingredients ON recipe_ingredients.IngredientID=ingredients.IngredientID;
*주의! 가상 테이블을 검색의 대상으로 삼기 위해서는 가상 테이블 안에서 ORDER BY 구문을 사용해서는 안된다.
●INNER JOIN 연산을 이용해서 풀 수 있는 문제들
INNER JOIN을 이용해서 풀 수 있는 문제들 중 주목할 만한 유형은 교집합(Intersect)을 구하는 문제들이다. 이러한 문제들은 간단한 경우 INTERSECT 명령을 이용해서 풀 수도 있으나 데이터가 여러 테이블에 나뉘어 저장되어 있고, 각 테이블의 형태도 서로 다를 때는 INNER JOIN을 이용해서 검색식을 만드는 것이 더 좋을 때가 많다.
예) 자전거와 헬멧을 모두 구매한 고객의 이름(CustFirstName, CustLastName)을 알고 싶다.
① 자전거를 구매한 고객
② 헬멧을 구매한 고객
③ SELECT bikePurchasers.OrderNumber, bikePurchasers.CustFirstName, bikePurchasers.CustLastName, bikePurchasers.ProductName, helmetPurchasers.ProductName FROM 자전거를 구매한 고객 AS bikePurchasers INNER JOIN 헬멧을 구매한 고객 AS helmetPurchasers ON bikePurchasers.OrderNumber=helmetPurchasers.OrderNumber;