[ORACLE JOIN]오라클 조인 개념 및 실습.

//*****************************************

-- EQUI 조인

  - 두 개 이상의 테이블에 관계되는 컬럼들의 값들이 일치하는 경우에 사용하는 가장 일반적인 join 형태로 WHERE 절에 '='(등호)를 사용한다.

  - EQUI JOIN은 단순 JOIN 또는 내부 JOIN이라고도 한다.

  - JOIN 조건의 컬럼은 UNIQUE가 아니어도 가능하다.

-----------------------------------------------

- 회원기본 테이블

아이디   패스워드    이름

1       1          a

2       2          b

3       3          c

- 회원상세 테이블

아이디   전화번호    이메일

1       11          aa

3       33          cc

- 회원기본 테이블과 회원상세 테이블을 아이디를 기준으로 EQUI 조인한 결과는 ?

아이디    패스워드    이름    전화번호    이메일

1       1          a      11        aa

3       3          c      33        cc

- 회원기본 테이블과 회원상세 테이블을 아이디를 기준으로 LEFT OUTER 조인한 결과는 ?

아이디    패스워드    이름    전화번호    이메일

1       1          a      11        aa

2       2          b      null      null

3       3          c      33        cc

-----------------------------------------------

-- 책코드(b_id), 책이름(title), 단가(price), 고객명(g_name), 수량(p_su), 금액(price * p_su)

-- book : b_id, title

-- danga : b_id, price

-- panmai : b_id, p_su, g_id(고객코드)

-- gogaek : g_name, g_id(고객코드)

-----------------------------------------------

-- 형식-1

   SELECT 테이블명1.컬럼명, 테이블명2.컬럼명 FROM 테이블명1, 테이블명2 [, 테이블명3 ]

         WHERE 테이블명1.컬럼명1=테이블명2.컬럼명1  [AND 조건];

 

SELECT  book.b_id, title, price, g_name, p_su, price * p_su amt

FROM book, danga, panmai, gogaek   

WHERE  book.b_id = danga.b_id AND book.b_id = panmai.b_id AND panmai.g_id = gogaek.g_id;

SELECT  b.b_id, title, price, g_name, p_su, price * p_su amt

FROM book b, danga d, panmai p, gogaek g  

WHERE  b.b_id = d.b_id AND b.b_id = p.b_id AND p.g_id = g.g_id;

---------------------------------------------

-- 형식-2

    SELECT 컬럼명, 컬럼명 FROM 테이블명1

                JOIN 테이블명2 USING (컬럼명1)

                JOIN 테이블명3 USING (컬럼명2);

SELECT  b_id, title, price, g_name, p_su, price * p_su amt

FROM book 

JOIN danga USING(b_id)

JOIN panmai USING(b_id)

JOIN gogaek USING(g_id);

---------------------------------------------

-- 형식-3

   SELECT 테이블명1.컬럼명, 테이블명2.컬럼_명 FROM 테이블명1

               JOIN 테이블명2 ON 테이블명1.컬럼명1=테이블명2.컬럼명1;

SELECT  b.b_id, title, price, g_name, p_su, price * p_su amt

FROM book b

JOIN danga d ON b.b_id = d.b_id

JOIN panmai p ON b.b_id = p.b_id

JOIN gogaek g ON p.g_id = g.g_id;

---------------------------------------------

-- 형식-4

    SELECT 컬럼명, 컬럼명 FROM 테이블명1 

                NATURAL JOIN 테이블명2;

    - 만약 MATCH되는 컬럼이 여러 개 일 경우, NATURAL JOIN에서는 특정 컬럼을 지정하지 않으므로 MATCH되는 컬럼 모두를 연결 조건으로 사용한다.

    - NATURAL JOIN의 경우 MATCH되는 컬럼이 여러 개일 경우 모두 join조건으로 사용하므로, 실행속도나 performance면에서 좋지 않다.

SELECT  b_id, title, price, g_name, p_su, price * p_su amt

FROM book

NATURAL JOIN danga

NATURAL JOIN panmai

NATURAL JOIN gogaek;

-----------------------------------------------

-- 판매된 책의 책코드(b_id), 책이름(title), 누적판매권수(p_su 합)

SELECT  b.b_id, title, p_su

FROM book b

JOIN panmai p ON b.b_id = p.b_id;

SELECT  b.b_id, title, SUM(p_su) 누적권수

FROM book b

JOIN panmai p ON b.b_id = p.b_id

GROUP BY b.b_id, title

ORDER BY b_id;

