[데이터 모델과 SQL] 10. 서브쿼리Subquery - 1/2
서브쿼리란 검색식 안에서 사용되는 또 다른 검색식을 말한다. 서브쿼리는 아래 세 가지 타입 중 하나의 결과를 반환하고 검색식은 넘겨받은 결과를 이용하여 추가적인 연산을 진행하게 된다.
ⓐ 테이블 서브쿼리Table subquery: 하나 이상의 컬럼으로 구성된 0개 이상의 행. 즉, 테이블.
ⓑ 로 서브쿼리Row subquery: 하나 이상의 컬럼으로 구성된 하나의 행. 즉, 하나의 행.
ⓒ 스칼라 서브쿼리Scalar subquey: 하나의 컬럼으로 구성된 하나의 값.
● 테이블 서브쿼리
SQL 검색식 안에서 서브쿼리가 사용될 수 있는 자리는 서브쿼리의 타입에 따라 결정된다. 테이블 서브쿼리의 경우 서브쿼리의 결과는 '가상 테이블'이 되고, 메인 쿼리는 이 가상 테이블을 대상으로 데이터를 검색하게된다.
● 로row 서브쿼리
로row 서브쿼리의 경우 서브쿼리 결과로 하나의 행이 반환된다. 예를 들면 아래와 같이 값 비교 대상으로 로row 서브쿼리가 사용될 수 있다.
SELECT SKUClass, SKUNumber, ProductName FROM Products
WHERE (SKUClass, SKUNumber) >= (SELECT SKUClass, SKUNumber FROM Products WHERE (SKUClass > 'DSK') OR ((SKUClass = 'DSK') AND (SKUNumber >= 9775)));
● 스칼라 서브쿼리
스칼라 서브쿼리는 값 하나를 반환한다. 아래 예를 살펴보자.
예) 2017년 10월 3일에 배송이 시작된 주문, 그리고 각 주문의 주문자 이름을 알고 싶다.
SELECT orders.OrderNumber, orders.ShipDate,
(SELECT CONCAT(customers.custFirstName, ' ', customers.custLastName) FROM customers WHERE customers.customerID=orders.customerID) AS customerName
FROM orders WHERE orders.ShipDate='2017-10-03';위 검색식 예에서 서브쿼리(두번째 행)는 주어진 자리가 값 하나에 해당하는 자리이기 때문에 CONCAT( ) 함수를 사용하여 하나의 값(표현식 결과)을 반환하도록 만들어주었다.
★서브쿼리를 사용하는 경우 전체 검색식의 실행 과정을 이해하는 것이 매우 중요하다. 일반적으로 서브쿼리 결과가 검색의 대상(FROM 혹은 WHERE 구문의 목적 데이터)인 경우 서브쿼리가 먼저 실행되고 메인쿼리가 나중에 실행되는 bottom up 방향으로 실행된다.
하지만 검색 결과를 서브쿼리로 부터 얻을 경우(SELECT 구문의 목적 컬럼, 위의 예) 메인 쿼리가 먼저 실행되고 서브쿼리가 나중에 실행되는 top down 방향으로 진행된다.
위의 예에서 보면 orders.ShipDate='2017-10-03'에 해당하는 결과가 orders 테이블에서 얻어질 때 마다 서브쿼리인 (SELECT CONCAT(customers.custFirstName, ' ', customers.custLastName) FROM customers WHERE customers.customerID=orders.customerID)가 실행되면서 값을 출력하게 된다.
● COUNT( ) 함수와 MAX( ) 함수
서브쿼리가 하나 이상의 값을 반환한다 하더라도 이 값들을 적절히 하나로 합칠 수 있다면 스칼라 서브쿼리 자리에 사용할 수 있다. 예를 들어 결과 셋에서 개수를 세는 COUNT( ) 함수와 결과 셋에서 최대값을 뽑는 MAX( ) 함수를 사용하면 여러 결과 값을 하나의 값으로 표현할 수 있다.
예) SELECT CONCAT(customers.custFirstName, ' ', customers.custLastName) AS customerName,
(SELECT COUNT(order.OrderNumber) AS numOrders FROM orders WHERE orders.customerID=customers.customerID)
FROM customers;★위 예에서 orders 테이블과 customer 테이블은 다:1 관계에 있다. 이 경우 orders 테이블을 기준으로 customers 테이블을 호출하면(order 테이블=메인 쿼리, customers 테이블=서브 쿼리) orders 테이블에는 없는 정보는 결과 셋에서 빠질 수 있다. 다:1 관계를 1:다 관계로 방향을 바꾸어 검색하면, 즉 customers 테이블 검색을 메인 쿼리에서 수행하고 조건에 맞는 orders 테이블 결과를 호출하면 모든 customers에 대해 orders 값을 검색할 수 있으므로 다:1 → 1:다 방향으로 방향을 바꾸어 메인쿼리와 서브쿼리를 구성하는 것이 좋다.
● 검색 결과 필터링에 서브쿼리 사용하기
예) 고객 리스트 고객들의 가장 최근 주문의 상세 내역을 알고 싶다.
메인 쿼리 대상: 고객 리스트 고객들(customers)의 주문 상세 내역들(order_details)
SELECT customers.custFirstName, customers.custLastName, orders.orderNumber, products.ProductName
FROM ((order_details LEFT OUTER JOIN products ON order_details.ProductNumber=products.ProductNumber)
LEFT OUTER JOIN orders ON order_details.OrderNumber=orders.OrderNumber)
LEFT OUTER JOIN customers ON orders.CustomerID=customers.CustomerID
WHERE orders.OrderDate=(SELECT MAX(orders.OrderDate) FROM orders WHERE orders.CustomerID=customers.CustomerID);검색 조건으로 서브쿼리를 사용할 경우 전체 검색 후보 데이터의 어떤 항목을 기준으로 서브쿼리를 반복할 것인지 결정해야 한다. 또한 전체 검색 후보 데이터를 구성할 때 검색 대상이 되는 데이터가 사라지지 않도록 테이블들의 관계를 고려하여 테이블들을 결합(JOIN)해야 한다. 즉, 결합된 테이블의 형태를 알고 있어야 해당 테이블의 어떤 항목을 기준으로 서브쿼리를 반복하면 전체 검색 후보 데이터 각각에 대해 서브쿼리를 조건으로 검색 조건을 만족하는지 또는 그렇지 않는지 결정할 수 있다.
위의 예에서 보면 order_details 테이블이 결합 대상이 되는 모든 테이블 중 가장 후손 테이블이고 따라서 이 가장 후손 테이블을 기준으로 JOIN을 그 부모 테이블로 확장해 나가면(위의 예에서는 OUTER JOIN을 사용하였지만, 다:1 관계의 테이블 사이에서는 INNER JOIN을 사용해도 된다) 관심 대상 데이터인 주문 상세 내역(order_details)가 사라지지 않는다. 이들 각각의 order_details에 대해 고객 별로 OrderDate이 가장 큰 order_details를 출력함으로써 찾고자 하는 '고객 리스트의 각 고객의 주문 상세 내역들'을 알 수 있다.
● 서브쿼리를 포함하는 서브쿼리
예) 해산물(Seafood)이 들어간 음식의 모든 재료들을 알고 싶다.
해산물이 들어간 음식들의 RecipeID 획득 → 해당 RecipeID에 해당하는 음식들의 모든 재료들 출력
해산물이 들어간 음식들의 RecipeID 획득 = recipe_ingredients를 기준으로 ingredient를 OUTER JOIN
→ 다시 ingredient를 기준으로 ingredient_classes를 OUTER JOIN
→ ingredient_classes의 IngredientClassDescription이 'Seafood'인 RecipeID를 recipe_ingredients로 부터 획득.
= SELECT recipe_ingredients.RecipeID FROM (recipe_ingredients LEFT OUTER JOIN ingredients ON recipe_ingredients.IngredientID=ingredients.IngredientID) LEFT OUTER JOIN ingredient_classes ON ingredients.IngredientClassID = ingredient_classes.IngredientClassID
모든 음식의 모든 재료들
SELECT recipes.RecipeID, RecipeTitle, ingredients.IngredientName
FROM (recipe_ingredients LEFT OUTER JOIN ingredients ON recipe_ingredients.IngredientID=ingredients.IngredientID)
LEFT OUTER JOIN recipes ON recipe_ingredients.RecipeID=recipes.RecipeID
해산물이 들어간 음식들
SELECT recipes.RecipeID, RecipeTitle, ingredients.IngredientName
FROM (recipe_ingredients LEFT OUTER JOIN ingredients ON recipe_ingredients.IngredientID=ingredients.IngredientID)
LEFT OUTER JOIN recipes ON recipe_ingredients.RecipeID=recipes.RecipeID
WHERE ingredients.IngredientClassID=(SELECT IngredientClassID FROM ingredient_classes WHERE IngredientClassDescription='Seafood');
■