오라클 Oracle ANSI 조인

ANSI 조인

기존 문법과 ANSI 조인의 차이점은 조인 조건이 WHERE절이 아닌 FROM 절에 들어 간다는 점

아래는 ANSI 문법

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

오라클 조인 불편한 점 개선 -> ANSI 조인.  

오라클 조인은 full outer 불가능, union처리해야 했으나 ANSI 조인은 full outer 가능 

ANSI JOIN은 where 조회조건 

 

ANSI 외부 조인 = OUTER JOIN

SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2 ...
      FROM 테이블 A
      LEFT(RIGHT) [OUTER] JOIN 테이블 B
        ON ( A.컬럼1 = B.컬럼1)
    WHERE ...;

ANSI 문법

SELECT a.employee_id, a.emp_name, b.job_id, b.department_id
      FROM employees a
      LEFT OUTER JOIN job_history b
        ON ( a.employee_id  = b.employee_id
             and a.department_id = b.department_id) ;

SELECT a.employee_id, a.emp_name, b.job_id, b.department_id
      FROM job_history b
     RIGHT OUTER JOIN employees a
        ON ( a.employee_id  = b.employee_id
             and a.department_id = b.department_id) ;

SELECT a.employee_id, a.emp_name, b.job_id, b.department_id
      FROM employees a
      LEFT JOIN job_history b
        ON ( a.employee_id  = b.employee_id
             and a.department_id = b.department_id) ;

 

ANSI 조인 CROSS JOIN  = WHERE 절에 조인 조건을 명시하지 않은 카타시안 조인

SELECT a.employee_id, a.emp_name, b.department_id, b.department_name
      FROM employees a
     CROSS JOIN departments b;

FULL OUTER 조인 JOIN

FULL OUTER 조인은 외부 조인의 하나로 ANIS 조인에서만 제공하는 기능

 

---

 (1) 출력항목
연도, 사원명, 최대매출 => 연도별 최대 매출을 올린 사원 조회
이탈리아 고객에게 판매한
1-1. 연도별 사원별 매출합

SELECT SUBSTR(a.SALES_MONTH, 1, 4) AS years
       , a.EMPLOYEE_ID
       , SUM(a.AMOUNT_SOLD) AS tot_sold
  FROM SALES_60 a
 GROUP BY SUBSTR(a.SALES_MONTH, 1, 4) -- 연도별
     , a.EMPLOYEE_ID  -- 사원별
 ORDER BY years ASC , a.EMPLOYEE_ID ASC
;

이탈리아 고객 대상 연도별, 사원별 매출합

SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
       , a.EMPLOYEE_ID
       , SUM(a.AMOUNT_SOLD) AS tot_sold
  FROM SALES_60 a
     , CUSTOMERS b
     , COUNTRIES c
 WHERE c.country_name = 'Italy' -- 조회조건
   AND c.country_id = b.country_id -- 이탈리아 고객 추출
   AND b.cust_id = a.CUST_ID -- 이탈리아 고객 구매 정보 추출
 GROUP BY SUBSTR(a.SALES_MONTH,1,4)
        , a.EMPLOYEE_ID
 ORDER BY years ASC , a.EMPLOYEE_ID ASC
;
SELECT SUBSTR(a.SALES_MONTH, 1, 4) AS years
  FROM SALES_60 a
 WHERE ROWNUM <6
;

 

SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
       , a.EMPLOYEE_ID
       , SUM(a.AMOUNT_SOLD) AS tot_sold
  FROM SALES_60 a
 GROUP BY SUBSTR(a.SALES_MONTH,1,4)
        , a.EMPLOYEE_ID
;
-- 연도별, 사원별 매출합(A) => 연도별 최대매출
SELECT k.years
       , MAX(k.tot_sold) AS max_sold
  FROM (
        SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
               , a.EMPLOYEE_ID
               , SUM(a.AMOUNT_SOLD) AS tot_sold
          FROM SALES_60 a
         GROUP BY SUBSTR(a.SALES_MONTH,1,4)
                , a.EMPLOYEE_ID
  ) k -- from에 있는 서브쿼리 : 인라인 뷰
 GROUP BY k.years
 ORDER BY k.years ASC
;

SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
       , a.EMPLOYEE_ID
       , SUM(a.AMOUNT_SOLD) AS tot_sold
  FROM SALES_60 a
 GROUP BY SUBSTR(a.SALES_MONTH,1,4)
        , a.EMPLOYEE_ID
 ORDER BY years ASC, a.EMPLOYEE_ID
;
-- 연도별, 사원별 매출합(A) => 연도별 최대매출
SELECT k.years
       , MAX(k.tot_sold) AS max_sold
  FROM (
        SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
               , a.EMPLOYEE_ID
               , SUM(a.AMOUNT_SOLD) AS tot_sold
          FROM SALES_60 a
         GROUP BY SUBSTR(a.SALES_MONTH,1,4)
                , a.EMPLOYEE_ID
  ) k -- from에 있는 서브쿼리 : 인라인 뷰
 GROUP BY k.years
 ORDER BY k.years ASC
;

1-3. 위 1-1, 1-2 결과를 합쳐서 연도별 사원번호별 최대매출

SELECT
  FROM ( -- 연도별, 사원별 매출합
     SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
             , a.EMPLOYEE_ID
             , SUM(a.AMOUNT_SOLD) AS tot_sold
        FROM SALES_60 a
       GROUP BY SUBSTR(a.SALES_MONTH,1,4)
              , a.EMPLOYEE_ID
  ) a
  ,(
     SELECT k.years
             , MAX(k.tot_sold) AS max_sold
        FROM (
              SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
                     , a.EMPLOYEE_ID
                     , SUM(a.AMOUNT_SOLD) AS tot_sold
                FROM SALES_60 a
               GROUP BY SUBSTR(a.SALES_MONTH,1,4)
                      , a.EMPLOYEE_ID
        ) k -- from에 있는 서브쿼리 : 인라인 뷰
       GROUP BY k.years
  )

SELECT a.years, a.EMPLOYEE_ID, b.max_sold
  FROM ( -- 연도별, 사원별 매출합
     SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
             , a.EMPLOYEE_ID
             , SUM(a.AMOUNT_SOLD) AS tot_sold
        FROM SALES_60 a
       GROUP BY SUBSTR(a.SALES_MONTH,1,4)
              , a.EMPLOYEE_ID
  ) a
  ,( -- 연도별 최대매출
     SELECT k.years
             , MAX(k.tot_sold) AS max_sold
        FROM (
              SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
                     , a.EMPLOYEE_ID
                     , SUM(a.AMOUNT_SOLD) AS tot_sold
                FROM SALES_60 a
               GROUP BY SUBSTR(a.SALES_MONTH,1,4)
                      , a.EMPLOYEE_ID
        ) k -- from에 있는 서브쿼리 : 인라인 뷰
       GROUP BY k.years
  ) b
  WHERE a.years = b.years
    AND a.tot_sold = b.max_sold
;

1-4 위 1-3의 결과와 employees table 조인 연도별 사원명별 최대

SELECT a.years
     , a.EMPLOYEE_ID
     , c.EMP_NAME
     , b.max_sold
  FROM ( -- 연도별, 사원별 매출합
     SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
             , a.EMPLOYEE_ID
             , SUM(a.AMOUNT_SOLD) AS tot_sold
        FROM SALES_60 a
       GROUP BY SUBSTR(a.SALES_MONTH,1,4)
              , a.EMPLOYEE_ID
  ) a
  ,( -- 연도별 최대매출
     SELECT k.years
             , MAX(k.tot_sold) AS max_sold
        FROM (
              SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
                     , a.EMPLOYEE_ID
                     , SUM(a.AMOUNT_SOLD) AS tot_sold
                FROM SALES_60 a
               GROUP BY SUBSTR(a.SALES_MONTH,1,4)
                      , a.EMPLOYEE_ID
        ) k -- from에 있는 서브쿼리 : 인라인 뷰
       GROUP BY k.years
  ) b
  , employees c
  WHERE a.years = b.years
    AND a.tot_sold = b.max_sold
    AND a.EMPLOYEE_ID = c.EMPLOYEE_ID
  ORDER BY a.years ASC 
;

(1) 출력항목
연도, 사원명, 최대매출 => 연도별 최대 매출을 올린 사원 조회
이탈리아 고객에게 판매한
 1-1. 연도별 사원별 매출합

