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

정말 오랜만에 글 남김니다. 요즘 일에 공부에 조금 지쳐서 살고 있는 것 같네요.

---

앞서 아래 두 검색식 예에 대해 살펴보았다.

 

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

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

 

위의 두 검색식을 구성할 때 각 재료를 포함(='재료 이름')하거나 포함하지 않는 것(NOT IN 재료 이름)을 조건으로 가상 테이블들을 생성하고 이들 사이의 교집합을 구하였다.

검색에서 하나의 조건은 하나의 집합을 생성한다. 따라서 두 개 이상의 조건은 조건의 수 만큼의 집합을 의미하고 이들 조건들을 모두 충족시킨다는 의미는 조건이 만들어 내는 집합들의 교집합을 찾는다는 의미와 같다.

---

●다중 부정 - 여러 부정(NOT) 조건들의 결합

 

예) 어떤 요리에도 사용되지 않는 재료들을 알고 싶다.

요리의 조리법과 재료 사이의 관계를 알기 위해서는 'recipes - recipe_ingredients - ingredients' 세 테이블을 함께 살펴보아야 한다. 여기서 어떤 요리에도 사용되지 않는 재료를 알기 위해서는 ingredients 테이블을 기준으로 세 테이블을 OUTER JOIN하고 최종적으로 recipes 테이블에 항목이 없는(예, recipeID = NULL) 재료를 출력하면 된다.

SELECT ingredients.IngredientID, ingredients.IngredientName 
FROM ingredients LEFT OUTER JOIN recipeingredients 
ON ingredients.IngredientID=recipeingredients.IngredientID 
LEFT OUTER JOIN recipes 
ON recipeingredients.RecipeID=recipes.RecipeID 
WHERE recipes.RecipeID IS NULL;

 

예) 소고기(Beef), 양파(Onion), 당근(Carrot)이 모두 들어가지 않은 음식들을 알고 싶다.

위 문장을 검색식으로 바로 옮기려하면 어디서 부터 접근해야 할 지 막막해 질 수 있다. 이 문제를 이렇게 표현해보자. 음식 조리법 테이블(recipes)에서 소고기, 양파, 당근 중 하나라도 들어간 음식을 제외하면 소고기, 양파, 당근이 모두 들어가지 않은 음식을 찾을 수 있다.

 

① 음식 조리법 테이블(recipes)에서 소고기, 양파, 당근 중 하나라도 들어간 음식을 찾는다.

SELECT DISTINCT recipes.RecipeID 
FROM (ingredients LEFT OUTER JOIN recipeingredients 
ON ingredients.IngredientID=recipeingredients.IngredientID) 
LEFT OUTER JOIN recipes 
ON recipeingredients.RecipeID=recipes.RecipeID 
GROUP BY ingredients.IngredientName, recipes.RecipeID 
HAVING ingredients.IngredientName='Beef' 
OR ingredients.IngredientName='Onion' 
OR ingredients.IngredientName='Carrot';

 

② 이제 조리법(recipes) 테이블에서 ①의 검색 결과에 포함되지 않는 음식들을 찾으면 된다.

SELECT recipes.RecipeTitle 
FROM recipes 
WHERE recipes.RecipeID NOT IN 
(SELECT DISTINCT recipes.RecipeID 
FROM (ingredients LEFT OUTER JOIN recipeingredients 
ON ingredients.IngredientID=recipeingredients.IngredientID) 
LEFT OUTER JOIN recipes 
ON recipeingredients.RecipeID=recipes.RecipeID 
GROUP BY ingredients.IngredientName, recipes.RecipeID 
HAVING ingredients.IngredientName='Beef' 
OR ingredients.IngredientName='Onion' 
OR ingredients.IngredientName='Carrot');

 

예) 소고기 또는 양파 또는 당근이 들어가지 않고 버터(Butter)가 포함된 음식을 알고 싶다.
이 문제를 아래와 같이 생각해 볼 수 있다.

① 소고기 또는 양파 또는 당근이 들어가지 않은 음식을 구한다.

