[Oracle] SQL JOIN : 외부 조인
외부 조인
- 내부조인은 조인조건을 만족하지 않는 자료를 무시(자료의 종류가 적은 쪽을 기준) 한 결과를 반환
- 외부조인은 자료의 종류가 많은 쪽을 기준으로 적은 쪽에 NULL행을 추가하여 조인을 수행함
- 일반 외부조인 경우
. 조인조건 기술시 자료의 종류가 적은 쪽에 외부조인 연산자 '(+)'를 기술
. 조인조건이 여러개 이고 모두 외부 조인이 필요한 경우 해당되는 모든 조건에 '(+)'를 사용해야 함
. 한 테이블이 동시에 여러번 외부조인에 사용될 수 없다. 예르 들어 3 테이블
A, B, C가 외부조인되는 경우 A를 기준으로 B가 외부조인되고, 동시에 C를
기준으로 B가 외부조인 될수 없다 (A=B(+_ AND C=(+)는 허용 안됨
. 일반 외부조인에서 일반 조건이 부여되면 결과는 내부조인 결과가 반환됨
→ 해결책으로 ANSI외부조인 서브쿼리르 사용해야 함
--결과의 NULL값이 나올 수 있음
--종류가 부족한거지 레코드수가 적다는 게 아님
--내부조인에 : 자료의 종류가 적은 곳에 외부조인 연산자만 추가(+)
(일반외부조인 사용형식)
SELECT 컬럼 LIST,...
FROM 테이블명1 [별칭1], 테이블명2 [별칭2][,테이블명3 [별칭3,...]
WHERE [별칭1.] 컬럼명 = [별칭2.]컬럼명(+)
[AND [별칭2.] 컬럼명 = [별칭3.]컬럼명(+)
(ANSI반외부조인 사용형식)
SELECT 컬럼 LIST,...
FROM 테이블명1 [별칭1]
LEFT|RIGHT|FULL|OUTER JOIN 테이블명2 [별칭2] ON(조인조건 [AND 일반조건])
:
{WHERE 일반조건]
. LEFT OUTER JOIN : 테이블명1의 자료의 종류가 테이블명2의 자료의 종류보다 많을 때
. RIGHT OUTER JOIN : 테이블명1의 자료의 종류가 테이블명2의 자료의 종류보다 적을 때
. FULL OUTER JOIN : 양쪽 테이블의 자료의 종류가 각각 적을 때
. WHERE 일반조건 : 모든 테이블에 공통으로 적용되는 조건을 기술해야 함
이 절을 사용할려면 결과가 내부 조인 결과가 반환 됨
조회하세요 - 내부
모든 | 전부 조회하세요 - 외부
~별 조회하세요 - GROUP BY
사용예) 모든 분류별 상품의 수를 조회하시오
Alias 분류코그(L,P), 분류명, 상품의 수 | 분류(9) < 상품(6)
SELECT DISTINCT PROD_LGU
FROM PROD;
SELECT LPROD_GU
FROM LPROD;
(일반외부조인)
SELECT L.LPROD_GU AS 분류코드,
L.LPROD_NM AS 분류명,
COUNT(PROD_ID) AS "상품의 수"
FROM LPROD L, PROD P
WHERE L.LPROD_GU = P.PROD_LGU(+)
GROUP BY L.LPROD_GU, L.LPROD_NM
ORDER BY 1;
-- 같은 테이블에 공통을ㄹ도 들어있는 SELECT절을 쓸때에는 많은 쪽에 컬럼을 써야합
-- COUNT에서 외부조인에서는 *을 쓰면안됨 NULL값도 하나의 행으로 판단 (대상테이블에 기본 컬럼키 넣어야함)
(ANSI JOIN)
SELECT L.LPROD_GU AS 분류코드,
L.LPROD_NM AS 분류명,
COUNT(PROD_ID) AS "상품의 수"
FROM LPROD L,
LEFT OUTER JOIN PROD P ON(L.LPROD_GU = P.PROD_LGU)
GROUP BY L.LPROD_GU, L.LPROD_NM
ORDER BY 1;
사용예) 2020년 7월 모든 회원별 구매금액집계(회원번호, 회원명, 구매금액합계)를 조회하시오.
SELECT M.MEM_ID AS 회원번호,
M.MEM_NAME AS 회원명,
SUM(C.CART_QTY*P.PROD_PRICE) AS 구매금액합계
FROM MEMBER M, CART C, PROD P
WHERE M.MEM_ID = C.CART_MEMBER(+)
AND C.CART_PROD = P.PROD_ID --카트에 (+)를 못함 위에서 확장을 한번해서 허용 안됨 (사실 할 필요없음 회원기준이기 떄문에
AND C.CART_NO LIKE '202007%'
GROUP BY M.MEM_ID, M.MEM_NAME
ORDER BY 1;
(ANSI JOIN)
SELECT M.MEM_ID AS 회원번호,
M.MEM_NAME AS 회원명,
NVL(SUM(C.CART_QTY*P.PROD_PRICE),0) AS 구매금액합계
FROM MEMBER M
LEFT OUTER JOIN CART C ON(M.MEM_ID = C.CART_MEMBER)
LEFT OUTER JOIN PROD P ON(C.CART_PROD = P.PROD_ID AND
C.CART_NO LIKE '202007%')
GROUP BY M.MEM_ID, M.MEM_NAME
ORDER BY 1;
--2번째 조건도 멤버랑 비교해서 MEM이 더 많으니까 LEFT
(SUBQUERY)
- 2020년 7월 회원별 구매금액 집계
SELECT M.MEM_ID AS 회원번호,
M.MEM_NAME AS 회원명,
NVL(C.CSUM,0) AS 구매금액합계
FROM MEMBER M, ( SELECT A.CART_MEMBER AS CID,
SUM(A.CART_QTY*B.PROD_PRICE) AS CSUM
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '202007%'
GROUP BY A.CART_MEMBER)C
WHERE M.MEM_ID = C.CID(+)
ORDER BY 1;