[Oracle] JOIN / 서브쿼리(SUBQUERY)

사용 테이블 : EMP, DEPT,SALGRADE

SELECT * FROM EMP;

SELECT * FROM DEPT

SELECT * FROM SALGRADE;

---

비동등결합(NON-EQUI JOIN)

• 두개이상의 테이블의 결합조건에 = 연산자가 아닌 다른 연산자를 사용하여 참(TRUE)인 행을 결합하여 검색

 

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여 검색 

SELECT EMPNO,ENAME,SAL FROM EMP;

SALGRADE 테이블에 저장된 모든 급여등급의 등급번호,최소급여,최대급여 검색 

SELECT GRADE,LOSAL,HISAL FROM SALGRADE;

 
EMP 테이블과 SALGRADE 테이블에서 모든 사원의 사원번호,사원이름,급여,등급번호 검색

• 결합조건 : EMP 테이블의 급여(SAL)가 SALGRADE 테이블의 최소급여(LOSAL)부터 최대급여(HISAL) 범위에 포함되는 경우 행을 결합하여 검색 

SELECT EMPNO,ENAME,SAL,GRADE FROM EMP, SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL;

외부결합(OUTER JOIN)

• 결합조건이 참인 행만 결합하여 검색하는 것이 아니라 결합조건이 맞지 않는 행도 NULL과 결합하여 검색한다.
• 결합 조건식의 테이블에 (+)를 사용하면 결합조건이 맞지 않는 행을 NULL과 결합하여 검색된다.

 
EMP 테이블에 저장된 모든 사원의 부서번호를 중복되지 않는 유일한 컬럼값 검색 

SELECT DISTINCT DEPTNO FROM EMP; --검색결과 : 10,20,30

DEPT 테이블에 저장된 모든 부서의 부서번호,부서이름,부서위치 검색 

SELECT DEPTNO, DNAME, LOC FROM DEPT; --검색결과 : 10,20,30,40

EMP 테이블과 DEPT 테이블에 저장된 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색 

• 결합조건 : EMP 테이블의 부서번호(DEPTNO)와 DEPT 테이블의 부서번호(DEPTNO)가 같은 행만 결합
• 40번 부서에 근무하는 사원이 없으므로 40번 부서에 대한 부서이름, 부서위치가 검색되지 않음 

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;

사원이 없는 부서정보도 검색하기 위해 결합 조건식에서 EMP 테이블에 (+)를 붙여 검색하면 EMP 테이블에 결합되는 행이 없는 경우 NULL과 결합되어 사원이 없는 부서정보도 검색된다. - 외부결합 

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO (+)= DEPT.DEPTNO;

 

자기결합(SELF JOIN) 

• 하나의 테이블을 서로 다른 별칭을 부여하여 2개 이상의 테이블로 구분하여 행을 결합하여 검색

• 검색대상을 사용할 때 테이블을 명확하게 구분하기 위해 테이블 별칭을 사용해 컬럼값을 검색한다.

 

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,관리자번호(사원번호) 검색

SELECT EMPNO,ENAME,MGR FROM EMP;

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,관리자번호(사원번호),관리자이름(사원이름) 검색 

• 결합조건 : EMP 테이블(WORKER)의 관리자번호(MGR)와 EMP 테이블(MANAGER)의 사원번호(EMPNO)가 같은 행을 결합하여 검색

SELECT WORKER.EMPNO,WORKER.ENAME WORKER_ENAME,WORKER.MGR,MANAGER.ENAME MANAGER_ENAME 
    FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR=MANAGER.EMPNO;

하나의 테이블이 두 개의 별칭을 통해, 두 개의 테이블이 되었다.
검색대상을 사용할 때 테이블을 명확하게 구분하기 위해 WORKER_ENAME,MANAGER_ENAME라는 별칭을 사용해 컬럼값을 검색한 것이다.

하지만 이 방식으로는 EMP 테이블(WORKER)의 관리자번호(MGR)가 NULL인 사원이 검색되지 않는다. 
이 때 외부결합을 사용하면 EMP 테이블(WORKER)의 관리자번호(MGR)가 NULL인 사원 검색이 가능하다.

SELECT WORKER.EMPNO,WORKER.ENAME WORKER_ENAME,WORKER.MGR,MANAGER.ENAME MANAGER_ENAME 
    FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR=MANAGER.EMPNO(+);

 
EMP 테이블과 DEPT 테이블에서 SALES 부서에 근무하는 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색 

• 결합조건 : EMP 테이블의 부서번호(DEPTNO)와 DEPT 테이블의 부서번호(DEPTNO)가 같은 행 결합 

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME = 'SALES';