-- 누적판매권수가 10이상

SELECT  b.b_id, title, SUM(p_su) 누적권수

FROM book b

JOIN panmai p ON b.b_id = p.b_id

GROUP BY b.b_id, title

HAVING SUM(p_su) >= 10

ORDER BY b_id;

-- 누적판매권수가 가장 많은 책코드, 책이름

SELECT  b.b_id, title, SUM(p_su) 누적권수

FROM book b

JOIN panmai p ON b.b_id = p.b_id

GROUP BY b.b_id, title

ORDER BY b_id;

SELECT  b.b_id, title, SUM(p_su) 누적권수

        ,RANK() OVER(ORDER BY SUM(p_su) DESC) 순위

FROM book b

JOIN panmai p ON b.b_id = p.b_id

GROUP BY b.b_id, title;

SELECT b_id, title FROM (

   SELECT  b.b_id, title, SUM(p_su) 누적권수

        ,RANK() OVER(ORDER BY SUM(p_su) DESC) 순위

   FROM book b

   JOIN panmai p ON b.b_id = p.b_id

   GROUP BY b.b_id, title

) WHERE 순위=1;

-- 다른방법

SELECT MAX(SUM(p_su))

FROM book b

JOIN panmai p ON b.b_id = p.b_id

GROUP BY b.b_id, title;

SELECT b.b_id, title

FROM book b

JOIN panmai p ON b.b_id = p.b_id

GROUP BY b.b_id, title

HAVING SUM(p_su) = (

   SELECT MAX(SUM(p_su))

   FROM book b

   JOIN panmai p ON b.b_id = p.b_id

   GROUP BY b.b_id, title

);

-- 판매된 책의 책코드(b_id), 책이름(title), 누적판매권수(p_su 합), 판매금액합

SELECT  b.b_id, title, SUM(p_su) 누적권수, SUM(p_su*price) 판매금액

FROM book b

JOIN danga ON b.b_id = d.b_id

JOIN panmai p ON b.b_id = p.b_id

GROUP BY b.b_id, title

ORDER BY b_id;

------------------------------------

-- 판매된 책코드, 책이름

SELECT b_id, title 

FROM book

WHERE b_id IN (SELECT b_id FROM panmai);

SELECT DISTINCT b.b_id, title 

FROM book b

JOIN panmai p  ON b.b_id = p.b_id;

//*****************************************

-- OUTER JOIN(외부 조인)

   JOIN 조건을 만족하지 않는 행을 보기 위한 추가적인 join의 형태이다. 일반적인 join으로 얻을 수 없는 데이터를 구하고 싶을 때 사용하며, '+' 연산자를 사용한다. 

   OUTER JOIN은 조인 조건의 양측 컬럼 값 중에서 하나가 NULL인 경우에도 조인 결과를 출력할 수 있는 방법이다.

   EQUI JOIN은 조인 조건에서 양측 컬럼 값 중에서 하나라도 NULL이면 '=' 비교 결과가 거짓이 되어 조인 결과로 출력되지 않는다.

---------------------------------------------

-- LEFT OUTER JOIN

   왼쪽 테이블의 데이터는 모두 출력하고 오른쪽 테이블은 조건에 맞는 데이터만 출력

   SELECT 테이블1명.컬럼명, 테이블2명.컬럼명 FROM 테이블1명, 테이블2명

                WHERE 테이블1명.컬럼명=테이블2명.컬럼명(+);

    또는

    SELECT 테이블1명.컬럼명, 테이블2명.컬럼명 FROM 테이블1명

                LEFT OUTER JOIN 테이블2명

                ON 테이블1명.컬럼명=테이블2명.컬럼명;

--  판매된책

SELECT b.b_id, title, p_su

FROM book b

JOIN panmai p ON b.b_id = p.b_id;

-- 판매된 책 및 판매되지 않은 책도 출력

SELECT b.b_id, title, p_su

FROM book b

LEFT OUTER JOIN panmai p ON b.b_id = p.b_id;

SELECT b.b_id, title, NVL(p_su, 0) p_su

FROM book b

LEFT OUTER JOIN panmai p ON b.b_id = p.b_id;

SELECT b.b_id, title, p_su

FROM book b, panmai p WHERE b.b_id = p.b_id(+);

-- 판매되지 않은 책

SELECT b.b_id, title

FROM book b

LEFT OUTER JOIN panmai p ON b.b_id = p.b_id

WHERE p_su IS NULL;

---------------------------------------------

