'join'에 해당되는 글 1건
- 2008.09.17 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)
'DATABASE' 카테고리의 다른 글
| DB 구조 (0) | 2008.09.17 |
|---|---|
| Oracle - DBMS 구조 (0) | 2008.09.17 |
| 데이터베이스 트랜잭션 (0) | 2008.09.17 |
