Oracle SQL(3)

■ JOIN종류

1. 조인 대상 테이블 중 데이터가 없는 데이블 조인 조건에(+)를 붙인다.
2. 외부 조인의 조인 조건이 여러 개일 때 모든 조건에 (+)를 붙인다.
3. 한 번에 한 테이블에만 외부 조인을 할 수 있다.
4. (+)연산자가 붙은 조건과 OR를 같이 사용할 수 없다
5. (+)연산자가 붙은 조건에는 IN 연산자를 같이 사용할 수 없다(단 IN절에 포함되는 값이 1개인 때는 사용 가능)

01. INNER JOIN

SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2... FROM 테이블 A, 테이블 B
WHERE A.컬럼1 = B.컬럼1 -> 조인조건
SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2... FROM 테이블A INNER JOIN 테이블B ON (A.컬럼1 = B.컬럼1) ->조인조건

SELECT s.*,c.* FROM student s, classroom c WHERE s.class=c.code AND s.kor >= 80; 
SELECT s.*,c.* FROM student s INNER JOIN classroom c ON s.class=c.code WHERE s.kor >= 80; 

02. RIGHT OUTER JOIN

SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2... FROM 테이블 A, 테이블 B
WHERE A.컬럼1(+) = B.컬럼1 -> 조인조건
SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2... FROM 테이블A RIGHT OUTER JOIN 테이블B ON (A.컬럼1 = B.컬럼1) ->조인조건

SELECT s.*,c.*  FROM student s, classroom c WHERE s.class(+)=c.code;
SELECT s.*,c.*  FROM student s RIGHT OUTER JOIN classroom c ON s.class =c.code; 

right outer join => class에는 모든 것 = student는 일치하는것 없으면 null

03. LEFT OUTER JOIN

SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2... FROM 테이블 A, 테이블 B
WHERE A.컬럼1 = B.컬럼1(+) -> 조인조건
SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2... FROM 테이블A LEFT OUTER JOIN 테이블B ON (A.컬럼1 = B.컬럼1) ->조인조건

SELECT s.*,c.*  FROM student s, classroom c WHERE s.class=c.code(+);
SELECT s.*,c.*  FROM student s LEFT OUTER JOIN classroom c ON s.class =c.code;

left outer join => student에는 모든 것 = class는 일치하는것 없으면 null

04. FULL OUTER JOIN

SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2... FROM 테이블 A, 테이블 B
WHERE A.컬럼1(+) = B.컬럼1(+) -> 오류
SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2... FROM 테이블A FULL OUTER JOIN 테이블B ON (A.컬럼1 = B.컬럼1) ->조인조건

SELECT s.*,c.*  FROM student s, classroom c WHERE s.class(+)=c.code(+); --오류
SELECT s.*,c.*  FROM student s FULL OUTER JOIN classroom c ON s.class =c.code ;

■ GROUP BY절

특정 그룹으로 묶어 데이터를 집계할 수 있다. 이때 사용되는 구문이 바로 GROUP BY 절이다.
그룹으로 묶을 컬럼명이나 표현식을 GROUP BY 절에 명시해서 사용하며 GROUP BY구문은 WHERE와 ORDER BY절 사이에 위치한다

GROUP BY 그룹으로 묶을 컬럼명

SELECT
    sv1.class,
    MAX(sv1.total) max,
    MIN(sv1.total) min,
    SUM(sv1.total) sum,
    AVG(sv1.total) avg,
    COUNT(sv1.total) cnt
FROM
    studentview1 sv1
GROUP BY
    sv1.class

■ HAVING절

HAVING절은 GROUP BY절 다음에 위치해 GROUP BY한 결과를 대상으로 다시 필터를 거는 역할을 한다. 즉 HAVING 필터 조건 형태로 사용한다.

HAVING 필터 조건

 SELECT
    sv1.class,
    MAX(sv1.total) max,
    MIN(sv1.total) min,
    SUM(sv1.total) sum,
    AVG(sv1.total) avg,
    COUNT(sv1.total) cnt
FROM
    studentview1 sv1
WHERE   
    sv1.kor >= 70 
GROUP BY
    sv1.class
HAVING
    COUNT(sv1.total) > 0; 

where절은 쿼리 전체에 대한 필터 역할을 한다(그룹하기 전에 필터)
having절은 where조건을 처리한 결과에 대해 group by를 수행한 후 산출된 결과에 대해 다시 조건을 건다(그룹후 필터)
-> kor점수가 70이상인 것을 class별로 그룹한 후 그룹중 COUNT가 0이상인 것을 조회

예제문제)
-- 문제1) 연령대별 주문수량 합계
SELECT age 연령대, sum(cnt) 주문수량
FROM purchaseview1
GROUP BY age;
-- 문제2) 상품별 주문수량
SELECT code 상품코드, name 상품이름, sum(cnt) 주문수량
FROM purchaseview1 pv1
GROUP BY code ;
-- 문제3) 성별 주문수량, 주문금액합계
SELECT pv1.usergender 성별, count(*) 주문수량, sum(pv1.total) 주문금액합계
FROM purchaseview1 pv1
GROUP BY pv1.usergender;    
-- 문제4) 시간대별 주무순량 0시 1시 2시 23시
SELECT TO_CHAR(pv1.regdate,'HH24') 시간대, sum(pv1.cnt) 주문수량
FROM purchaseview1 pv1
GROUP BY TO_CHAR(pv1.regdate,'HH24');    
-- 문제5) 주문수량이 2개 이상인 주문의 연령대별 주문수량 평균 구매금액
SELECT pv1.age 연령대, sum(pv1.cnt) 주문수량, avg(pv1.total) 평균구매금액
FROM purchaseview1 pv1
GROUP BY pv1.age
HAVING sum(pv1.cnt) >= 2;  
-- 문제6) 상품별 주문수량 개수가 3개 이상인것
SELECT pv1.code 상품, sum(pv1.cnt) 주문수량 FROM
purchaseview1 pv1
GROUP BY pv1.code
HAVING sum(pv1.cnt) >= 3;

■ Oracle과 JAVA연동