Oracle - join

join은 다음과 같이 분류한다.
1. 내부 join(=등가, equi join) (inner join)
1) 자연 join
2) on join
3) using join
- 비등가(not equi=on, using) join
2. 외부 join (outer join)
3. 교차 join (cross join)
4. 셀프 join (self join)
etc. semi join, anti join
......

@@@(다 알아야 하나..)

하지만, 대부분 우리가 쓰는 join은 equi join에서 대부분 사용한다.

 

cross join(실제 테이블에서는 이렇게 하면 위험함!)

--TSQL JOIN
SELECT * 
FROM TAB01, TAB02;
--또는(ANSI JOIN), 권장하는 방식
SELECT * 
FROM TAB01 CROSS JOIN TAB02;

equi join(등가 조인)

내부 조인 ⊃ 등가 조인

SELECT * 
FROM TAB01, TAB02
--TAB01 GNO = PRIMARY KEY(바뀌지 않는 키)
--TAB02 GNO = FOREIGNER KEY(외부 키)
--등가 JOIN: PK와 FK를 등가 연산하는 것
WHERE TAB01.GNO = TAB02.GNO;

 

TABLE 마다 별명을 붙이는 것을 권장함.

SELECT *
FROM TAB01 T1, TAB02 T2
WHERE T1.GNO = T2.GNO;

--FIELD 선택 출력
SELECT NAME, T1.GNO, SAL, GRADE
FROM TAB01 T1, TAB02 T2
WHERE T1.GNO = T2.GNO;

--INNER JOIN 이용
SELECT NAME, T1.GNO, SAL, GRADE
FROM TAB01 T1 INNER JOIN TAB02 T2
ON T1.GNO = T2.GNO;

 

JOIN 조건별 분류

SELECT NAME, T1.GNO, SAL, GRADE
--FROM 뒤에 있는 내용은 모두 JOIN 조건
FROM TAB01 T1, TAB02 T2
--WHERE 뒤에 있는 내용은 일반 조건
WHERE T1.GNO = T2.GNO
AND ROWNUM <= 2 --출력 갯수 제한
AND SAL >= 200
;

 

 

등가 JOIN과 비등가 JOIN

SELECT NAME, T1.GNO, SAL, GRADE
FROM TAB01 T1, TAB02 T2
--등가 JOIN(=이용한 JOIN)
WHERE T1.GNO = 1
--비등가 JOIN(부등호 조건 이용한 JOIN)
AND T1.SAL BETWEEN T2.LOSAL AND T2.HISAL;

 

외부 JOIN(OUTER JOIN)

--등가 연산
SELECT T1.ID, NAME, AGE
FROM TAB01 T1, TAB02 T2
WHERE T1.ID = T2.ID;

--OUTER JOIN 1
SELECT T1.ID, NAME, AGE
FROM TAB01 T1, TAB02 T2
WHERE T1.ID = T2.ID(+); 
-- +: 왼쪽(T1) 테이블 내용 모두 출력

--OUTER JOIN 2
SELECT T1.ID, NAME, AGE
FROM TAB01 T1, TAB02 T2
WHERE T1.ID(+) = T2.ID;
-- 오른쪽(T2) 테이블 내용 모두 출력

 

 

JOIN 다시 정리.

--LEFT JOIN
SELECT T1.ID, NAME, AGE
FROM TAB01 T1 LEFT JOIN TAB02 T2
ON T1.ID = T2.ID;

--RIGHT JOIN
SELECT T1.ID, NAME, AGE
FROM TAB01 T1 RIGHT JOIN TAB02 T2
ON T1.ID = T2.ID;

--FULL OUTER JOIN
SELECT T1.ID, NAME, AGE
FROM TAB01 T1 FULL OUTER JOIN TAB02 T2
ON T1.ID = T2.ID;

--INNER JOIN
SELECT T1.ID, NAME, AGE
FROM TAB01 T1 INNER JOIN TAB02 T2
ON T1.ID = T2.ID;

 

겹치는 부분 제거하는 JOIN

--LEFT JOIN 중 겹치는 부분 제거
SELECT T1.ID, NAME, AGE
FROM TAB01 T1 LEFT JOIN TAB02 T2
ON T1.ID = T2.ID
WHERE T2.ID IS NULL;

