[데이터 모델과 SQL] 18. NOT 그리고 AND 문제 1/2

 

SQL에 대한 기본적인 내용들을 살펴보았다. 이제 데이터베이스에서 어느 정도 데이터를 찾고 수정할 수 있을 것이다. 이번에는 두 개 이상의 검색 조건을 충족시켜야 찾을 수 있는 데이터에 대해 그리고 이들을 찾기 위한 SQL 검색식에 대해 살펴보려 한다.

 

예) 소고기(Beef), 양파(Onion), 그리고 당근(Carrot)을 모두 포함한 음식의 조리법을 알고 싶다.

 

소고기를 포함한 음식의 조리법 AND 양파를 포함한 음식의 조리법 AND 당근을 포함한 음식의 조리법

①소고기를 포함한 음식의 조리법
특정 재료(ingredient)를 포함한 음식의 조리법을 찾기 위해서는 'recipes - recipeingredients - ingredients' 세 테이블을 함께 살펴보아야 한다.
SELECT recipes.RecipeID, recipes.RecipeTitle 

FROM (ingredients LEFT OUTER JOIN recipeingredients ON ingredients.IngredientID= recipeingredients.IngredientID) 

LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID 

WHERE ingredients.IngredientName='Beef';

②양파를 포함한 음식의 조리법
① 검색식에서 조건을 'Beef' 대신 'Onion'으로 변경하면 검색식을 구성할 수 있다.
SELECT recipes.RecipeID, recipes.RecipeTitle 

FROM (ingredients LEFT OUTER JOIN recipeingredients ON ingredients.IngredientID= recipeingredients.IngredientID) 

LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID 

WHERE ingredients.IngredientName='Onion';

③당근을 포함한 음식의 조리법
역시 ①검색식에서 조건을 'Beef' 대신 'Carrot'으로 변경하면 검색식을 구성할 수 있다.
SELECT recipes.RecipeID, recipes.RecipeTitle 

FROM (ingredients LEFT OUTER JOIN recipeingredients ON ingredients.IngredientID= recipeingredients.IngredientID) 

LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID 

WHERE ingredients.IngredientName='Carrot';

 

①, ②, ③ 검색식에서 공통되는 조리법을 찾으면된다. ①, ②, ③ 결과를 각각 가상 테이블로하여 INNER JOIN을 이용하면 된다. 첫 번째 FROM 절에서 JOIN되는 가상 테이블(=인라인 뷰)들은 ①, ②, ③ 검색식의 결과들이다. 검색식의 길이는 길지만 사실 그 내용은 복잡하지 않다.

SELECT recipeswbeef.RecipeID, recipeswbeef.RecipeTitle 
FROM ((SELECT recipes.RecipeID, recipes.RecipeTitle 
FROM (ingredients LEFT OUTER JOIN recipeingredients ON ingredients.IngredientID= recipeingredients.IngredientID) 
LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID WHERE ingredients.IngredientName='Beef') AS recipeswbeef 
INNER JOIN (SELECT recipes.RecipeID, recipes.RecipeTitle 
FROM (ingredients LEFT OUTER JOIN recipeingredients ON ingredients.IngredientID= recipeingredients.IngredientID) 
LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID WHERE ingredients.IngredientName='Onion') AS recipeswonion ON recipeswbeef.RecipeID=recipeswonion.RecipeID) 
INNER JOIN (SELECT recipes.RecipeID, recipes.RecipeTitle 
FROM (ingredients LEFT OUTER JOIN recipeingredients ON ingredients.IngredientID= recipeingredients.IngredientID) 
LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID WHERE ingredients.IngredientName='Carrot') AS recipeswcarrot ON recipeswonion.RecipeID=recipeswcarrot.RecipeID;

 

이렇게 가상테이블을 먼저 구하고 JOIN 연산을 수행하면 JOIN 연산의 대상 집합이 작아지기 때문에 SQL의 수행 시간 측면에서도 유리하다고 할 수 있다.

 

---

 

위 방법을 이용하면 소고기(Beef), 양파(Onion), 그리고 당근(Carrot)을 모두 포함하지 않은(NOT) 조리법을 찾는 검색식도 구성할 수 있다.

 

예) 소고기(Beef), 양파(Onion), 그리고 당근(Carrot)을 모두 포함하지 않은(NOT) 음식의 조리법을 알고 싶다.
가상 테이블을 출력하는 검색식의 조건을 아래와 같이 수정한다.

①SELECT recipes.RecipeID, recipes.RecipeTitle 

FROM (ingredients LEFT OUTER JOIN recipeingredients ON ingredients.IngredientID= recipeingredients.IngredientID) 

LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID WHERE ingredients.IngredientName NOT IN ('Beef');

②SELECT recipes.RecipeID, recipes.RecipeTitle 

FROM (ingredients LEFT OUTER JOIN recipeingredients ON ingredients.IngredientID= recipeingredients.IngredientID) 

LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID WHERE ingredients.IngredientName NOT IN ('Onion');

③SELECT recipes.RecipeID, recipes.RecipeTitle FROM 

(ingredients LEFT OUTER JOIN recipeingredients ON ingredients.IngredientID= recipeingredients.IngredientID) 

LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID WHERE ingredients.IngredientName NOT IN ('Carrot');

 

①, ②, ③ 검색식의 결과 가상 테이블들을 INNER JOIN 하면 공통 원소, 즉 소고기(Beef), 양파(Onion), 그리고 당근(Carrot)을 모두 포함하지 않은 음식의 조리법을 찾을 수 있다. 검색식은 길지만 검색 결과 가상테이블들을 INNER JOIN하고 최종으로 남는 레코드들을 출력하는 방법 자체는 단순하다.

SELECT DISTINCT recipeswobeef.RecipeID, recipeswobeef.RecipeTitle 
FROM ((SELECT recipes.RecipeID, recipes.RecipeTitle 
FROM (ingredients LEFT OUTER JOIN recipeingredients ON ingredients.IngredientID= recipeingredients.IngredientID) 
LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID WHERE ingredients.IngredientName NOT IN ('Beef')) AS recipeswobeef 
INNER JOIN (SELECT recipes.RecipeID, recipes.RecipeTitle FROM (ingredients LEFT OUTER JOIN recipeingredients ON ingredients.IngredientID= recipeingredients.IngredientID) 
LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID WHERE ingredients.IngredientName NOT IN ('Onion')) AS recipeswoonion ON recipeswobeef.RecipeID=recipeswoonion.RecipeID) 
INNER JOIN (SELECT recipes.RecipeID, recipes.RecipeTitle FROM (ingredients LEFT OUTER JOIN recipeingredients ON ingredients.IngredientID= recipeingredients.IngredientID) 
LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID WHERE ingredients.IngredientName NOT IN ('Carrot')) AS recipeswocarrot ON recipeswoonion.RecipeID=recipeswocarrot.RecipeID;

 

SELECT 구문의 DISTINCT 옵션이 SQL의 수행 시간을 길게 만들것이다. 만약 중복된 결과가 상관 없다면 DISTINCT 옵션을 빼는 것이 좋다.

 

■