014 dev log

서브쿼리 (SUBQUERY)

메인 SQL안에 포함된 보조역할 해주는 또하나의 SELECT문

--간단 서브쿼리 예시 1
--노옹철 사원과 같은 부서인 사원들
SELECT EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE FROM EMPLOYEE WHERE EMP_NAME='노옹철'); --D9

--간단 서브쿼리 예시 2
--전체 사원의 평균 급여보다 더 많은 급여를 받고 있는 사원들의 사번,이름,직급코드 조회
SELECT EMP_ID,EMP_NAME,JOB_CODE
FROM EMPLOYEE
WHERE SALARY>(SELECT FLOOR(AVG(SALARY)) FROM EMPLOYEE);

다중조인

3개 이상의 테이블 조인 시 사용하며 순서가 중요하다

--직급이 대리이면서 ASIA 지역에 근무하는 직원들
--사번, 사원명, 직급명, 부서명, 근무지역명, 급여 조회

--오라클 전용 구문
SELECT E.EMP_ID 사번, E.EMP_NAME 사원명, J.JOB_NAME 직급명, D.DEPT_TITLE 부서명, N.NATIONAL_NAME 근무지역명, E.SALARY 급여
FROM EMPLOYEE E, JOB J, DEPARTMENT D, LOCATION L, NATIONAL N
WHERE E.JOB_CODE = J.JOB_CODE
AND   E.DEPT_CODE = D.DEPT_ID
AND   D.LOCATION_ID = L.LOCAL_CODE
AND   L.NATIONAL_CODE = N.NATIONAL_CODE
AND   J.JOB_NAME = '대리'
AND   L.LOCAL_NAME LIKE 'ASIA%';

--ANSI 구문
SELECT E.EMP_ID 사번, E.EMP_NAME 사원명, J.JOB_NAME 직급명, D.DEPT_TITLE 부서명, N.NATIONAL_NAME 근무지역명, E.SALARY 급여
FROM EMPLOYEE E
LEFT JOIN JOB J ON(E.JOB_CODE=J.JOB_CODE)
LEFT JOIN DEPARTMENT D ON(E.DEPT_CODE=D.DEPT_ID)
LEFT JOIN LOCATION L ON(D.LOCATION_ID=L.LOCAL_CODE)
LEFT JOIN NATIONAL N ON(L.NATIONAL_CODE=N.NATIONAL_CODE)
WHERE J.JOB_NAME = '대리'
AND   L.LOCAL_NAME LIKE 'ASIA%';

--70년대생이면서 여자이고, 성이 전씨인 직원들
--사원명, 주민번호, 부서명, 직급명 조회

--오라클 전용 구문
SELECT EMP_NAME 사원명, EMP_NO 주민번호, DEPT_TITLE 부서명, JOB_NAME 직급명
FROM EMPLOYEE E, DEPARTMENT D, JOB J
WHERE E.DEPT_CODE=D.DEPT_ID
AND   E.JOB_CODE=J.JOB_CODE
AND   SUBSTR(EMP_NO,1,2) LIKE '7%'
AND   SUBSTR(EMP_NO,8,1) IN('2','4')
AND   EMP_NAME LIKE '전%';

--ANSI 구문
SELECT EMP_NAME 사원명, EMP_NO 주민번호, DEPT_TITLE 부서명, JOB_NAME 직급명
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON(E.DEPT_CODE=D.DEPT_ID)
LEFT JOIN JOB J ON(E.JOB_CODE=J.JOB_CODE)
WHERE SUBSTR(EMP_NO,1,2) LIKE '7%'
AND   SUBSTR(EMP_NO,8,1) IN('2','4')
AND   EMP_NAME LIKE '전%';

--사번, 사원명, 직급명, 급여등급, 구분 조회
--    이 때, 구분에 해당하는 값은
--    급여등급이 S1, S2 인 경우 '고급'
--    급여등급이 S3, S4 인 경우 '중급'
--    급여등급이 S5, S6 인 경우 '초급' 으로 조회

--오라클 전용 구문
SELECT E.EMP_ID 사번, E.EMP_NAME 사원명, J.JOB_NAME 직급명, SAL_LEVEL 급여등급
        ,CASE WHEN SAL_LEVEL IN ('S1','S2') THEN '고급'
        WHEN SAL_LEVEL IN ('S3','S4') THEN '중급'
        WHEN SAL_LEVEL IN ('S5','S6') THEN '초급'
        END 구분
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE=J.JOB_CODE;

비등가 조인 (NON EQUAL JOIN)

'=' 등호를 사용하지 않고 다른 비교 연산자를 사용하여 조인한다

(>, <, >=, <=, BETWEEN A AND B)

