[SQL Query] Sub Query (서브쿼리)

■ 용어설명

 - 서브쿼리는 괄호 안에 완전한 SELECT 문을 넣고는 여기에 이름을 붙여 생성한 테이블 표현식이다.

   일반적으로 테이블 이름을 쓸 수 있는 곳이라면 어디에나 서브쿼리를 사용할 수 있다. 값의 목록을 

   사용하는 곳 (예를 들면 IN 절) 이라면 어디에나 단일 컬럼을 반환하는 서브쿼리를 활용할 수 있다.

   컬럼 한 개나 0개를 반환하거나 값을 한개만 반환하는 서브쿼리는 컬럼 이름 한 개나 단일 값을

   사용할 수 있는 곳에서는 어디든 활용할 수 있다.

 - 서브쿼리는 완전한 SELECT 문을 괄호로 둘러싸고 괄호 밖에는 AS절과 함께 별칠을 주는 것이다.

   다른 SELECT, UPDATE, INSERT, DELETE 문 내의 여러 곳에 서브쿼리를 사용할 수 있다.

 

• 테이블 서브쿼리 (반환 : 컬럼과 로우 여러개를 포함한 데이터 집합 전체)

   - 테이블이나 뷰의 이름, 테이블을 반환하는 저장 프로시저나 함수 이름을 사용할 수 있는 곳이라면

     어디에나 활용할 수 있다.

    - 조인을 수행하기 전에 하나 이상의 집합에 대해 필터링이 필요한 여러 데이터 집합과 조인을 수행하는

      FROM 절에서 특히 유용하다

. 소고기와 마늘 모두를 사용하는 요리법을 찾는 테이블 서브쿼리

SELECT BeefRecipes.RecipeTitle
FROM

(
	SELECT Recipes.RecepeID, Recipes.RecepeTitle
    FROM Recipes
    INNER JOIN Recipe_Ingrediets
    ON Recipes.RecipeID = Recipe_Ingredients.RecipeID
    INNER JOIN Ingredients
    ON Ingredients.IngredientID = Recipe_Ingredients.IngredientID
WHERE Ingredients.IngredientName = 'Beef'
) AS BeefRecipes