이처럼 결합조건과 행조건을 WHERE에서 같이 사용하므로 유지보수의 불편함이 존재한다. 
따라서 1999년 채택된 표준 SQL(SQL3)에서 테이블의 결합조건과 행의 조건을 구분하여 사용이 가능하도록 테이블 결합 관련 기능을 제공하는데, 해당 문법을 사용하는 것을 권장한다.

CROSS JOIN

• 결합 테이블의 모든 행을 교차 결합하여 검색 - 결합조건 생략 

• 형식)SELECT 검색대상,검색대상,... FROM 테이블명1 CROSS JOIN 테이블명2

 

EMP 테이블과 DEPT 테이블에 저장된 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치를 교차결합하여 검색 

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP CROSS JOIN DEPT;

 

NATURAL JOIN

• 결합 테이블에 같은 이름의 컬럼이 하나만 있는 경우 같은 이름의 컬럼에 저장된 컬럼값으로 같은 행을 결합하여 검색 

• 형식)SELECT 검색대상,검색대상,... FROM 테이블명1 NATURAL JOIN 테이블명2

 

EMP 테이블과 DEPT 테이블에 저장된 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색

• 결합조건 : EMP 테이블의 부서번호(DEPTNO)와 DEPT 테이블의 부서번호(DEPTNO)가 같은 행을 결합 

• NATURAL JOIN 사용 >> 결합할 테이블에 같은 이름의 컬럼값을 이용하여 자동으로 행을 결합 

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP NATURAL JOIN DEPT;

 
NATURAL JOIN을 사용할 경우 결합 테이블의 같은 이름의 컬럼을 사용할 때 테이블을 구분하지 않아도 검색이 가능하다.

SELECT EMPNO,ENAME,SAL,DEPTNO,DNAME,LOC FROM EMP NATURAL JOIN DEPT;

JOIN USING

• 결합 테이블에 같은 이름의 컬럼이 여러개 있는 경우 같은 이름의 컬럼에 저장된 컬럼값으로 같은 행을 결합하여 검색 - 결합조건 생략 

• 형식)SELECT 검색대상,검색대상,... FROM 테이블명1 JOIN 테이블명2 USING(컬럼명) 

 

EMP 테이블과 DEPT 테이블에 저장된 모든 사원의 사원번호,사원이름,급여,부서번호,부서이름,부서위치 검색

• 결합조건 : EMP 테이블의 부서번호(DEPTNO)와 DEPT 테이블의 부서번호(DEPTNO)가 같은 행을 결합 

결합 테이블에 같은 이름의 컬럼값을 이용하여 자동으로 행을 결합하므로 결합조건을 생략

SELECT EMPNO,ENAME,SAL,DEPTNO,DNAME,LOC FROM EMP JOIN DEPT USING(DEPTNO);

 

INNER JOIN

• 결합조건이 참(TRUE)인 행을 결합하여 검색

• 형식)SELECT 검색대상,검색대상,... FROM 테이블명1 INNER JOIN 테이블명2 ON 결합조건

EMP 테이블과 DEPT 테이블에 저장된 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색

• 결합조건 : EMP 테이블의 부서번호(DEPTNO)와 DEPT 테이블의 부서번호(DEPTNO)가 같은 행을 결합 

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

INNER 키워드 생략 가능 

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

 
EMP 테이블과 DEPT 테이블에 저장된 모든 사원의 사원번호,사원이름,급여,부서번호,부서이름,부서위치 검색
결합 테이블에 같은 이름의 컬럼을 검색할 경우 반드시 테이블을 명확하게 구분해 표현해야 한다. 

SELECT EMPNO,ENAME,SAL,DEPTNO,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO; --에러
SELECT EMPNO,ENAME,SAL,EMP.DEPTNO,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO; 
SELECT EMPNO,ENAME,SAL,DEPT.DEPTNO,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

 
INNER JOIN은 BETWEEN 등 다양한 결합조건을 제공한다는 이점이 있다. 
EMP 테이블과 SALGRADE 테이블에 저장된 모든 사원의 사원번호,사원이름,급여,등급번호 검색

• 결합조건 : EMP 테이블의 급여(SAL)가 SALGRADE 테이블의 최소급여(LOSAL)부터 최대급여(HISAL) 범위에 포함되는 경우 행을 결합하여 검색 

SELECT EMPNO,ENAME,SAL,GRADE FROM EMP JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL;

 

 

3개의 테이블 결합

EMP 테이블과 DEPT 테이블, SALGRADE 테이블에 저장된 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치,등급번호 검색

• 결합조건1 : EMP 테이블의 부서번호(DEPTNO)와 DEPT 테이블의 부서번호(DEPTNO)가 같은 행을 결합
• 결합조건2 : EMP 테이블의 급여(SAL)가 SALGRADE 테이블의 최소급여(LOSAL)부터 최대급여(HISAL) 범위에 포함되는 행을 결합하여 검색 