지정한 컬럼 값들이 일치하는 경우가 아닌 '범위'에 포함되는 경우 매칭하여 조회한다

--오라클 국문
SELECT E.EMP_NAME,E.SALARY,S.SAL_LEVEL
FROM EMPLOYEE E, SAL_GRADE S
WHERE E.SALARY BETWEEN MIN_SAL AND MAX_SAL;

--ANSI
SELECT EMP_NAME,SALARY,S.SAL_LEVEL
FROM EMPLOYEE E
JOIN SAL_GRADE S ON (SALARY BETWEEN MIN_SAL AND MAX_SAL);

자체조인 (SELF JOIN)

같은 테이블끼리 조인하는 경우 사용하며 테이블 별칭 붙여서 서로 다른 테이블처럼 조인한다

--사원의 사번,사원명,사수의사번,사수명
--ORACLE 구문
SELECT E.EMP_ID 사번, E.EMP_NAME 직원명, M.MANAGER_ID 사수사번, M.EMP_NAME 사수명
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER_ID = M.EMP_ID(+); --LEFT OUTER JOIN
--ANSI 구문
SELECT E.EMP_ID 사번, E.EMP_NAME 사원명, E.MANAGER_ID 사수사번, M.EMP_NAME 사수명
FROM EMPLOYEE E
LEFT OUTER JOIN EMPLOYEE M ON (E.MANAGER_ID=M.EMP_ID);

카테시안 곱 (CARTESIAN PRODUCT) - ORACLE / 교차조인 (CROSS JOIN) - ANSI

모든 테이블의 각 행들이 서로 매핑 된 데이터가 조회된다

즉, 두 테이블의 행이 모두 곱해진 행 조합이 출력된다

→ 각각 N개, M개의 행을 가진 테이블들의 카테시안 곱의 결과는 N*M행

→ 모든 경우의 수를 다 따져서 조회할 경우 사용

→ 방대한 데이터를 출력 (과부화 위험)

--사원명,부서명의 모든 경우의 수를 출력

--오라클구문
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT; --23*9=207행 조회

--ANSI구문
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
CROSS JOIN DEPARTMENT;
--WHERE절에 기술하는 조인 조건이 잘못되었거나 아예 없을 경우 발생하는 구문

포괄조인 - ORACLE / 외부조인 (OUTER JOIN) - ANSI

테이블간 JOIN 시 일치하지 않는 행도 포함시켜서 조회 가능하게 한다

기준이 되는 테이블을 지정하여 LEFT 또는 RIGHT를 필수로 지정한다

일치하는행 + 기준이 되는 테이블 기준으로 일치하지 않는 행도 포함 시켜서 조회된다

LEFT OUTER JOIN

왼쪽에 기술한 테이블 기준으로 JOIN

//ORACLE
SELECT 컬럼명, ...
FROM 테이블명1, 테이블명2, ...
WHERE 기준컬럼=조인컬럼(+);

//ANSI
SELECT 컬럼명, ...
FROM 기준테이블명
LEFT OUTER JOIN 조인테이블명 ON (기준컬럼=조인컬럼);

--오라클 구문
SELECT EMP_NAME,SALARY,DEPT_TITLE
FROM EMPLOYEE,DEPARTMENT
WHERE DEPT_CODE=DEPT_ID(+); 
--기준으로 삼지 않는 테이블의 컬럼명에 (+)를 붙여준다

--ANSI구문
SELECT EMP_NAME,SALARY,DEPT_TITLE
FROM EMPLOYEE
LEFT /*OUTER*/ JOIN DEPARTMENT ON (DEPT_CODE=DEPT_ID); --OUTER 생략가능

--EMPLOYEE테이블이 기준이 되었기 때문에 EMPLOYEE테이블에 존재하는 데이터는 모두 조회해옴

RIGHT OUTER JOIN

오른쪽에 기술한 테이블 기준으로 JOIN

//ORACLE
SELECT 컬럼명, ...
FROM 테이블명1, 테이블명2, ...
WHERE 조인컬럼(+)=기준컬럼;

//ANSI
SELECT 컬럼명, ...
FROM 기준테이블명
RIGHT OUTER JOIN 조인테이블명 ON (기준컬럼=조인컬럼);

--ORACLE 구문
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE(+)=DEPT_ID;

--ANSI 구문
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE
RIGHT /*OUTER*/ JOIN DEPARTMENT ON (DEPT_CODE=DEPT_ID); --OUTER 생략가능

FULL OUTER JOIN

두 테이블이 가진 모든 행을 조회 (ANSI 구문 존재)

//ANSI
SELECT 컬럼명, ...
FROM 기준테이블명
FULL OUTER JOIN 조인테이블명 ON (기준컬럼=조인컬럼);