INNER JOIN
(
(
	SELECT Recipes.RecepeID, Recipes.RecepeTitle
    FROM Recipes
    INNER JOIN Recipe_Ingrediets
    ON Recipes.RecipeID = Recipe_Ingredients.RecipeID
    INNER JOIN Ingredients
    ON Ingredients.IngredientID = Recipe_Ingredients.IngredientID
WHERE Ingredients.IngredientName = 'Garlic'
) AS BeefRecipes

ON BeefRecipes.RecipeID = GarlicRecipes.RecipeID

• 단일 컬럼 테이블 서브쿼리 (반환 : 로우를 여러개 가진 단일 컬럼)

   - 테이블 서브쿼리나 값의 목록을 IN / NOT IN 조건으로 비교하는 곳이라면 어디에나 활용할 수 있다.

. 단일 컬럼 테이블 서브쿼리로 2015년 12월에 주문하지 않은 제품 찾기

SELECT Product.ProductName
FROM Products
WHERE Products.ProductNumber NOT IN (
	SELECT Order_Details.ProductNumber
    FROM Orders
    	INNER JOIN Order_Details
        ON Orders.OrderNumber = Order_Details.OrderNumber
    WHERE Orders.OrderDate
    BETWEEN '2015-12-01' AND '2015-12-31'
)

. CASE 문에서 단일 컬럼 서브쿼리를 사용한 쿼리

SELECT Employees.EmpFirstName, Employees.EmpLastName,
	Customers.CustFirstName, Customers.CustLastName,
    Customer.CustAreaCode, Customers.CustPhoneNumber,
    
    CASE WHEN Customers.CustomerID IN (
    	SELECT CustomerID
        FROM Orders
        WHERE Orders.EmployeeID = Employees.EmployeeID
        ) THEN 'Ordered from you.'
        ELSE ''
	END AS CustStatus
FROM Employees
	INNER JOIN Customers
    ON Employees.EmpState = Customers.CustState

 

• 스칼라 서브쿼리 (반환 : 값을 하나만 반환)

   - 컬럼 이름이나 컬럼 이름에 대한 표현식을 다른 용도로 사용하는 곳이라면 어디에나 활용할 수 있다.

. SELECT 절에서 스칼라 서브쿼리를 컬럼으로 사용한 쿼리

SELECT Products.ProductNumber, Products.ProductName, 
	(SELECT MAX(Order.OrderDate)
    FROM Orders
    INNER JOIN Order_Details
    ON Orders.OrderNumber = Order_Details.OrderNumber
    WHERE Order_Details.ProductNumber = Products.ProductNumber
    ) AS LastOrder
FROM Products

. 비교조건에서 스칼라 서브쿼리 사용

SELECT Vendors.VendName, AVG(Product_Vendors.DaysToDeliver) AS AvgDelivery
FROM Vendors
INNER JOIN Product_Vendors
ON Vendors.VendorID = Product_Vendors.VendorID
GROUP BY Vendors.VendName
HAVING AVG(Product_Vendors.DayToDeliver) > (
SELECT AVG(DaysToDeliver)
FROM Product_Vendors
)

. 샐러드, 수프, 메인 코스 요리 찾기

SELECT Recipes.RecipeTitle
FROM Recipes
WHERE Recipes.RecipeClassID IN (
	SELECT rc.RecipeClassID
    FROM Recipe_Classes AS rc
    WHERE rc.RecipeClassDescription IN
    ('Salas', 'Soup', 'Main course')
    )

 

• 상관 서브쿼리 (반환 : 값을 하나만 반환)
  - 바깥 쪽 쿼리의 컬럼 중에 하나가 안쪽 서브쿼리의 조건에 이용
    그 결과는 다시 바깥쪽 쿼리에 영향을 준다. 이러한 쿼리를 상관관계 서브쿼리라고 한다.

 

SELECT E.emp_id, E.emp_name, E.dept_id
	(SELECT D.dept_name
    	FROM Department D
        WHERE E.dept_id = D.dept_id) AS dept_name
FROM Employees E;

. CART 테이블에서 회원 별 최고의 구매수량을 가진 레코드의
  회원, 주문번호, 상품수량, 회원명을 검색하시오
-----------------------------------------------------------------

SELECT A.cart_member 회원,
		A.cart_no 주문번호,
        A.cart_qty 수량,
        A.cart_prod 상품,
        (SELECT M.mem_name FROM MEMBER M WHERE M.mem_id = A.cart_member) 회원명
FROM CART A
WHERE A.cart_qty = (
					SELECT MAX(B.CART_QTY)
                    FROM CART B
                    WHERE B.cart_member = A.cart_member
                    )
ORDER BY 1;

■ 예제 쿼리

쿼리 조건
 - 직전 회사명, 직전 매상을 구하시오


Sales 테이블

company(회사)	year(연도)	sale(매상 : 억)
A	2002	50
A	2003	50
A	2004	55
A	2007	55
B	2001	27
B	2005	28
B	2006	28
B	2009	30
C	2001	40
C	2005	39
C	2006	38
C	2010	35

서브쿼리를 이용한 쿼리

SELECT company,
	year,
    sale,
    (SELECT company
    FROM Sales S2
    WHERE S1.company = S2.company
    AND year = (SELECT MAX(year)
    					FROM Sales S3
                        WHERE S1.company = S3.company	-- 상관 서브쿼리의 결합 조건
                        AND S1.year > S3.year)) AS pre_company,
	
    (SELECT sale
    FROM Sales S2
    WHERE S1.company = S2.company
    AND year = (SELECT MAX(year)
    					FROM Sales S3
                        WHERE S1.company = S3.company	-- 상관 서브쿼리의 결합 조건
                        AND S1.year > S3.year)) AS pre_company
FROM Sales S1;

결과값

company	year	sale	pre_company	pre_sale
A	2002	50
A	2003	52	A	50
A	2004	55	A	52
A	2007	55	A	55
B	2001	27
B	2005	28	B	27
B	2006	28	B	28
B	2009	30	B	28
C	2001	40
C	2005	39	C	40
C	2006	38	C	39
C	2010	35	C	38