SELECT recipes.RecipeTitle 
FROM recipes 
WHERE recipes.RecipeID NOT IN 
(SELECT DISTINCT recipes.RecipeID 
FROM (ingredients LEFT OUTER JOIN recipeingredients 
ON ingredients.IngredientID=recipeingredients.IngredientID) 
LEFT OUTER JOIN recipes 
ON recipeingredients.RecipeID=recipes.RecipeID 
GROUP BY ingredients.IngredientName, recipes.RecipeID 
HAVING ingredients.IngredientName='Beef' 
OR ingredients.IngredientName='Onion' 
OR ingredients.IngredientName='Carrot');

 

② 버터(Butter)가 포함된 음식을 구한다.

SELECT recipes.RecipeTitle 
FROM recipes 
WHERE recipes.RecipeID IN 
(SELECT DISTINCT recipes.RecipeID 
FROM (ingredients LEFT OUTER JOIN recipeingredients 
ON ingredients.IngredientID=recipeingredients.IngredientID) 
LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID 
GROUP BY ingredients.IngredientName, recipes.RecipeID 
HAVING ingredients.IngredientName='Butter');

 

③ ①, ②의 결과에 모두 포함된 음식을 찾으면 된다.

WITH recipesNotWithBeefOnionCarrot AS SELECT recipes.RecipeTitle 
FROM recipes 
WHERE recipes.RecipeID NOT IN 
(SELECT DISTINCT recipes.RecipeID 
FROM (ingredients LEFT OUTER JOIN recipeingredients 
ON ingredients.IngredientID=recipeingredients.IngredientID) 
LEFT OUTER JOIN recipes 
ON recipeingredients.RecipeID=recipes.RecipeID 
GROUP BY ingredients.IngredientName, recipes.RecipeID 
HAVING ingredients.IngredientName='Beef' 
OR ingredients.IngredientName='Onion' 
OR ingredients.IngredientName='Carrot';

WITH recipesWithButter AS SELECT DISTINCT recipes.RecipeID 
FROM (ingredients LEFT OUTER JOIN recipeingredients 
ON ingredients.IngredientID=recipeingredients.IngredientID) 
LEFT OUTER JOIN recipes ON recipeingredients.RecipeID=recipes.RecipeID 
GROUP BY ingredients.IngredientName, recipes.RecipeID 
HAVING ingredients.IngredientName='Butter';

SELECT recipesNotWithBeefOnionCarrot 
FROM recipesNotWithBeefOnionCarrot INNER JOIN recipesWithButter
ON recipesNotWithBeefOnionCarrot.recipeTitle=recipesWithButter.RecipeTitle;

 

SQL 쿼리가 상당히 길어졌는데 임시 테이블(오라클의 경우 WITH <테이블이름> AS <SELECT 쿼리>)을 사용하여 단계적으로 쿼리를 만들면 좀 더 읽기 쉬운 쿼리를 작성할 수 있다.

 

쿼리의 HAVING 구문에서 인덱스 또는 테이블 필터링이 발생하고 효율적이지 않을 것 같은데 일단 원하는 데이터를 얻는것에 집중하였다.

---

★★ 두 개 이상의 부정(NOT) 조건들이 결합된 기준과 OUTER JOIN, NOT IN, NOT EXISTS, 그리고 GROUP BY+HAVING 구문들을 각각 사용해서 주어진 조건에 맞는 데이터들을 검색하였다. 복합적인 기준과 다양한 방법들을 이용해 원하는 데이터를 검색했지만 모든 검색식이 공통적으로 가지는 원리를 정리하면 아래와 같다.

① 복잡한 문제는 여러 부분 혹은 과정으로 나누고 순차적으로 답을 구해 나간다.

② 검색식에서 각각의 조건은 집합을 형성한다. 따라서 둘 이상의 조건들이 결합된 복합 조건은 각각의 조건이 의미하는 집합들의 교집합을 찾는 방법으로, 즉 각각의 조건들을 모두 만족하는 원소를 찾는 형태로 풀어서 기술해 주어야 한다. SQL 문법을 빌려 표현하면 테이블에서 특정 조건을 적용해서 도출한 가상 테이블들의 교집합을 구하는 것이라 할 수 있다.

 

■