SUB QUERY

-------------------------------------------------------
--SUB QUERY - 개념 이해
-------------------------------------------------------
□ 하위 질의(SUB QUERY)란?

하나의 SQL문 안에 또 다른 SQL문이 포함된 SQL을 말한다.

□ 성능과 특징?  

  성능 :JOIN > 하위 질의 > 상관 하위 쿼리

  특징

      ＊하위질의:단독 실행이 가능

      ＊상관 하위질의:단독 실행이 불가능.(외부에 쿼리를 참조하기 때문에)

      ＊ JOIN ==> 하위 질의로 100% 변경이 가능

      ＊ 하위 질의 ==> JOIN으로 100% 변경이 불가

1-1 SELECT절의 하위 질의

    ＊SELECT 절에 괄호로 묶은 새로운 SQL을 만든다.

    ＊ 단일값(하나의 로우,하나의 컬럼에 여러가지 로우)

       하나의 로우 : 말그대로 하나의 로우(섭쿼리의 셀렉트에 SUM,MAX,MIN)

      하나의 컬럼에 여러가지 로우 : 섭쿼리에 WHERE절을 이용,바깥쪽의 FROM절에

                                                 테이블을을 참조

예)SELECT OrderID, (SELECT Freight

                              FROM Customers t2
                              Where t1.customerid=t2.customerid) as FreightList
FROM Orders t1

------------------------------------------------------
SELECT절 SUB QUERY
-------------------------------------------------------
 --Orders를 조회하는데, OrderID, 해당 OrderID의 Freight를 보여주고
 --여기에 각 OrderID마다 Orders의 총Freight, 그리고 Orders중에서 가장 높은

     Freight값을 보여주는 SQL
 SELECT OrderID, Freight,
  (SELECT MAX(Freight) FROM Orders) as MAXFreight,
  (SELECT SUM(Freight) FROM Orders) as TTLFreight
 FROM Orders
 --다음은 오류가 발생된다.
 SELECT OrderID, Freight, (SELECT Freight FROM Orders) as FreightList
 FROM Orders
 --Sub query에서는 단일값(한 개의 컬럼, 한 개의 로우)을 돌려 주어야 한다.

 

1-2 FROM절의 하위 질의

    ＊FROM 절에 테이블 대신에 SUB QUERY를 이용해서 또 다른 집합(테이블)을 만든다.

    ＊외부 쿼리의 내용을 SUB QUERY 내부에서 사용할 수 없다.

      (즉,상관 하위쿼리는 FROM절에 올 수가 없다는 뜻.)

-------------------------------------------------------
 FROM절 SUB QUERY
-------------------------------------------------------

 --Orders와 [Order Details]테이블을 조인해서,

    Orders의 CustomerID별로 OrderAmount(UnitPrice * Quantity)를 구한후,
 --이결과를 다시 Customers와 조인해서, CustomerID, Country, OrderAmount를 보여주시오.
 

SELECT T1.CustomerID, T1.Country, T2.OrderAmount
FROM Customers T1 INNER JOIN (
                                    SELECT T1.CustomerID, SUM(T2.UnitPrice * T2.Quantity) OrderAmount
                                    FROM Orders T1
                                    INNER JOIN [Order Details] T2
                                                   ON T1.OrderID = T2.OrderID
 WHERE T1.OrderDate >= '1996-07-01'
            AND T1.OrderDate < '1996-08-01'
 GROUP BY T1.CustomerID
 ) T2
 ON T1.CustomerID = T2.CustomerID

 

 --Products테이블의 ProdcutID, [ProductName별] 주문금액(OrderAmount)를 구한다.
 --OrderAmount는 [Order Details]테이블의 UnitPrice * Quanaity이고, 이때,

    Orders의 OrderDate가 1996-07인 데이터만 사용한다.
 --FROM절 Sub Query를 이용해서, ProductID별 OrderAmount를 구한후에 Products테이블과 조인.
 --주문하지 않은 Products도 결과에 포함시킨다.
 

SELECT T1.ProductID, T1.ProductName, ISNULL(T2.OrderAmount,0) OrderAmount
 FROM Products T1 LEFT OUTER JOIN (
                                 SELECT T2.ProductID, SUM(T2.UnitPrice * T2.Quantity) OrderAmount
                                 FROM Orders T1
                                           INNER JOIN [Order Details] T2
                                     ON T1.OrderID = T2.OrderID
                                     WHERE T1.OrderDate >= '1996-07-01'
                                     AND T1.OrderDate < '1996-08-01'
                                     GROUP BY T2.ProductID
                                  ) T2
                                     ON T1.ProductID = T2.ProductID

1-3 WHERE절의 하위 질의

     ＊WHERE 절의 조건을 SUB QUERY로 만든다.

     ＊ IN 조건  또는, = , >= , <= 과 같은 조건에 사용할 수 있다.

        IN 조건 : 단일 컬럼의 다중 혹은 단일값

        = , >= , <= 과 같은 조건 : 단일값     

     ＊ SELECT 절의 SUB QUERY 와 마찬가지로 단일값을 돌려주어야 한다.

-------------------------------------------------------
  WHERE절 SUB QUERY
