[데이터 모델과 SQL] 11. 서브쿼리Subquery - 2/2

 

● 수량 지정 - ALL, SOME, ANY

 

검색 조건으로 집합이 주어졌을 때 해당 집합에 속하는 원소인지 알아보기 위해 IN 키워드를 사용했었다. 이 밖에도 검색 조건으로 집합이 주어졌을 때 사용할 수 있는 연산으로 ALL, SOME, ANY가 존재한다.

· ALL: 조건으로 주어진 집합의 모든 원소들과 맞아야 한다.
· SOME: 조건으로 주어진 집합의 하나 이상의 원소들과 맞아야 한다.
· ANY: 조건으로 주어진 집합의 어떤 원소들과도 맞지 않아야 한다.

 

문법: SELECT <컬럼, 표현식> FROM <테이블 이름> WHERE <비교 대상이 되는 값> = or <> or < or > or <= or >= <ALL/SOME/ANY> <검색 조건>

* <> 연산 기호는 SQL에서 같지 않다(!=) 관계를 표현하는 기호이다.

 

예 1) 소고기(Beef) 또는 마늘(Garlic)이 포함된 모든 조리법을 알고 싶다.
① 먼저 소고기, 마늘에 해당하는 재료의 ID를 얻는다.

SELECT IngredientID FROM Ingredients WHERE IngredientName IN ('Beef', 'Garlic');

 

위 ① 조건에 해당하는 재료 ID를 가지는 조리법을 얻는다.

SELECT recipes.RecipeID, recipes.RecipeTitle 
FROM recipes INNER JOIN recipe_ingredients ON recipes.RecipeID = recipe_ingredients.RecipeID 
WHERE recipe_ingredients.IngredientID = 
SOME (SELECT IngredientID FROM Ingredients WHERE ingredients.IngredientID IN ('Beef', 'Garlic'));

 

예 2) 옷(Clothing)보다 비싼 액세서리(Accessory)들을 알고 싶다.

① 카테고리가 옷인 상품들 중 가장 비싼 옷의 가격을 얻는다.

SELECT MAX(products.RetailPrice) 
FROM products LEFT OUTER JOIN categories ON products.CategoryID=categories.CategoryID 
WHERE categories.CategoryDescription='Clothing';

 

② 카테고리가 액세서리인 상품들과 가격들을 얻는다.

SELECT products.ProductName, products.RetailPrice, categories.CategoryDescription 
FROM products LEFT OUTER JOIN categories ON products.CategoryID=categories.CategoryID 
WHERE categories.CategoryDescription='Accessories'

 

③ ②의 집합에서 ①의 결과 값 보다 큰 상품들을 출력한다.

SELECT products.ProductName, products.RetailPrice, categories.CategoryDescription 
FROM products LEFT OUTER JOIN categories ON products.CategoryID=categories.CategoryID 
WHERE categories.CategoryDescription='Accessories' 
AND products.RetailPrice > (SELECT MAX(products.RetailPrice) FROM products LEFT OUTER JOIN categories ON products.CategoryID=categories.CategoryID WHERE categories.CategoryDescription='Clothing');

 

● 있는지/없는지 - EXISTS
EXISTS 키워드를 사용하면 서브쿼리 결과 셋에 원소가 있는지 없는지 여부 만을 알 수 있다.

예) 자전거를 주문한 모든 고객들을 알고 싶다.

① 메인 쿼리: 모든 고객들

SELECT customerID, CONCAT(custFirstName, ' ', custLastName) FROM customers;

 

② 서브쿼리: 특정 고객의 주문에 자전거가 포함된 주문

SELECT orders.OrderNumber FROM ((orders LEFT OUTER JOIN order_details ON orders.OrderNumber=order_details.OrderNumber) 
LEFT OUTER JOIN products ON order_details.ProductNumber=products.ProductNumber) 
LEFT OUTER JOIN categories ON products.CategoryID=categories.CategoryID 
WHERE CategoryDescription='Bikes

 

③ 서브쿼리를 조건으로 메인 쿼리와 결합

SELECT customerID, CONCAT(custFirstName, ' ', custLastName) 
FROM customers 
WHERE EXISTS (SELECT orders.OrderNumber FROM ((orders LEFT OUTER JOIN order_details ON orders.OrderNumber=order_details.OrderNumber) LEFT OUTER JOIN products ON order\_details.ProductNumber=products.ProductNumber) LEFT OUTER JOIN categories ON products.CategoryID=categories.CategoryID WHERE CategoryDescription='Bikes' AND orders.CustomerID=customers.CustomerID);

 

● 서브쿼리의 용도 정리

· 서브쿼리를 컬럼 이름이나 표현식과 같이 값을 반환하는 표현식을 대신하여 사용할 수 있다. 검색식이 하나의 값을 반환해야 하므로 서브쿼리에 COUNT, MAX와 같이 값을 요약하는 함수를 함께 사용하는 경우가 많다.
예) 고객 명단과 각 고객이 주문을 몇 번 했는지 알고 싶다.

SELECT Customers.CustFirstName, Customers.CustLastName, (SELECT COUNT(Orders.OrderNumber) 
FROM Orders WHERE Customers.CustomerID=Orders.CustomerID) AS NumberOfOrders FROM Customers;

 

· 서브쿼리를 검색 결과를 필터링하는 검색 조건으로 사용할 수 있다. 필터링 검색 조건으로 서브쿼리를 사용함으로써 더 복잡한 조건에 해당하는 값을 얻어서 다른 값들과 비교할 수 있다.
예) 해산물 재료가 들어가는 모든 조리법을 알고 싶다.

SELECT Recipes.RecipeTitle, Ingredient_Classes.IngredientClassDescription, Ingredients.IngredientName 
FROM Recipes 
INNER JOIN (Recipe_Ingredients 
INNER JOIN (Ingredients 
INNER JOIN Ingredient_Classes ON Ingredients.IngredientClassID=Ingredient_Classes.IngredientClassID) 
ON Recipe_Ingredients.IngredientID=Ingredients.IngredientID) 
ON Recipes.RecipeID=Recipe_Ingredients.RecipeID 
WHERE (SELECT IngredientClassID FROM Ingredient_Classes WHERE IngredientClassDescription='Seafood')=Ingredients.IngredientClassID;

 

서브 쿼리는 메인 쿼리가 메인 쿼리의 검색 대상 데이터 각각을 차례로 방문할 때 마다 실행된다. 메인 쿼리와 서브쿼리에 같은 데이터가 포함되지 않도록 즉, 서로 겹치는 데이터를 검색하지 않도록 메인 쿼리와 서브쿼리를 구성하도록 유의한다. 그리고 서브쿼리 검색식에서 서브쿼리에 WHERE 조건절을 사용하여 메인 쿼리와 서브쿼리 사이의 관계를 지정해 준다.

 

■