SELECT SUBSTR(a.SALES_MONTH, 1, 4) AS years
       , a.EMPLOYEE_ID
       , SUM(a.AMOUNT_SOLD) AS tot_sold
  FROM SALES_60 a
 GROUP BY SUBSTR(a.SALES_MONTH, 1, 4) -- 연도별
     , a.EMPLOYEE_ID  -- 사원별
 ORDER BY years ASC , a.EMPLOYEE_ID ASC
;

이탈리아 고객 대상 연도별, 사원별 매출합

SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
       , a.EMPLOYEE_ID
       , SUM(a.AMOUNT_SOLD) AS tot_sold
  FROM SALES_60 a
     , CUSTOMERS b
     , COUNTRIES c
 WHERE c.country_name = 'Italy' -- 조회조건
   AND c.country_id = b.country_id -- 이탈리아 고객 추출
   AND b.cust_id = a.CUST_ID -- 이탈리아 고객 구매 정보 추출
 GROUP BY SUBSTR(a.SALES_MONTH,1,4)
        , a.EMPLOYEE_ID
 ORDER BY years ASC , a.EMPLOYEE_ID ASC
;
SELECT SUBSTR(a.SALES_MONTH, 1, 4) AS years
  FROM SALES_60 a
 WHERE ROWNUM <6
;

 1-2. 연도별 최대매출
연도별, 사원별 매출합(A) => 연도별 최대매출

SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
       , a.EMPLOYEE_ID
       , SUM(a.AMOUNT_SOLD) AS tot_sold
  FROM SALES_60 a
 GROUP BY SUBSTR(a.SALES_MONTH,1,4)
        , a.EMPLOYEE_ID
 ORDER BY years ASC, a.EMPLOYEE_ID
;

 연도별, 사원별 매출합(A) => 연도별 최대매출

SELECT k.years
       , MAX(k.tot_sold) AS max_sold
  FROM (
        SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
               , a.EMPLOYEE_ID
               , SUM(a.AMOUNT_SOLD) AS tot_sold
          FROM SALES_60 a
         GROUP BY SUBSTR(a.SALES_MONTH,1,4)
                , a.EMPLOYEE_ID
  ) k -- from에 있는 서브쿼리 : 인라인 뷰
 GROUP BY k.years
 ORDER BY k.years ASC
;

1-3. 위 1-1, 1-2 결과를 합쳐서 연도별 사원번호별 최대매출

SELECT a.years, a.EMPLOYEE_ID, b.max_sold
  FROM ( -- 연도별, 사원별 매출합
     SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
             , a.EMPLOYEE_ID
             , SUM(a.AMOUNT_SOLD) AS tot_sold
        FROM SALES_60 a
       GROUP BY SUBSTR(a.SALES_MONTH,1,4)
              , a.EMPLOYEE_ID
  ) a
  ,( -- 연도별 최대매출
     SELECT k.years
             , MAX(k.tot_sold) AS max_sold
        FROM (
              SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
                     , a.EMPLOYEE_ID
                     , SUM(a.AMOUNT_SOLD) AS tot_sold
                FROM SALES_60 a
               GROUP BY SUBSTR(a.SALES_MONTH,1,4)
                      , a.EMPLOYEE_ID
        ) k -- from에 있는 서브쿼리 : 인라인 뷰
       GROUP BY k.years
  ) b
  WHERE a.years = b.years
    AND a.tot_sold = b.max_sold
;

1-4 위 1-3의 결과와 employees table 조인 연도별 사원명별 최대

SELECT year_emp_sold.years
     , year_emp_sold.EMPLOYEE_ID
     , c.EMP_NAME
     , b.max_sold
  FROM ( -- 연도별, 사원별 매출합
     SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
             , a.EMPLOYEE_ID
             , SUM(a.AMOUNT_SOLD) AS tot_sold
        FROM SALES_60 a
           , CUSTOMERS b
           , COUNTRIES c
       WHERE c.country_name = 'Italy'
         AND c.country_id = b.country_id
         AND b.cust_id = a.cust_id
       GROUP BY SUBSTR(a.SALES_MONTH,1,4)
              , a.EMPLOYEE_ID
  ) year_emp_sold
  ,( -- 연도별 최대매출
     SELECT k.years
             , MAX(k.tot_sold) AS max_sold
        FROM (
              SELECT SUBSTR(a.SALES_MONTH,1,4) AS years
                     , a.EMPLOYEE_ID
                     , SUM(a.AMOUNT_SOLD) AS tot_sold
                FROM SALES_60 a
                   , CUSTOMERS b
                   , COUNTRIES c
               WHERE c.country_name = 'Italy'
                 AND c.country_id = b.country_id
                 AND b.cust_id = a.cust_id
               GROUP BY SUBSTR(a.SALES_MONTH,1,4)
                      , a.EMPLOYEE_ID
        ) k -- from에 있는 서브쿼리 : 인라인 뷰
       GROUP BY k.years
  ) b
  , employees c
  WHERE year_emp_sold.years = b.years
    AND year_emp_sold.tot_sold = b.max_sold
    AND year_emp_sold.EMPLOYEE_ID = c.EMPLOYEE_ID
  ORDER BY year_emp_sold.years ASC 
