혼코딩

JOIN

• 두 개이상의 테이블에서 원하는 컬럼값을 검색하기 위한 기능

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

SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP;

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

SELECT DEPTNO, DNAME, LOC FROM DEPT;

EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호, 사원이름, 급여, 부서이름, 부서위치 검색 두 개이상의 테이블에서 컬럼값을 검색하기 위해서는 반드시 조인조건을 명시하여 사용

카타시안 프로덕트(CATASIAN PRODUCT)

• 조인조건을 명시하지 않은 조인 명령
• 조인 테이블들에 대한 모든 행의 컬럼값을 교차조인하여 검색

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

동등조인(EQUI JOIN)

• 조인조건에서 조인 테이블의 컬럼값이 같을 경우 조인하여 검색 조인조건은 WHERE 구문의 조건식으로 표현하여 사용

EMP 테이블과 DEPT 테이블의 조인조건은 부서번호가 같은 경우 조인하여 검색

→ 테이블에 동일한 컬럼명이 존재할 경우 테이블명을 이용하여 컬럼명을 명확하게 표현

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

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

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

조인 테이블의 동일 컬럼명을 동시에 검색할 경우 두번째 선언된 컬럼명은 자동으로 변경되어 검색

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

조인 테이블의 동일 컬럼명을 동시에 검색할 경우 컬럼 별칭을 사용하여 검색하는 것을 권장

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

테이블 별칭(TABLE ALIAS)

• 조인 테이블에 새로운 이름을 일시적으로 부여하는 기능
• 테이블 이름을 간단하게 표현하거나 셀프 조인하기 위해 사용
• 형식) 테이블명 별칭
• 테이블에 별칭을 부여한 경우 해당 명령에서 실제 테이블명을 사용하면 에러 발생

SELECT EMPNO, ENAME, SAL, E.DEPTNO EMP_DEPTNO, D.DEPTNO DEPT_DEPTNO, DNAME, LOC 
    FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO;

비동등 조인(NON-EQUI JOIN)

• = 연산자를 제외한 조인조건을 사용하여 컬럼값을 비교하여 조인

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

SELECT EMPNO,ENAME,SAL FROM EMP;

SALGRADE 테이블에서 모든 등록의 급여등급, 최소급여, 최대급여 검색

SELECT GRADE,LOSAL,HISAL FROM SALGRADE;

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

조인조건 : 급여가 최소급여과 최대급여 범위에 포함될 경우 급여등급 검색

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

외부조건(OUTER JOIN)

• 조인조건이 맞는 행만 검색하는 것이 아니라 조인조건이 맞지 않는 행도 검색하는 조인
• 조인조건이 맞지 않는 행이 저장된 테이블에 (+)를 사용하여 컬럼값이 NULL로 검색되도록 설정

EMP 테이블에서 모든 사원의 부서번호를 중복되지 않고 하나만 검색 - 결과 : 10, 20, 30 검색(부서번호)

SELECT DISTINCT DEPTNO FROM EMP;

DEPT 테이블에서 모든 부서의 부서번호, 부서이름, 부서위치 검색 - 10, 20, 30, 40 검색(존재하는 모든 부서번호)

SELECT DEPTNO, DNAME, LOC FROM DEPT;

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

40번 부서에 근무하는 사원이 존재하지 않으므로 40번 부서에 대한 부서이름과 부서위치 미검색

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

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

사원이 존재하지 않는 부서가 검색될 경우 사원번호, 사원이름, 급여는 NULL 검색

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

셀프조인(SELF JOIN) : 하나의 테이블에 다른 이름(TABLE ALIAS)을 여러 개 부여하여 조인

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 매니저번호(매니저의 사원번호) 검색

SELECT EMPNO,ENAME,MGR FROM EMP;

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 매니저번호(매니저의 사원번호), 매니저이름(매니저의 사원이름) 검색 매니저번호에 NULL이 저장된 사원 미검색 - 검색오류

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

매니저번호에 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 부서에 근무하는 사원의 사원번호, 사원이름, 급여, 부서이름, 부서위치 검색

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

1999 JOIN

• 1999년에 채택된 표준 SQL(SQL3)에 추가된 JOIN 구문