--RIGHT JOIN 중 겹치는 부분 제거
SELECT T1.ID, NAME, AGE
FROM TAB01 T1 RIGHT JOIN TAB02 T2
ON T1.ID = T2.ID
WHERE T1.ID IS NULL;

-- FULL OUTER JOIN 중 겹치는 부분 제거
SELECT T1.ID, NAME, AGE
FROM TAB01 T1 FULL OUTER JOIN TAB02 T2
ON T1.ID = T2.ID
WHERE T1.ID IS NULL
OR T2.ID IS NULL;

역시 능력자들은 항상 나보다 앞서있다..

 

 

NATURAL JOIN: EQUI(등가) JOIN의 Upgrade Version
USING JOIN: NATURAL JOIN의 Upgrade Version
결국 USING JOIN 또는 ON JOIN을 사용함.

하나를 골라 계속 쓰는 것 권장.

ON JOIN: USING과 동일하지만, 등가와 비등가 모두 사용함.

 

NATURAL JOIN

SELECT *
FROM TAB01 T1 NATURAL JOIN TAB02 T2;
--공통으로 걸린 것은 DB가 알아서 찾게 함

 

USING JOIN

등가 JOIN 대신하는 방식, 다중 사용 가능

SELECT ID, NAME, AGE
FROM TAB01 T1 JOIN TAB02 T2 USING(ID)
WHERE AGE >= 200;

 

ON JOIN

가장 넓은 범위. 등가 및 비등가 모두 사용 가능.

 

SEMIJOIN

ex) 세계의 모든 국가 데이터가 있는데

우리 반 학생들이 가고 싶은 국가는 5개밖에 없다.

이 국가의 리스트를 보고 싶을 때 사용한다.

--SEMIJOIN
SELECT *
FROM TAB02
WHERE TAB02.DEPT IN(
SELECT DEPT
FROM TAB01);

--EXIST가 속도가 더 빠름.
SELECT *
FROM TAB02 T2
WHERE EXISTS(
SELECT *
FROM TAB01 T1
WHERE T1.DEPT = T2.DEPT
);

 

 

ex) 다음과 같은 테이블이 2개 있다.(탕슉은 무시하자..)

음식번호    중국집 메뉴      
	   NUM MENU      
---------- ----------
         1 짜장      
         2 우동      
         3 냉면      
         4 짬뽕      
         5 탕슉      
   
   
   주문번호   판매 갯수	  판매 가격  음식번호
  ORDERNUM      VALUE      PRICE        NUM
---------- ---------- ---------- ----------
         1          2       3000          2
         2          2       3000          3
         3          2       3000          1
         4          2       3000          2
         5          2       3000          3
         6          2       3000          1
         7          2       3000          2
         8          2       3000          3

 

여기서 사장님이 이렇게 물었다.

"오늘 무슨 음식 팔았노?"

이러면 우린 오늘 짜장, 우동, 냉면만 팔았다고 말하도록 데이터가 출력되어야 한다.

SEMIJOIN을 이용한 방법은 다음과 같다.

SELECT *
FROM MENU M
WHERE M.NUM IN(
    SELECT NUM
    FROM ORDERS);

 

반대로 "오늘 뭐 안팔렸지?"는 다음과 같다.

여기서 NOT EXIST를 EXIST로 바꾸면 앞 결과와 같아진다.

SELECT *
FROM MENU M
WHERE NOT EXISTS(
    SELECT *
    FROM ORDERS O
    WHERE O.NUM = M.NUM
    );

 

등가 JOIN 및 DISTINCT를 통해서도 같은 결과를 낼 수 있다.

하지만, 실제로 DB에서 연산을 할 시에는 효율이 떨어지므로 이렇게는 쓰지 않는다.

SELECT 
DISTINCT MENU, M.NUM
FROM MENU M, ORDERS O
WHERE M.NUM = O.NUM
ORDER BY NUM;

 

무슨 음식을 몇 개나 팔았는지 추가하려면, 다음과 같이 하면 COUNT로 출력된다.

(SELECT 안 서브쿼리로 COUNT 추가)

SELECT M.NUM, M.MENU,
    (SELECT COUNT(*)
    FROM ORDERS S
    WHERE S.NUM = M.NUM)
AS COUNT
FROM MENU M
WHERE EXISTS(
    SELECT *
    FROM ORDERS S
    WHERE S.NUM = M.NUM)
ORDER BY NUM;