이번 시간에는 오라클에서 중요한 개념 중 하나인
조인에 대해 개념과 예제를 보여주는 시간을 가지겠습니다
먼저 오라클에서 조인(JOIN)이란..
'두 개 이상의 테이블이나 데이터 베이스를 서로 연결하여 데이터를 검색할 때 사용하는 방법으로 서로 연관있는 테이블을 이용하여 두 개의 테이블이 마치 하나의 테이블인 것처럼 보여주는 것'
일반적으로 PRIMARY KEY(PK)와 FOREIGN KEY (FK)를 사용하여 JOIN 하는 경우가 대부분이지만 논리적인 값들의 연관으로 JOIN하는 경우도 있습니다.
조인(JOIN) 구문 형태
==================================
SELECT table1.colum1[, table2.column2 ...]
FROM table1, table2
WHERE table1.colum1=table2.column2;
또는 join절을 이용한 명시적 조인
SELECT table1.colum1[,table2.colum2...]
FROM table1 JOIN table2
ON table1.column1 = talbe2.column2;
===================================
조인의 형태
--------------------------------------------------------------------
1) EQUI JOIN : 조인조건이 정확히 일치하는 경우에 사용(pk와 fk를 사용하여)
2) NON-EQUI JOIN : 조인 조건이 정확히 일치하지 않는 경우에 사용(등급,학점)
3) OUTER JOIN : 조인 조건이 정확히 일치하지 않는 경우에도 모든 행들을 출력
4) SELF JOIN : 하나의 테이블에서 행들을 조인하고자 할 때 사용.
그외
5) Cartesian Product : 모든 가능한 행들의 조인
6) Set Operator : 여러 개의 select문장을 연결하여 작성한다.
--------------------------------------------------------------------
예제
dept와 emp의 equi join
SELECT D.DNAME, D.DEPTNO, E.ENAME, E.JOB
FROM DEPT D, EMP E
WHERE D.DEPTNO=E.DEPTNO
ORDER BY D.DEPTNO ASC;
명시적 JOIN절 사용
SELECT DNAME, D.DEPTNO, ENAME, LOC
FROM DEPT D JOIN EMP E
ON D.DEPTNO=E.DEPTNO
ORDER BY 1;
where절을 쓴거와 join절을 쓴것의 결과는 위와 똑같습니다.
[1]SALESMAN의 사원번호, 이름, 급여, 부서명과 근무지를 출력하세요
SELECT E.EMPNO, E.ENAME, E.SAL, E.JOB, D.DNAME, D.LOC
FROM EMP E JOIN DEPT D
ON D.DEPTNO=E.DEPTNO AND E.JOB='SALESMAN';
[2]카테고리 테이블과 상품 테이블을 JOIN하여 카테고리명, 카테고리 코드, 상품명, 상품 판매가, 제조사(COMPANY)를 출력하세요
SELECT C.CATEGORY_NAME, C.CATEGORY_CODE, P.PRODUCTS_NAME, P.OUTPUT_PRICE, P.COMPANY
FROM CATEGORY C JOIN PRODUCTS P
ON C.CATEGORY_CODE = P.CATEGORY_FK
ORDER BY 2;
위의 'C', 'P' 빼도 실행에는 문제가 없습니다.
[3]상품의 카테고리, 상품명, 공급가, 판매가를 출력하되, 카테고리가 'TV'인 것은 제외시키세요
또한 상품의 판매가가 저렴한 순으로 정렬하세요
SELECT C.CATEGORY_NAME, P.PRODUCTS_NAME, INPUT_PRICE, OUTPUT_PRICE
FROM CATEGORY C, PRODUCTS P
WHERE C.CATEGORY_CODE=P.CATEGORY_FK
AND CATEGORY_NAME <>'TV'
ORDER BY OUTPUT_PRICE;
#NON-EQUI JOIN
조인 조건이 EQUAL(=)이 아닌 다른 연산 기호로 만들어지는 경우
EMP, SALGRADE 테이블을 살펴보자
SELECT * FROM SALGRADE;
각 사원의 사원명, 급여, 급여 등급을 출력하세요
SELECT ENAME, SAL, GRADE, LOSAL, HISAL
FROM EMP E JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL;
[4] 공급업체 테이블과 상품 테이블을 조인하여 공급업체명, 공급업체 코드, 상품명, 공급가를 출력하세요 (EQUI JOIN)
SELECT * FROM supply_comp; -- PK==>EP_CODE
SELECT * FROM PRODUCTS; -- 상품 테이블
SELECT EP_NAME, EP_CODE, PRODUCTS_NAME, INPUT_PRICE
FROM SUPPLY_COMP C JOIN PRODUCTS P
ON C.EP_CODE = P.EP_CODE_FK
ORDER BY 1;
[5] 공급업체 테이블과 상품 테이블을 조인하여 공급업체명, 상품명, 공급가를 표시하되 상품의 공급가가 100000원 이상의 상품 정보만 표시하세요
단, 공급업체는 공급업체B만 표시되도록 하세요
SELECT EP_NAME, PRODUCTS_NAME, OUTPUT_PRICE
FROM SUPPLY_COMP S JOIN PRODUCTS P
ON S.EP_CODE = P.EP_CODE_FK AND INPUT_PRICE>=100000
WHERE EP_NAME='공급업체B';
-- 잘못된 데이터 아래
SELECT EP_NAME, PRODUCTS_NAME, OUTPUT_PRICE
FROM SUPPLY_COMP S JOIN PRODUCTS P
ON P.INPUT_PRICE>=100000 AND EP_NAME='공급업체B';
두 테이블이 서로 EQUAL하지 않기 때문에 해당 정보를 독립적으로 보여준다.
그냥 공급업체B와 10만원이 넘는 상품을 결합하여 잘못된 데이터를 출력
#OUTER JOIN
두 개 이상의 테이블을 EQUI 조인하는 경우, 한쪽 테이블에 일치하는 행이 없으면 다른 쪽 테이블을 NULL로 하여 그 값을 보여준다.
SELECT * FROM DEPT;
EQUI 조인의 예
SELECT D.DEPTNO, DNAME, ENAME, SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO=E.DEPTNO ORDER BY 1;
SELECT D.DEPTNO, DNAME, ENAME, SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO(+)
ORDER BY 1;
명시적 JOIN 절을 이용한 OUTER 조인
오라클 9i 버전부터는 ANSI/ISO 표준 SQL인
LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN을 지원한다.
OUTER JOIN을 해보자.
SELECT D.DEPTNO, DNAME, ENAME
FROM DEPT D LEFT OUTER JOIN EMP E
ON D.DEPTNO=E.DEPTNO ORDER BY 1 DESC;
:LEFT OUTER JOIN : 왼쪽을 테이블을 기준으로 하여 출력
:RIGHT OUTER JOIN : 오른쪽 테이블을 기준으로 하여 출력
:FULL OUTER JOIN : 양쪽 테이블에 다 아웃터 조인을 건다.
SELECT D.DEPTNO, DNAME, ENAME
FROM DEPT D RIGHT OUTER JOIN EMP E
ON D.DEPTNO=E.DEPTNO ORDER BY 1 DESC;
SELECT D.DEPTNO, DNAME, ENAME
FROM DEPT D FULL OUTER JOIN EMP E
ON D.DEPTNO=E.DEPTNO ORDER BY 1 DESC;
[6] 상품 테이블의 모든 상품을 공급업체, 공급업체코드, 상품명, 공급가, 판매가 순서로 출력하되 일치하지 않는 공급업체도 출력되도록 하세요 (일치하지 않는 공급업체도.. <= outer join 활용
SUPPLY_COMP, PRODUCTS
SELECT * FROM SUPPLY_COMP;
SELECT * FROM PRODUCTS;
조인 활용 예제
SELECT EP_NAME, EP_CODE, PRODUCTS_NAME, INPUT_PRICE, OUTPUT_PRICE
FROM SUPPLY_COMP S JOIN PRODUCTS P
ON S.EP_CODE=P.EP_CODE_FK ORDER BY 1;
아래 나머지 조인은 직접 실행시켜보세요~!
SELECT S.EP_NAME, S.EP_CODE, P.PRODUCTS_NAME, P.INPUT_PRICE, P.OUTPUT_PRICE
FROM SUPPLY_COMP S LEFT OUTER JOIN PRODUCTS P
ON S.EP_CODE=P.EP_CODE_FK ORDER BY 1;
SELECT S.EP_NAME, S.EP_CODE, P.PRODUCTS_NAME, P.INPUT_PRICE, P.OUTPUT_PRICE
FROM SUPPLY_COMP S RIGHT OUTER JOIN PRODUCTS P
ON S.EP_CODE=P.EP_CODE_FK ORDER BY 1;
SELECT S.EP_NAME, S.EP_CODE, P.PRODUCTS_NAME, P.INPUT_PRICE, P.OUTPUT_PRICE
FROM SUPPLY_COMP S FULL OUTER JOIN PRODUCTS P
ON S.EP_CODE=P.EP_CODE_FK ORDER BY 1;
[7] 상품의 공급업체명, 카테고리명, 상품명, 판매가를 출력하세요
단, 공급업체나 상품 카테고리가 없는 상품도 출력되도록 합니다.
(category, supply_comp, products)
SELECT EP_NAME, CATEGORY_NAME, PRODUCTS_NAME, OUTPUT_PRICE
FROM CATEGORY C JOIN PRODUCTS P
ON C.CATEGORY_CODE = P.CATEGORY_FK
JOIN SUPPLY_COMP S
ON P.EP_CODE_FK=S.EP_CODE
ORDER BY 1;
SELECT EP_NAME, CATEGORY_NAME, PRODUCTS_NAME, OUTPUT_PRICE
FROM CATEGORY C RIGHT OUTER JOIN PRODUCTS P
ON C.CATEGORY_CODE = P.CATEGORY_FK
LEFT OUTER JOIN SUPPLY_COMP S
ON P.EP_CODE_FK=S.EP_CODE
ORDER BY 1;
#SELF JOIN
:테이블 자체적으로 조인할 경우, 가령 각 사원명과 각 사원의 관리자명을 함께 출력하세요
SELECT EMPNO, ENAME"사번" , MGR"관리자 사번" FROM EMP;
SELECT E.EMPNO, E.ENAME, M.ENAME
FROM EMP E JOIN EMP M
ON E.MGR=M.EMPNO ORDER BY 1;
[8] emp 테이블에서 NEW YORK에서 근무하고 있는 사원에 대하여 이름, 업무, 급여, 부서명을 출력하는 SELECT 문을 작성하세요
SELECT ENAME, JOB, SAL, DNAME, D.DEPTNO, D.LOC
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO AND D.LOC='NEW YORK';
[9] EMP에서 보너스를 받는 사원의 이름, 부서명, 보너스, 근무지를 출력하세요
SELECT ENAME, D.DEPTNO, SAL, D.LOC
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO AND COMM>0;
조인절 안쓴 버전
SELECT ENAME, DNAME, COMM, LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND COMM IS NOT NULL; --<>NULL [X]
[10]EMP에서 이름에 'L'자를 갖는 사원의 이름, 업무, 부서명, 급여를 출력하세요
SELECT ENAME, JOB, DNAME, SAL
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO AND (ENAME) LIKE '%L%';
[11]EMP 테이블에서 '누구의 관리자는 누구이다'는 내용을 출력하세요
단, 관리자를 갖지 않는 KING도 포함되도록 출력하세요
Ex) SMITH의 관리자는 FORD입니다. KING의 관리자는 NULL입니다.
SELECT E.ENAME||'의 관리자는'||NVL(M.ENAME,'NONE')||'입니다'"종속관계"
FROM EMP E LEFT OUTER JOIN EMP M
ON E.MGR = M.EMPNO;