;

 

문제 : 2008년 1월 1일 이후에 입사한 사원중에 
 사원번호, 사원명, 부서번호, 부서명 조회

SELECT a.EMPLOYEE_ID, a.EMP_NAME, b.DEPARTMENT_ID, b.DEPARTMENT_NAME, a.HIRE_DATE
  FROM EMPLOYEES a
     , DEPARTMENTS b
 WHERE a.DEPARTMENT_ID = b.DEPARTMENT_ID -- 조인조건
   AND a.HIRE_DATE >= TO_DATE('2008-01-01', 'YYYY-MM-DD')
   -- AND TO_CHAR(a.hire_date,'YYYYMMDD') > '2008-01-01'-- 조회 조건
;

date type : 내부 저장 => 숫자로 저장, 1970년 1월 1일 1초 단위 증가
1000 > 900 , '1000' > '900'
TO_CHAR(a.hire_date,'YYYYMMDD') => '2008-01-02'
DESC EMPLOYEES;

---

문제 : 2008년 1월 1일 이후에 입사한 사원중에 
사원번호, 사원명, 부서번호, 부서명 조회

SELECT a.EMP_NAME, b.DEPARTMENT_NAME, a.HIRE_DATE
  FROM EMPLOYEES a 
  JOIN departments b
    ON (a.DEPARTMENT_ID = b.DEPARTMENT_ID)
 WHERE a.HIRE_DATE >= TO_DATE('2008-01-01','YYYY-MM-DD')
;
-- 
SELECT a.EMP_NAME, b.DEPARTMENT_NAME, a.HIRE_DATE
  FROM EMPLOYEES a 
  JOIN departments b
 USING (DEPARTMENT_ID)
    -- ON (a.DEPARTMENT_ID = b.DEPARTMENT_ID)
 WHERE a.HIRE_DATE >= TO_DATE('2008-01-01','YYYY-MM-DD')
;

 문제 사원조회 => 사원명, 부서명

SELECT a.EMP_NAME, a.DEPARTMENT_ID 
  FROM EMPLOYEES a 
 WHERE a.DEPARTMENT_ID IS NULL  
;


SELECT COUNT(*) 
  FROM EMPLOYEES a
     , DEPARTMENTS b
 WHERE a.DEPARTMENT_ID = b.DEPARTMENT_ID(+)
;

SELECT COUNT(*)
  FROM EMPLOYEES a -- left
  LEFT JOIN departments b -- right
 USING(department_id)
;

 emp, dept

SELECT * FROM emp;
SELECT * FROM dept;

 inner join : 사원이 소속된 부서명 출력

SELECT e.EMPNM, d.DEPTNM  
  FROM emp e
     , dept d
 WHERE e.DEPTNO = d.DEPTNO
;
SELECT e.EMPNM, d.DEPTNM
  FROM emp e
  JOIN DEPT d
 USING (deptno)
;

사원 조회

SELECT e.EMPNM, d.DEPTNM
  FROM emp e
     , dept d
 WHERE e.DEPTNO = d.DEPTNO(+)
;
SELECT e.EMPNM, d.DEPTNM
  FROM emp e
  LEFT JOIN dept d
 USING (deptno)
;

부서 조회

SELECT e.EMPNM, d.DEPTNM
  FROM emp e
     , dept d
 WHERE e.DEPTNO(+) = d.DEPTNO
;
SELECT e.EMPNM, d.DEPTNM
  FROM emp e
  right JOIN dept d
 USING (deptno)
;