CROSS JOIN

• 조인 테이블의 모든 행들을 교차 조인
• 조인조건을 명시하지 않는다.
• 형식) SELECT 검색대상,... FROM 테이블명1 CROSS JOIN 테이블명2

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

NATURAL JOIN

• 조인 테이블에 하나의 동일한 컬럼명의 컬럼값이 같은 경우 조인
• 형식) SELECT 검색대상,... FROM 테이블명1 NATURAL JOIN 테이블명2

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

• 동일한 컬럼명에 대한 테이블명 명시 불필요

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

조인 테이블에 동일한 이름에 컬럼이 여러개 존재할 경우 USING 구분을 이용하여 조인 가능

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

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

INNER JOIN

• 조건조건을 명시하여 조인조건이 맞을 경우 조인
• 형식) SELECT 검색대상,... FROM 테이블명1 [INNER] JOIN 테이블명2 ON 조인조건
• 조인 테이블에 같은 이름에 컬럼이 존재할 경우 테이블명을 명시하여 컬럼 표현

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

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

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

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

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

OUTER JOIN

• 조인조건을 명시하여 조인조건이 맞을 경우뿐만 아니라 맞지 않을 경우에도 조인
• 형식) SELECT 검색대상,... FROM 테이블명1 {LEFT|RIGTH|FULL} [OUTER] JOIN 테이블명2 ON 조인조건
• LEFT OUTER JOIN : 왼쪽 방향에 선언된 조인 테이블의 모든 행을 검색하되 조인조건이 맞지 않는 오른쪽방향의 테이블 행에 컬럼값은 NULL 검색
• RIGTH OUTER JOIN : 오른쪽 방향에 선언된 조인 테이블의 모든 행을 검색하되 조인조건이 맞지 않는 왼쪽방향의 테이블 행에 컬럼값은 NULL 검색
• FULL OUTER JOIN : 양쪽 방향에 선언된 조인 테이블의 모든 행을 검색하되 조인조건이 맞지 않는 왼쪽 또는 오른쪽 방향의 테이블 행에 컬럼값은 NULL 검색

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

EMP 테이블에서 모든 사원의 사원번호, 사원이름, 매니저번호, 매니저이름 검색 - SELF JOIN

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

SELECT WORKER.EMPNO, WORKER.ENAME WORKER_ENAME, WORKER.MGR, MANAGER.ENAME MANAGER_ENAME
    FROM EMP WORKER LEFT JOIN EMP MANAGER ON WORKER.MGR=MANAGER.EMPNO;

SUBQUERY

• SQL 명령에 포함되어 실행되는 SELECT 명령
• 여러번의 SQL 명령으로 얻을 수 있는 결과를 하나의 SQL 명령으로 얻기 위해 사용하는 기능
• SELECT 명령(MAINQUERY)에 포함되어 실행되는 SELECT 명령(SUBQUERY)
• SUBQUERY 명령 실행 후 MAINQUERY 명령 실행
• SELECT 명령에서 SUBQUERY 명령은 FROM, WHERE, HAVING 구문에서 ( ) 안에 작성

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

• WHERE 구문에서 조건식의 비교값 대신 SUBQUERY 명령의 결과값으로 사용
• 조건식의 비교대상과 같은 자료형의 값이 하나만 검색되도록 SUBQUERY 명령 작성

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인 사원과 같은 업무를 하는 사원 중 사원번호 7900인 사원보다 많은 급여를 받는 사원의 사원번호, 사원이름, 업무, 급여 검색

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

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

• 부서이름은 DEPT 테이블에 저장되어 있으므로 테이블 조인을 이용하여 검색

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

• 테이블 조인 대신 서브쿼리를 사용하여 검색

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);

EMP 테이블에서 SALES 부서에 근무하는 사원 중 가장 많은 급여를 받는 사원의 사원번호, 사원이름, 업무, 급여 검색

오라클은 서브쿼리 내부에 다른 서브쿼리 사용 가능

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

EMP 테이블에서 부서별 평균 급여 중 가장 많은 평균 급여를 받는 부서의 부서번호, 평균급여 검색

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