4)Non-Equi Join
- 조인조건절에 동등 연산자('=')가 아닌 다른 연산자가 사용되는 경우 --주로 부등호
- 부등호, IN,ANY 등의 연산자가 사용되는 경우
사용예)사원테이블에서 자기부서의 50번 부서의 관리자보다 입사일이 빠른 사원정보를 조회하시오.
Alias는 사원번호,사원명,직책명,입사일
식별관계를 맺게되면 직선으로 이어짐
식별자관계 : 직선으로 이어지는 관계 - 부모테이블이 없어지면 자식테이블이 존재이유가 없어질 때
ex)주문이 취소되었을 때 결제가 의미없는 것처럼
비식별관계 : 이외의 관계
SELECT C.EMPLOYEE_ID AS 사원번호,
C.EMP_NAME AS 사원명,
B.JOB_TITLE AS 직책명,
C.HIRE_DATE AS 입사일
FROM HR.EMP A, HR.JOBS B, HR.EMP C, HR.DEPT D
--A:50번부서의 관리자만 지칭하는 테이블 C:50번부서의 직원들의 정보를 지칭하는 테이블
WHERE D.DEPARTMENT_ID=30
AND A.EMPLOYEE_ID=D.MANAGER_ID --30번부서 매니저의 사원번호
AND C.HIRE_DATE<=A.HIRE_DATE --입사일이 빠른 사람 추출
AND A.DEPARTMENT_ID=C.DEPARTMENT_ID --30번부서에 속한 사람들 추출
AND C.JOB_ID=B.JOB_ID
ORDER BY 4;사용예)회원테이블에서 40대회원의 평균마일리지보다 많은 마일리지를 보유한 회원정보를 조회하시오.
Alias는 회원번호,회원명,연령대,마일리지
(서브쿼리 : 40대회원의 평균마일리지)
SELECT ROUND(AVG(MEM_MILEAGE))
--일반컬럼이 사용되지 않아서 GROUP BY절을 안써도 됨.
FROM MEMBER
WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1)=40;
(FROM절에 서브쿼리이용) --훨씬 효율적
SELECT A.MEM_ID AS 회원번호,
A.MEM_NAME AS 회원명,
TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1) AS 연령대,
A.MEM_MILEAGE AS 마일리지
FROM MEMBER A, (SELECT ROUND(AVG(MEM_MILEAGE)) AS AMILE
FROM MEMBER
WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)
-EXTRACT(YEAR FROM MEM_BIR),-1)=40) B
WHERE B.AMILE<=A.MEM_MILEAGE; --조인조건
(WHERE절에 서브쿼리이용) --비교할 양이 많음.
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1) AS 연령대,
MEM_MILEAGE AS 마일리지
FROM MEMBER
WHERE MEM_MILEAGE>=(SELECT ROUND(AVG(MEM_MILEAGE))
FROM MEMBER
WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)
-EXTRACT(YEAR FROM MEM_BIR),-1)=40);
(ANSI JOIN)
SELECT A.MEM_ID AS 회원번호,
A.MEM_NAME AS 회원명,
TRUNC(EXTRACT(YEAR FROM SYSDATE)
-EXTRACT(YEAR FROM MEM_BIR),-1) AS 연령대,
A.MEM_MILEAGE AS 마일리지
FROM MEMBER A
INNER JOIN (SELECT ROUND(AVG(MEM_MILEAGE)) AS AMILE
FROM MEMBER
WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)
-EXTRACT(YEAR FROM MEM_BIR),-1)=40)B
ON(B.AMILE<=A.MEM_MILEAGE); --조인조건
- 외부조인(OUTER JOIN)
-
내부조인은 조인조건을 만족하지 않는 자료는 무시됨
-
외부조인은 자료의 종류가 부족한 테이블에 NULL 행을 추가 삽입한 후 조인 연산 수행 --자료의 갯수가 아닌 자료의 종류(가짓수)가 많고 적음
-
조인조건에서 부족한 쪽의 컬럼명 뒤에 외부조인 연산자 '(+)'를 추가
-
조인조건이 하나 이상인 경우 모두 외부조인 연산자를 사용해야 함.
-
동시에 한 테이블이 두개이상 외부조인으로 연결될 수 없음.
즉 A, B, C테이블이 있고 A를 기준으로 B테이블이 확장되고, 동시에 C테이블을 기준으로 B테이블이 확장될 수 없다.
WHERE A=B(+) --양쪽에 (+)를 쓰는 FULL OUTER를 지원하지 않는다.
AND C=B(+) 는 허용되지 않음(일반외부조인 사용형식)
SELECT 컬럼list
FROM 테이블명1 [별칭1],테이블명2 [별칭2],테이블명3 [별칭3],...]
WHERE 별칭1,컬럼명1(+)=별칭2,컬럼명2
:(ANSI외부조인 사용형식)
SELECT 컬럼list
FROM 테이블명1 [별칭1]
FULL|RIGHT|LEFT OUTER JOIN 테이블명2 [별칭2] ON(조인조건 [AND 일반조건])
:
[WHERE 일반조건];
. 'FULL|RIGHT|LEFT' -- 많은 쪽에 기술(왼쪽이 많으면 LEFT, 오른쪽이 많으면 RIGHT)
- FULL : 양쪽 모두 부족한 경우
- RIGHT : FROM 절에 기술된 '테이블명1'보다 OUTER JOIN절의 '테이블명2'의 자료가 더 많은 경우
- LEFT : FROM 절에 기술된 '테이블명1'이 OUTER JOIN절의 '테이블명2'의 자료보다 더 많은 경우
**일반 외부조인사용시 조인조건과 일반조건이 동시적용되면 정확한 결과가 반환되지 않음 -- 내부조인이 결과로 나옴.
(해결책 => 서브쿼리사용 or ANSI 외부조인 사용) -- 기간, 시간을 비교할 경우 일반외부조인으로 나오지 않음.
사용예)모든 분류별 상품의 수를 조회하시오. --분류의 정확한 자료는 분류에, 상품의 정확한 자료는 상품테이블에 있음.
SELECT A.LPROD_GU AS 분류코드,
A.LPROD_NM AS 분류명,
COUNT(B.PROD_ID) AS "상품의 수"
-- OUTER조인일 때 COUNT(*)사용하면 안됨,
적은 수의 테이블에 있는 NULL값이 없는 기본키컬럼을 기술해주는 것이 BEST.
FROM LPROD A, PROD B-- 많은 쪽을 기준으로 A테이블을 작성
WHERE A.LPROD_GU=B.PROD_LGU(+)
GROUP BY A.LPROD_GU,A.LPROD_NM
ORDER BY 1;
(ANSI FORMAT)
SELECT A.LPROD_GU AS 분류코드, --분류코드가 가장 많은 테이블은 분류코드테이블(LPROD)
A.LPROD_NM AS 분류명,
COUNT(B.PROD_ID) AS "상품의 수"
FROM LPROD A
LEFT OUTER JOIN PROD B ON(A.LPROD_GU=B.PROD_LGU(+))
GROUP BY A.LPROD_GU,A.LPROD_NM
ORDER BY 1; 사용예)2005년 4월 모든 상품별 매입집계를 조회하시오.
-- 기간: 일반조건으로 처리 /모든: 아우터조인
상품코드,상품명,매입수량,매입금액
(일반 FORMAT)
SELECT B.PROD_ID AS 상품코드,
B.PROD_NAME AS 상품명,
SUM(A.BUY_QTY) AS 매입수량,
SUM(A.BUY_QTY*B.PROD_COST) AS 매입금액
FROM BUYPROD A, PROD B
WHERE A.BUY_PROD(+)=B.PROD_ID
AND A.BUY_DATE BETWEEN TO_DATE('20050401') AND TO_DATE('20050430')
GROUP BY B.PROD_ID,B.PROD_NAME
ORDER BY 1;
(ANSI FORMAT)
SELECT B.PROD_ID AS 상품코드,
B.PROD_NAME AS 상품명,
NVL(SUM(A.BUY_QTY),0) AS 매입수량,
NVL(SUM(A.BUY_QTY*B.PROD_COST),0) AS 매입금액
FROM BUYPROD A
RIGHT OUTER JOIN PROD B ON(A.BUY_PROD=B.PROD_ID
AND A.BUY_DATE BETWEEN TO_DATE('20050401')
AND TO_DATE('20050430'))
GROUP BY B.PROD_ID,B.PROD_NAME
ORDER BY 1;
(서브쿼리)
SELECT B.PROD_ID AS 상품코드,
B.PROD_NAME AS 상품명,
NVL(D.CAMT,0) AS 매입수량,
NVL(D.SAMT,0) AS 매입금액
FROM PROD B,
(--2005년 4월 상품별 매입집계(내부조인)--기간(일반조건)처리
SELECT A.BUY_PROD AS BID,
SUM(A.BUY_QTY) AS CAMT,
SUM(A.BUY_QTY*C.PROD_COST) AS SAMT
FROM BUYPROD A, PROD C
--왜 같은 SELECT절에 PROD가 두개 나오는지 : FROM절에 나오는 서브쿼리는 독립실행되어야함.
WHERE A.BUY_DATE BETWEEN TO_DATE('20050401') AND TO_DATE('20050430')
AND A.BUY_PROD=C.PROD_ID
GROUP BY A.BUY_PROD)D
WHERE B.PROD_ID=D.BID(+)
ORDER BY 1;
(확인용)
SELECT DISTINCT BUY_PROD--몇 가지나 매입되었는지 확인해보고 싶을 때 DISTINCT 사용
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050401') AND TO_DATE('20050430')
사용예)2005년 4월 모든 상품별 매출집계를 조회하시오
상품코드,상품명,매출수량,매출금액
(확인용)
SELECT DISTINCT CART_PROD--몇 가지나 매출되었는지 확인해보고 싶을 때 DISTINCT 사용
FROM CART
WHERE CART_NO LIKE '200504%'
(ANSI)
SELECT A.PROD_ID AS 상품코드,
A.PROD_NAME AS 상품명,
NVL(SUM(B.CART_QTY),0) AS 매출수량,
NVL(SUM(B.CART_QTY*A.PROD_PRICE),0) AS 매출금액
FROM PROD A
LEFT OUTER JOIN CART B ON(B.CART_PROD=A.PROD_ID AND B.CART_NO LIKE '200504%')
GROUP BY A.PROD_ID,A.PROD_NAME
ORDER BY 1;
(서브쿼리)
SELECT A.PROD_ID AS 상품코드,
A.PROD_NAME AS 상품명,
NVL(D.ASUM,0) AS 매출수량,
NVL(D.BSUM,0) AS 매출금액
FROM PROD A,
(--2005년 4월 상품별 매출집계(내부조인)
SELECT C.CART_PROD AS CID,
SUM(C.CART_QTY) AS ASUM,
SUM(C.CART_QTY*B.PROD_PRICE) AS BSUM
FROM PROD B, CART C
WHERE C.CART_NO LIKE '200504%'
AND B.PROD_ID=C.CART_PROD
GROUP BY C.CART_PROD)D
WHERE A.PROD_ID=D.CID(+)
ORDER BY 1;
사용예)2005년 4월 모든 상품별 매입/매출집계를 조회하시오
상품코드,상품명,매입수량,매출수량,매입금액,매출금액
(ANSI)
SELECT A.PROD_ID AS 상품코드,
A.PROD_NAME AS 상품명,
NVL(SUM(B.BUY_QTY),0) AS 매입수량,
NVL(SUM(C.CART_QTY),0) AS 매출수량,
NVL(SUM(B.BUY_QTY*A.PROD_COST),0) AS 매입금액,
NVL(SUM(C.CART_QTY*A.PROD_PRICE),0) AS 매출금액
FROM PROD A --A와 B는 반드시 직접조인(서로 관계가 있어야함)이 가능해야한다.
LEFT OUTER JOIN BUYPROD B ON(A.PROD_ID=B.BUY_PROD
AND B.BUY_DATE BETWEEN TO_DATE('20050401')
AND TO_DATE('20050430'))--매입관련
LEFT OUTER JOIN CART C ON(C.CART_PROD=A.PROD_ID AND C.CART_NO LIKE '200504%')
--위의 결과와 C가 아우터조인이 되는 것.
GROUP BY A.PROD_ID,A.PROD_NAME
ORDER BY 1;
(서브쿼리) --작성중...
SELECT A.PROD_ID AS 상품코드,
A.PROD_NAME AS 상품명,
NVL(SUM(B.BUY_QTY),0) AS 매입수량,
NVL(SUM(C.CART_QTY),0) AS 매출수량,
NVL(SUM(B.BUY_QTY*A.PROD_COST),0) AS 매입금액,
NVL(SUM(C.CART_QTY*A.PROD_PRICE),0) AS 매출금액
FROM PROD A,
(SELECT B.BUY_PROD,--4월 매입
SUM(B.BUY_QTY),
SUM(B.BUY_QTY*B.PROD_COST)
FROM BUYPROD B, PROD C