-- RIGHT OUTER JOIN

   오른쪽 테이블의 데이터는 모두 출력하고 왼쪽 테이블은 조건에 맞는 데이터만 출력

   SELECT 테이블1명.컬럼명, 테이블2명.컬럼명 FROM 테이블1명, 테이블2명

                WHERE 테이블1명.컬럼명(+)=테이블2명.컬럼명;

    또는

    SELECT 테이블1명.컬럼명, 테이블2명.컬럼명 FROM 테이블1명

                RIGHT OUTER JOIN 테이블2명

                ON 테이블1명.컬럼명=테이블2명.컬럼명;

SELECT b.b_id, title, p_su

FROM book b

LEFT OUTER JOIN panmai p ON b.b_id = p.b_id;

SELECT b.b_id, title, p_su

FROM panmai p

RIGHT OUTER JOIN book b ON b.b_id = p.b_id;

SELECT b.b_id, title, p_su

FROM panmai p, book b WHERE p.b_id(+) = b.b_id;

-----------------------------------------

b_id, title, price, p_su, p_su*price 금액 : 판매된 책 및 판매 되지 않은책 모두 출력

SELECT b.b_id, title, price, p_su, price * p_su 금액

FROM book b

JOIN danga d ON b.b_id = d.b_id

JOIN panmai p ON b.b_id = p.b_id;

SELECT b.b_id, title, price, NVL(p_su,0) p_su, price * NVL(p_su,0) 금액

FROM book b

LEFT OUTER JOIN danga d ON b.b_id = d.b_id

LEFT OUTER JOIN panmai p ON b.b_id = p.b_id;

---------------------------------------------

-- FULL OUTER JOIN(LEFT와 RIGHT OUTER JOIN 결합형태)

SELECT 테이블1명.컬럼명, 테이블2명.컬럼명

      FROM 테이블1명 FULL OUTER JOIN 테이블2명 ON 테이블1명.컬럼명=테이블2명.컬럼명;

SELECT num, name, buseo, city  FROM  insa  WHERE  city='인천';

SELECT num, name, buseo, city  FROM  insa  WHERE  buseo='개발부';

CREATE TABLE insa2 

AS SELECT num, name, buseo, city  FROM  insa  WHERE  city='인천';

CREATE TABLE insa3 

AS SELECT num, name, buseo, city  FROM  insa  WHERE  buseo='개발부';

SELECT a.num, b.num, a.name, b.name, a.buseo, b.buseo, a.city, b.city

FROM insa2 a

FULL OUTER JOIN insa3 b  ON a.num = b.num;

//*****************************************

-- NON-EQUI 조인

   두테이블에 관련성있는 컬럼이 없고 한테이블 컬럼의 값이 다른 테이블 컬럼에 포함된 경우 사용(= 대신 >, BETWEEN 사용)

SELECT b.b_id, title, price

FROM book b

JOIN danga d  ON  b.b_id = d.b_id;

SELECT b.b_id, title, price

FROM book b

JOIN danga d  ON  b.b_id > 'b-1';

   

//*****************************************

-- CROSS JOIN

   상호 조인은 조인에 포함된 테이블의 카티션 곱(Cartisian Product)을 반환한다.

   

SELECT b.b_id, title, price

FROM book b

CROSS JOIN danga d;

//*****************************************

-- SELF JOIN

   자신의 테이블을 alias를 사용하여 마치 두 개의 테이블처럼 JOIN하는 형태이다.

SELECT  a.b_id, a.name, b.name

FROM au_book a

JOIN au_book b ON a.b_id = b.b_id

ORDER BY a.b_id;

SELECT  a.b_id, a.name, b.name

FROM au_book a

JOIN au_book b ON a.b_id = b.b_id AND a.name > b.name

ORDER BY a.b_id;

//*****************************************

-- UPDATE JOIN VIEW

  -- tb_a 테이블의 내용(new_addr1, new_addr2) 을 tb_b 테이블의 내용(n_addr1, n_addr2)로 변경

  -- 조인 조건의 컬럼은 UNIQUE 속성이어야 하고 관계 1:1 이어야 가능. 아니면 ORA-01779 발생

UPDATE (

    SELECT

   a.new_addr1, a.new_addr2,

   b.n_addr1, b.n_addr2

FROM tb_a a, tb_b b

WHERE a.id = b.id

)

SET 

    new_addr1 = n_addr1,

    new_addr2 = n_addr2

;

COMMIT;

SELECT * FROM tb_a;

SELECT * FROM tb_b;