-------------------------------------------------------
 --Customers를 조회
 --주문일자가 1996년7월인 데이터이고, Freight가 100이상이 존재하는

    Orders가 있는 Customers만 조회한다.
 --Sub Query를 WHERE의 IN절에 사용한다.
 SELECT T1.*
 FROM Customers T1
 WHERE T1.CustomerID IN ( SELECT A.CustomerID    <---위와는 반대의 경우는 NOT IN
                                        FROM Orders A
                                        WHERE A.OrderDate >= '1996-07-01'
                                            AND A.OrderDate < '1996-08-01'
                                            AND A.Freight >= 100
                                      )

 --Orders를 조회
 --Orders의 Freight가 전체 Freight의 평균보다 높은 Freight를 가진 Orders만 조회한다.
 SELECT T1.Freight, *
 FROM Orders T1
 WHERE T1.Freight >= ( SELECT AVG(A.Freight)
                                  FROM Orders A)
 ORDER BY T1.Freight  

능

2.상관 하위쿼리가 쓰이는곳 SELECT절, WHERE절(FROM절 제외)

2-1 SELECT절의 상관 하위 질의

     ＊SELECT 절에 괄호로 묶은 새로운 SQL을 만든다.

     ＊SELECT 절의 SUB QUERY는 외부 QUERY의 결과값(FROM 절) 을 전달 받아서 사용한다.

     ＊앞에 SELECT 절의 QUERY 와 동일하게 단일값(혹은 단일컬럼의 여러 값들)을 반환한다.

-------------------------------------------------------
SELECT절 상관 SUB QUERY
-------------------------------------------------------
 --Orders테이블을 조회하는데, 상관 Sub query를 사용해서

 주문한 고객의 CompanyName을 보여주고자 한다.
 --CompanyName은 Customers테이블에 존재한다.
 SELECT ( SELECT CompanyName
                FROM Customers as A
                WHERE  A.CustomerID = T1.CustomerID) as CompanyName, T1.*
 FROM Orders T1
 --만약에 Customers테이블에 동일한 CustomerID가 두 개 이상 존재한다면
 --위와 같은 상관 서브쿼리는 오류가 발생한다.

 --위의 상관 Sub query를 JOIN으로 해결

 SELECT T2.CompanyName, T1.*
 FROM Orders T1 INNER JOIN Customers T2
                     ON T1.CustomerID = T2.CustomerID
 ----------------------------------------------------------
 --각 Customers에 대해 최초 주문일과 마지막 주문일을 구해라,

    이때, Customers테이블에 있는 모든 정보를 보여주어라.
 --상관 Sub query를 사용하라.
 SELECT CustomerID,

              ( SELECT MIN(OrderDate)
                FROM Orders A
                WHERE A.CustomerID = T1.CustomerID) FirstOrderDate,
              ( SELECT MAX(OrderDate)
                FROM Orders A
                WHERE A.CustomerID = T1.CustomerID) LastOrderDate
 FROM Customers T1
 
 --Orders를 조회하는데, 각 OrderID별로 총 UnitPrice(Order Details테이블에 있음)를 구하라.
 --상관 서브 쿼리를 사용
 SELECT ( SELECT SUM(A.UnitPrice)
                FROM [Order Details] A
                WHERE A.OrderID = T1.OrderID) TTL_UnitPrice, *
 FROM Orders T1
 

2-2 WHERE절의 상관 하위 질의

      ＊WHERE 절의 SUB QUERY 가 외부 QUERY의 결과에 관계를 맺는 SUB QUERY

         (즉,외부의 QUERY를 참조한다는 뜻)

      ＊EXISTS연산을 사용하게 되면 여러 로우,컬럼을 돌려줄 수 있다.

         (EXISTS연산은 데이터의 존재 여부를 따지는 연산이다.

         있으면 TRUE 없으면 FALSE(값을 조회하지 않음)

-------------------------------------------------------
 WHERE절 상관 SUB QUERY
-------------------------------------------------------
 --Orders를 조회
 --각 CustomerID별로 가장 마지막의 OrderDate에 대한 Orders만 보여준다.
 SELECT *
 FROM Orders T1
 WHERE T1.OrderDate = ( SELECT  MAX(OrderDate)
                                    FROM Orders A
                                    WHERE A.CustomerID = T1.CustomerID --외부 쿼리와 연결됨
                                   )
 --성능에 굉장히 안좋은 SQL.
 --WHERE절의 상관 Sub query는 될 수 있으면, EXISTS에서만 사용하도록 한다.

 --Orders를 조회
 --주문의 Order Details에 ProductID가 11번이 존재하는 Orders만 조회.
 SELECT *
 FROM Orders T1
 WHERE EXISTS (SELECT *
                         FROM [Order Details] A
                         WHERE A.OrderID = T1.OrderID  --외부 쿼리와 연결됨
                         AND A.ProductID = 11
                         )

 --Customers를 조회
 --Orders의 OrderDate가 1996-07인 데이터에서 해당 CustomerID의 Freight 총합이

    1000 이상인 고객만 조회한다.
 --서브 쿼리에서 GROUP BY까지 사용 가능
 SELECT *
 FROM Customers T1
 WHERE EXISTS( SELECT CustomerID, SUM(Freight)
                         FROM Orders A
                         WHERE A.CustomerID = T1.CustomerID
                         GROUP BY CustomerID
                         HAVING SUM(Freight) >= 1000)