<학습목표>

1. 서브쿼리 : 메인쿼리 안에 쓰인 또다른 SELECT절
   1) SCAIAR 2) INLINE VIEW 3) NESTED
2. 다중행 서브쿼리 : IN, ANY, ALL, EXISTS 비교연산자(X)
3. 집합연산자 : UNION, UNIONALL(합집합), INTERSECT(교집합), MINUS(차집합)
4. EXISTS 연산자

INLINE VIEW
EX)
P.256
모든 거래처의 2005년도 거래처별 매입금액 합계
ALIAS : 거래처코드, 거래처명, 매입금액합계
LEFT OUTER JOIN

SELECT T.BUYER_ID, T.BUYER_NAME
, NVL(U.SUM_QTY,0) SUM_QTY
FROM
(
SELECT BUYER_ID
, BUYER_NAME
FROM BUYER
) T,
(
SELECT B.BUYER_ID
, B.BUYER_NAME
, SUM(BP.BUY_COST * BP.BUY_QTY) SUM_QTY
FROM BUYER B, PROD P, BUYPROD BP
WHERE B.BUYER_ID = P.PROD_BUYER
AND P.PROD_ID = BP.BUY_PROD
AND EXTRACT(YEAR FROM BP.BUY_DATE) = 2005
GROUP BY B.BUYER_ID, B.BUYER_NAME
) U
WHERE T.BUYER_ID = U.BUYER_ID(+);

ANSI표준

SELECT T.BUYER_ID, T.BUYER_NAME
, NVL(U.SUM_QTY,0) SUM_QTY
FROM
(
SELECT BUYER_ID
, BUYER_NAME
FROM BUYER
) T LEFT OUTER JOIN
(
SELECT B.BUYER_ID
, B.BUYER_NAME
, SUM(BP.BUY_COST * BP.BUY_QTY) SUM_QTY
FROM BUYER B, PROD P, BUYPROD BP
WHERE B.BUYER_ID = P.PROD_BUYER
AND P.PROD_ID = BP.BUY_PROD
AND EXTRACT(YEAR FROM BP.BUY_DATE) = 2005
GROUP BY B.BUYER_ID, B.BUYER_NAME
) U
ON (T.BUYER_ID = U.BUYER_ID);

중요*
EX)

장바구니TABLE에서 회원별 최고의 구매수량을 가진 자료의 회원,
주문번호, 상품, 수량에 대해 모두 검색하시오
(ALIAS는 회원, 주문번호, 상품, 수량)
상관관계서브쿼리(CORRELATED SUBQUERY) : MAIN의 특정 컬럼이
SUB의 조건으로 사용되고, SUB의 결과가 다시 MAIN의 조건으로 사용됨
SELECT A.CART_MEMBER 회원
, A.CART_NO 주문번호
, A.CART_PROD 상품
, A.CART_QTY 수량
FROM CART A
WHERE A.CART_QTY = (
SELECT MAX(B.CART_QTY)
FROM CART B
WHERE B.CART_MEMBER = A.CART_MEMBER --***
);

P.260
단일행 서브쿼리, 단일컬럼 서브쿼리
=, !=, <>, <, >, <=, >=

다중행 서브쿼리에 사용가능
IN, ANY, EXISTS 연산자 사용

다중컬럼 서브쿼리에 사용가능

ANY : OR(또는), 어떤 것이라도 맞으면 TRUE

SELECT A.MEM_NAME, A.MEM_JOB, A.MEM_MILEAGE
FROM MEMBER A
WHERE A.MEM_MILEAGE > ANY(
SELECT B.MEM_MILEAGE
FROM MEMBER B
WHERE B.MEM_JOB = '공무원'
);

ALL : AND, 모두 만족해야만 TRUE

SELECT A.MEM_NAME, A.MEM_JOB, A.MEM_MILEAGE
FROM MEMBER A
WHERE A.MEM_MILEAGE > ALL(
SELECT B.MEM_MILEAGE
FROM MEMBER B
WHERE B.MEM_JOB = '공무원'
);

SELECT A.CART_NO 주문번호
, A.CART_PROD 상품코드
, (SELECT C.MEM_NAME FROM MEMBER C WHERE C.MEM_ID = A.CART_MEMBER) 회원명
, M.MEM_NAME 회원명2
, A.CART_QTY 구입수량
FROM CART A, MEMBER M
WHERE A.CART_MEMBER = M.MEM_ID
AND A.CART_MEMBER <> 'a001'
AND A.CART_QTY > ANY(
SELECT DISTINCT B.CART_QTY FROM CART B WHERE B.CART_MEMBER = 'a001'

집합 연산자

UNION
EX)
P.261
A집합
SELECT MEM_NAME
, MEM_JOB
, MEM_MILEAGE
FROM MEMBER
WHERE MEM_MILEAGE > 4000
합집합, 중복1회, 자동정렬
UNION
B집합
SELECT MEM_NAME
, MEM_JOB
, MEM_MILEAGE
FROM MEMBER
WHERE MEM_JOB = '자영업';

UNION ALL
EX)
P.261
A집합
SELECT MEM_NAME
, MEM_JOB
, MEM_MILEAGE
FROM MEMBER
WHERE MEM_MILEAGE > 4000
합집합, 중복모두, 자동정렬안됨
UNION ALL
B집합
SELECT MEM_NAME
, MEM_JOB
, MEM_MILEAGE
FROM MEMBER
WHERE MEM_JOB = '자영업';

INTERSECT
EX)
P.261
A집합
SELECT MEM_NAME
, MEM_JOB
, MEM_MILEAGE
FROM MEMBER
WHERE MEM_MILEAGE > 4000
교집합, 자동정렬안됨
INTERSECT
B집합
SELECT MEM_NAME
, MEM_JOB
, MEM_MILEAGE
FROM MEMBER
WHERE MEM_JOB = '자영업';

MINUS
EX)
P.261
A집합
SELECT MEM_NAME
, MEM_JOB
, MEM_MILEAGE
FROM MEMBER
WHERE MEM_MILEAGE > 4000
차집합, 자동정렬안됨
MINUS
B집합
SELECT MEM_NAME
, MEM_JOB
, MEM_MILEAGE
FROM MEMBER
WHERE MEM_JOB = '자영업';