--ANSI 구문
SELECT EMP_NAME,SALARY,DEPT_TITLE
FROM EMPLOYEE
FULL /*OUTER*/ JOIN DEPARTMENT ON (DEPT_CODE=DEPT_ID); --OUTER 생략 가능

등가조인 (EQUAL JOIN) - ORACLE / 내부조인 (INNER JOIN) - ANSI

연결 시키고자 하는 컬럼의 값이 일치하는 행들만 조인되어 조회된다 (일치하지 않는 값들을 결과에서 제외)

동등비교연산자 = 사용 ("일치한다"라는 조건을 제시)

등가조인(EQUAL JOIN) - ORACLE

SELECT 조회할 컬럼명 나열
FROM 조회할 테이블명 나열
WHERE 연결할 컬럼에 대한 조건 제시("=")

--전체 사원들의 사번,사원명,부서코드,부서명 출력
--1)연결할 두 컬럼명이 다를 경우 EX)EMPLOYEE-'DEPT_CODE' / DEPARTMENT-'DEPT_ID'
SELECT EMP_ID,EMP_NAME,DEPT_CODE,DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE=DEPT_ID; 
--부서코드가 일치하지 않는 값은 조회되지 않음(NULL, D3, D4, D7은 조회가 안됨 자료가 없기 때문!)

--2)연결할 두 컬럼명이 같을 경우 EX)EMPLOYEE-'JOB_CODE' / JOB-'JOB_CODE'
--방법1)테이블명 붙여서 명시 [표현법]테이블명.컬럼명
SELECT EMP_ID,EMP_NAME,EMPLOYEE.JOB_CODE,JOB_NAME
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOB_CODE=JOB.JOB_CODE;
--방법2)별칭을 붙여서 명시
SELECT EMP_ID,EMP_NAME,E.JOB_CODE,JOB_NAME
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE=J.JOB_CODE;

--조건제시
SELECT EMP_ID, EMP_NAME, BONUS
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID
AND DEPT_TITLE='인사관리부';

내부조인(INNER JOIN) - ANSI

//내부조인 (ANSI) : ON구문
SELECT 조회할 컬럼명 나열
FROM 기준 삼을 테이블명 1개 제시
JOIN 조인할 테이블명 1개 제시 ON (연결할 컬럼에 대한 조건 제시 ("="))

//내부조인(ANSI구문) : USING구문
SELECT 조회할 컬럼명 나열
FROM 기준 삼을 테이블명 1개 제시
JOIN 조인할 테이블명 1개 제시 USING (연결할 컬럼명 1개 제시)

--전체사원들의 사번,사원명,직급코드,직급명 조회

--연결할 두 컬럼이 같을 경우 EX)EMPLOYEE-'JOB_CODE' / JOB-'JOB_CODE'
--ON구문
SELECT EMP_ID,EMP_NAME,E.JOB_CODE,JOB_NAME
FROM EMPLOYEE E
/*INNER*/JOIN JOB J ON(E.JOB_CODE=J.JOB_CODE); --INNER 보통 생략
--USING구문(연결할 컬럼명이 같을경우만)
SELECT EMP_ID,EMP_NAME,JOB_CODE,JOB_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE);--컬럼명이 동일하여 어떤 테이블 컬럼인지 명시하지 않아도 매칭

--cf.자연조인(NATURAL JOIN):등가조인 방법중 하나
--동일한 타입과 이름을 가진 컬럼을 조인 조건으로 이용하는 방법
SELECT EMP_ID,EMP_NAME,JOB_CODE,JOB_NAME
FROM EMPLOYEE
NATURAL JOIN JOB;
--두개의 테이블에 일치하는 컬럼이 단 한개만 존재할 경우 알아서 매칭

--조건제시
SELECT EMP_ID, EMP_NAME, BONUS
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE=DEPT_ID)
WHERE DEPT_TITLE='인사관리부';

JOIN 정의

두 개 이상의 테이블에서 데이터를 같이 조회하고자 할 때 사용하는 구문이다

조회 결과는 하나의 결과물(RESULT SET)로 출력한다

JOIN 필요성

관계형 데이터베이스에서는 최소한의 데이터로 각각 테이블에서 데이터를 보관하는데,

중복을 최소화 하기 위해 JOIN을 사용한다

→ JOIN을 이용하여 여러 개의 테이블 간 관계를 맺어 사용

→ 테이블 간의 연결고리(컬럼)를 매칭 시켜 조회

문법상 분류

JOIN은 크게 "오라클 전용 구문"과 "ANSI(미국 국립 표준 협회) 구문"으로 나뉜다

개념상 분류