SELECT EMPNO,ENAME,SAL,DNAME,LOC,GRADE FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO 
    JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL;

EMP 테이블과 DEPT 테이블에서 SALES 부서에 근무하는 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색 

• 결합조건 : EMP 테이블의 부서번호(DEPTNO)와 DEPT 테이블의 부서번호(DEPTNO)가 같은 행 결합 

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE DNAME = 'SALES';

OUTER JOIN

• 결합조건이 참인 행만 결합하여 검색하는 것이 아니라 결합조건이 맞지 않는 행도 NULL과 결합하여 검색 

• 형식)SELECT 검색대상,검색대상,... FROM 테이블명1{LEFT|RIGHT|FULL} OUTER JOIN 테이블명2 ON 결합조건 
• LEFT OUTER JOIN : 왼쪽 테이블의 모든 행을 검색하고 결합조건이 맞는 오른쪽 테이블의 행은 NULL과 결합 
• RIGHT OUTER JOIN : 오른쪽 테이블의 모든 행을 검색하고 결합조건이 맞지 않는 왼쪽 테이블의 행은 NULL과 결합 
• FULL OUTER JOIN : 양쪽 테이블의 모든 행을 검색하고 결합조건이 맞지 않는 양쪽 테이블의 행은 NULL과 결합 

EMP 테이블과 DEPT 테이블에 저장된 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색

• 결합조건 : EMP 테이블의 부서번호(DEPTNO)와 DEPT 테이블의 부서번호(DEPTNO)가 같은 행을 결합

결합조건이 맞지 않는 행은 미검색 - 40번 부서의 부서이름,부서위치 미검색

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

결합조건이 맞지 않는 행을 NULL과 결합하여 검색하고 싶으면 OUTER JOIN 사용 - OUTER 키워드 생략 가능 

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP LEFT JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP FULL JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

---

서브쿼리(SUBQUERY) 

• SQL 명령에 포함되어 실행되는 SELECT 명령 

• 서브쿼리를 사용하는 이유는 다수의 SQL 명령으로 얻을 수 있는 결과를 하나의 SQL 명령으로 얻기 위해서이다.
• SELECT 명령(MAINQUERY)에 포함되어 실행되는 SELECT 명령(SUBQUERY)
• 서브쿼리의 명령을 먼저 실행하고 제공되는 결과값을 사용하여 메인쿼리의 명령 실행하여 검색 
• SELECT 명령의 서브쿼리는 FROM,WHERE,HAVING에서 () 안에 작성하여 실행 

EMP 테이블에서 사원이름이 SCOTT인 사원보다 많은 급여를 받는 사원의 사원번호,사원이름,급여 검색 

먼저 SCOTT의 급여를 알아봐야 한다.

SELECT SAL FROM EMP WHERE ENAME='SCOTT'; //검색결과 : 3000

급여가 3000이상인 사원의 사원번호,사원이름,급여 검색

SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>3000;

이처럼 서브쿼리를 사용하지 않는 경우, SELECT 명령을 2번 사용해야 원하는 결과를 검색할 수 있다.
서브쿼리를 사용하면 하나의 SELECT 명령으로 원하는 결과 검색이 가능하다.
WHERE에서 조건식의 비교값 대신 서브쿼리의 검색 결과값을 제공받아 검색한다.
이 때에, 조건식의 비교대상(컬럼)과 같은 자료형의 값이 하나만 검색되도록 서브쿼리를 작성해야한다.
>> 서브쿼리로 단일행(SINGLE-ROW)의 단일컬럼(SINGLE-COLUMN)에 대한 값이 검색되도록 작성

SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SCOTT');

 
EMP 테이블에서 사원번호가 7844인 사원과 같은 업무를 하는 사원의 사원번호,사원이름,업무,급여 검색 

SELECT EMPNO,ENAME,JOB,SAL FROM EMP 
    WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO='7844') AND EMPNO<>'7844';

EMP 테이블에서 사원번호가 7521인 사원과 같은 업무를 하는 사원 중 사원번호가 7844인 사원보다 많은 급여를 받는 사원의 사원번호,사원이름,업무,급여 검색 

SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO = 7521)
    AND SAL>(SELECT SAL FROM EMP WHERE EMPNO=7844);

 
EMP 테이블에서 SALES 부서에 근무하는 사원의 사원번호,사원이름,업무,급여 검색 

SELECT EMPNO,ENAME,JOB,SAL FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE DNAME='SALES';

부서 이름은 DEPT 테이블에 저장되어 있으므로 테이블 결합을 사용하여 검색한다.
테이블 결합 대신 서브쿼리를 사용하여 검색이 가능하다.

SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES');

검색 결과는 같다

EMP 테이블에 저장된 모든 사원 중 가장 적은 급여를 받는 사원의 사원번호,사원이름,업무,급여 검색 

SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM EMP);

이처럼 MAX나 MIN은 서브쿼리의 결과값을 얻기 위해 많이 사용한다. 
오라클은 서브쿼리 안에 서브쿼리를 사용하여 검색이 가능하다.
EMP 테이블에서 SALES 부서에 근무하는 사원 중 가장 적은 급여를 받는 사원의 사원번호,사원이름,업무,급여 검색 

SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM EMP 
    WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES')); //정확한 답은 아니다

이처럼 서브쿼리 안에 서브쿼리를 사용하는 것은 오라클에서만 가능하다 (MYSQL 등에선 안됨)
 
EMP 테이블에서 부서별 평균 급여 중 가장 많은 평균 급여를 받는 부서의 부서번호,평균급여 검색 

• HAVING의 그룹조건식에서 비교값 대신 서브쿼리의 결과값을 사용하여 검색 가능하다.

SELECT DEPTNO,CEIL(AVG(SAL)) FROM EMP GROUP BY DEPTNO 
    HAVING AVG(SAL)=(SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO);

 
이처럼 서브쿼리는 WHERE 뿐만 아니라 HAVING 에서도 사용할 수 있다.
 
서브쿼리의 검색결과가 다중행(MULTI-ROW SUBQUERY)인 경우에 [=]연산자를 사용하여 비교하면 에러가 발생한다.
EMP 테이블에서 부서별로 가장 적은 급여를 받는 사원의 사원번호,사원이름,급여,부서번호 검색 

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO); 
//에러 발생

서브쿼리의 검색결과가 다중행인 경우 [=]연산자 대신 IN 키워드를 사용하여 컬럼값 검색이 가능하다.

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);

 
서브쿼리의 검색결과가 다중행인 경우 > 또는 < 연산자로 컬럼값을 비교하기 위해 서브쿼리 앞에 ANY 또는 ALL 키워드를 사용하여 검색할 수 있다.
EMP 테이블에서 부서번호가 10인 부서에 근무하는 어떠한 사원보다 급여가 적은 사원의 사원번호,사원이름,급여,부서번호 검색 

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE SAL < ANY(SELECT SAL FROM EMP WHERE DEPTNO=10) AND DEPTNO<>10;

EMP 테이블에서 부서번호가 10인 부서에 근무하는 모든 사원보다 급여가 적은 사원의 사원번호,사원이름,급여,부서번호 검색 

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE SAL < ALL(SELECT SAL FROM EMP WHERE DEPTNO=10);

 
하지만 다중행 서브쿼리의 ANY 또는 ALL 키워드 대신 단일행 서브쿼리의 MIN 또는 MAX 함수를 사용하는 것을 권장한다.
EMP 테이블에서 부서번호가 10인 부서에 근무하는 어떠한 사원보다 급여가 적은 사원의 사원번호,사원이름,급여,부서번호 검색 

• 컬럼명<ANY(다중행 서브쿼리) 대신 컬럼명<(단일행 서브쿼리 - MAX 함수) 사용 - 속도가 더 빠르고 효율적 

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE SAL < (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=10) AND DEPTNO<>10;

 
EMP 테이블에서 부서번호가 10인 부서에 근무하는 모든 사원보다 급여가 적은 사원의 사원번호,사원이름,급여,부서번호 검색

• 컬럼명<ALL(다중행 서브쿼리) 대신 컬럼명<(단일행 서브쿼리 - MIN 함수) 사용 - 속도가 더 빠르고 효율적 

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE SAL < (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=10);

 
EMP 테이블에서 사원이름이 ALLEN인 사원과 관리자가 같으며 같은 업무를 하는 사원의 사원번호,사원이름,관리자번호,업무,급여 검색

SELECT EMPNO,ENAME,MGR,JOB,SAL FROM EMP WHERE MGR=(SELECT MGR FROM EMP WHERE ENAME ='ALLEN')
    AND JOB=(SELECT JOB FROM EMP WHERE ENAME='ALLEN') AND ENAME<>'ALLEN';

 
서브쿼리의 검색대상이 여러 개(MULTI-COLUMN SUBQUERY)인 경우 비교 컬럼을 () 안에 ,로 나열하여 검색 가능 (오라클만 가능)

SELECT EMPNO,ENAME,MGR,JOB,SAL FROM EMP 
    WHERE (MGR,JOB)=(SELECT MGR, JOB FROM EMP WHERE ENAME ='ALLEN') AND ENAME<>'ALLEN';