JOIN은 각 테이블 간에 특정 조건을 만족하는 데이터를 합쳐서 하나의 결과(Result Set)로 조회한다.
INNER JOIN
INNER JOIN은 조인하려는 테이블들에서 공통된 컬럼의 값이 일치되는 행들을 하나의 행으로 연결하여 결과(Result Set)를 조회한다.
INNER JOIN은 공통된 컬럼에서 공통된 값이 없거나 컬럼에 값이 없는 행은 조회되지 않는다.
1) 오라클 전용 구문
SELECT 컬럼,...,컬럼
FROM 테이블1, 테이블2,...
WHERE 테이블1.컬럼명 = 테이블2.컬럼명;
FROM절에 조인하려는 테이블들을 콤마로(,) 구분하여 나열한다.
WHERE절에 매칭 시킬 컬럼명에 대한 조건을 제시한다.
1-1) 연결한 두 컬럼명이 다른 경우
**-- EMPLOYEE 테이블과 DEPARTMENT 테이블을 조인하여 사번, 직원명, 부서 코드, 부서명을 조회**
-- 하나의 행으로 만들어졌기 때문에 EMPLOYEE, DEPARTMENT 컬럼 모두 조회 가능
-- 일치하는 값이 없는 행은 조회에서 제외된다. (DEPT_CODE가 NULL인 사원, DEPT_ID가 D3, D4, D7인 사원)
SELECT EMP_ID, EMP_NAME, DEPT_ID, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID -- NULL인 경우 제외
ORDER BY DEPT_CODE;
1-2) 연결한 두 컬럼명이 같은 경우
-- EMPLOYEE 테이블과 JOB 테이블을 조인하여 사번, 직원명, 직급 코드, 직급명을 조회
**-- 방법 1) 테이블명을 이용하는 방법**
SELECT EMP_ID, EMP_NAME, EMPLOYEE.JOB_CODE, JOB.JOB_CODE, JOB_NAME
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOB_CODE = JOB.JOB_CODE; -- 모호할 때는 테이블명을 넣어준다.
**-- 방법 2) 테이블 별칭을 이용하는 방법**
SELECT E.EMP_ID,
E.EMP_NAME,
E.JOB_CODE,
J.JOB_CODE,
J.JOB_NAME
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE;
1-3) 조건이 2개 이상일 경우
**-- EMPLOYEE 테이블과 JOB 테이블을 조인해서(조건1) 직급이 대리인(조건2) 사원의 사번, 직원명, 직급명, 급여를 조회**
SELECT E.EMP_ID,
E.EMP_NAME,
J.JOB_NAME,
E.SALARY
FROM EMPLOYEE E, JOB J
**WHERE E.JOB_CODE = J.JOB_CODE AND J.JOB_NAME = '대리';**
2) ANSI 표준 구문
SELECT 컬럼, ..., 컬럼
FROM 테이블1
[INNER] JOIN 테이블2 ON (테이블1.컬럼명 = 테이블2.컬럼명);
FROM절에서 기준이 되는 테이블을 기술한다.
JOIN절에서 조인하려는 테이블을 기술 후에 매칭 시킬 컬럼에 대한 조건을 제시한다.
연결에 사용하려는 컬럼명이 같은 경우 USING(컬럼명) 구문을 사용한다.
2-1) 연결할 두 컬럼명이 다른 경우
**-- EMPLOYEE 테이블과 DEPARTMENT 테이블을 조인하여 사번, 직원명, 부서 코드, 부서명을 조회**
SELECT E.EMP_ID,
E.EMP_NAME,
E.DEPT_CODE,
D.DEPT_TITLE
FROM EMPLOYEE E
/*INNER*/ JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID);
2-2) 연결할 두 컬럼명이 같은 경우
**-- EMPLOYEE 테이블과 JOB 테이블을 조인하여 사번, 직원명, 직급 코드, 직급명을 조회
-- 방법 1) 테이블명을 이용하는 방법**
SELECT EMPLOYEE.EMP_ID,
EMPLOYEE.EMP_NAME,
JOB.JOB_CODE,
JOB.JOB_NAME
FROM EMPLOYEE
INNER JOIN JOB ON (EMPLOYEE.JOB_CODE = JOB.JOB_CODE);
**-- 방법 2) 테이블 별칭을 이용하는 방법**
SELECT E.EMP_ID,
E.EMP_NAME,
J.JOB_CODE,
J.JOB_NAME
FROM EMPLOYEE E
INNER JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE);
**-- 방법 3) USING 구문을 이용하는 방법**
SELECT EMP_ID,
EMP_NAME,
JOB_CODE,
JOB_NAME
FROM EMPLOYEE
INNER JOIN JOB USING (JOB_CODE);
**-- 방법 4) NATURAL JOIN을 이용하는 방법 (참고)**
-- 조건을 기술하지 않아도 공통된 컬럼이 있으면 자동으로 동일한 이름의 컬럼을 찾아서 JOIN
-- 단, 동일 컬럼이 하나만 존재해야 함.
SELECT EMP_ID,
EMP_NAME,
JOB_CODE,
JOB_NAME
FROM EMPLOYEE
NATURAL JOIN JOB;
1-3) 조건이 2개 이상일 경우
-- EMPLOYEE 테이블과 JOB 테이블을 조인해서(조건1) 직급이 대리인(조건2) 사원의 사번, 직원명, 직급명, 급여를 조회
SELECT E.EMP_ID,
E.EMP_NAME,
J.JOB_NAME,
E.SALARY
FROM EMPLOYEE E
INNER JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE) -- ON절에는 JOIN 조건만
WHERE J.JOB_NAME = '대리'; **-- WHERE에는 데이터를 조회하는 조건만**
OUTER JOIN
외부 조인이라고도 부른다.
OUTER JOIN은 INNER JOIN과 다르게 공통된 컬럼에서 공통된 값이 없거나컬럼에 값이 없는 행들도 함께 조회하기 위해서 사용되는 구문이다.
두 테이블 간의 JOIN 시 일치하지 않는 행도 포함시켜서 조회할 때 사용하는 구문이다.
반드시 기준이 되는 테이블(컬럼)을 지정해야 한다. LEFT, RIGHT, FULL, (+)
1) LEFT OUTER JOIN
조인에 사용한 두 테이블 중 왼쪽에 기술된 테이블의 컬럼을 기준으로 조회하려고 할 때 사용한다.
즉, 오른쪽 테이블과 매칭되는 데이터가 없어도 조회된다.
/*
1) LEFT [OUTER] JOIN
두 테이블 중 왼편에 기술된 테이블의 컬럼을 기준으로 JOIN을 진행한다.
*/
-- ANSI 구문
SELECT E.EMP_NAME,
D.DEPT_TITLE,
E.SALARY,
E.SALARY * 12
FROM EMPLOYEE E
LEFT /*OUTER*/ JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
ORDER BY DEPT_TITLE;
-- 오라클 구문
SELECT E.EMP_NAME,
D.DEPT_TITLE,
E.SALARY,
E.SALARY * 12
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_ID(+) -- 오른쪽 테이블 컬럼에 + 표시 ! 즉, 데이터가 없는데 출력해야 하는 부분(D_DEPT_ID)에 (+) 표시
ORDER BY DEPT_TITLE;
EMPLOYEE E 를 기준, EMPLOYEE E 의 값은 전부 나오기 때문에 DEPARTMENT D 은 NULL값이지만 조회가 된다.
2) RIGHT OUTER JOIN
조인에 사용한 두 테이블 중 오른쪽에 기술된 테이블의 컬럼을 기준으로 조회하려고 할 때 사용한다.
즉, 왼쪽 테이블과 매칭되는 데이터가 없어도 조회된다.
-- ANSI 구문
SELECT E.EMP_NAME,
D.DEPT_TITLE,
E.SALARY,
E.SALARY * 12
FROM EMPLOYEE E
RIGHT /*OUTER*/ JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
ORDER BY DEPT_CODE DESC;
-- 오라클 구문
SELECT E.DEPT_CODE,
E.EMP_NAME,
D.DEPT_TITLE,
E.SALARY,
E.SALARY * 12
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT_CODE(+) = D.DEPT_ID -- 왼쪽 테이블 컬럼에 + 표시 ! 즉, 데이터가 없는데 출력해야 하는 부분(E.DEPT_CODE)에 (+) 표시
ORDER BY DEPT_TITLE DESC;
DEPARTMENT D 를 기준, DEPARTMENT D 의 값은 전부 나오기 때문에 EMPLOYEE E 은 NULL값이지만 조회가 된다.
3) FULL OUTER JOIN
조인에 사용한 두 테이블이 가진 모든 테이블의 컬럼을 기준으로 조회하려고 할 때 사용한다.
즉, 왼쪽과 오른쪽 테이블 모두 매칭되는 데이터가 없어도 조회된다. (단, 오라클 구문은 지원하지 않는다.)
-- ANSI 구문
SELECT E.EMP_NAME,
D.DEPT_TITLE,
E.SALARY,
E.SALARY * 12
FROM EMPLOYEE E
FULL /*OUTER*/ JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
ORDER BY DEPT_CODE DESC;
-- 오라클 구문 (ERROR)
SELECT E.DEPT_CODE,
E.EMP_NAME,
D.DEPT_TITLE,
E.SALARY,
E.SALARY * 12
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT_CODE(+) = D.DEPT_ID(+)
ORDER BY DEPT_TITLE DESC;
CROSS JOIN
CROSS JOIN은 카테시안 곱(Cartesian Product)라고도 하며 조인되는 테이블의 모든 행들이 매핑된 데이터를 조회하기 위해 사용되는 구문이다.
두 테이블의 행들이 모두 곱해진 조합이 출력 → 방대한 데이터 출력 → 과부하의 위험
-- ANSI 구문
SELECT E.EMP_NAME, D.DEPT_TITLE
FROM EMPLOYEE E
CROSS JOIN DEPARTMENT D -- 23(EMPLOYEE) * 9(DEPARTMENT) = 207행 조회
ORDER BY EMP_NAME;
-- 오라클 구문
SELECT E.EMP_NAME, D.DEPT_TITLE
FROM EMPLOYEE E, DEPARTMENT D;
NON_EQU JOIN
NON_EQU JOIN은 조인 조건에 등호(=)를 사용하지 않고 데이터를 조회하기 위해 사용되는 구문이다.
-- EMPLOYEE 테이블과 SAL_GRADE 테이블을 비등가 조인해서 직원명, 급여, 급여 등급 조회
-- ANSI 구문
SELECT E.EMP_NAME, E.SALARY, S.SAL_LEVEL
FROM EMPLOYEE E
**INNER JOIN SAL_GRADE S ON (E.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL);**
--SELECT E.EMP_NAME, E.SALARY, S.SAL_LEVEL
--FROM EMPLOYEE E
--INNER JOIN SAL_GRADE S ON (E.SALARY >= S.MIN_SAL AND E.SALARY <= S.MAX_SAL);
-- 오라클 구문
SELECT E.EMP_NAME, E.SALARY, S.SAL_LEVEL
FROM EMPLOYEE E, SAL_GRADE S
**WHERE E.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL;**
--SELECT E.EMP_NAME, E.SALARY, S.SAL_LEVEL
--FROM EMPLOYEE E, SAL_GRADE S
--WHERE E.SALARY >= S.MIN_SAL AND E.SALARY <= S.MAX_SAL;
SELF JOIN
SELF JOIN은 동일한 테이블을 가지고 JOIN 하여 데이터를 조회하기 위해 사용되는 구문이다.
-- EMPLOYEE 테이블을 SELF JOIN 하여 사번, 직원명, 부서 코드, 사수 사번, 사수명을 조회
-- ANSI 구문
SELECT E.EMP_ID,
E.EMP_NAME,
E.DEPT_CODE,
M.EMP_ID AS "상사 사번",
M.EMP_NAME AS "상사 이름"
FROM EMPLOYEE E
LEFT OUTER JOIN EMPLOYEE M ON (E.MANAGER_ID = M.EMP_ID); -- 상사가 없는 경우에도 모두 출력
-- 오라클 구문
SELECT E.EMP_ID,
E.EMP_NAME,
E.DEPT_CODE,
M.EMP_ID AS "상사 사번",
M.EMP_NAME AS "상사 이름"
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER_ID = M.EMP_ID(+); -- 상사가 없는 경우에도 모두 출력
다중 JOIN
다중 JOIN은 여러 개의 테이블을 JOIN 하여 데이터를 조회하기 위해 사용되는 구문이다.
-- EMPLOYEE, DEPARTMENT, LOCATION 테이블을 다중 JOIN 하여 사번, 직원명, 부서명, 지역명을 조회
SELECT * FROM EMPLOYEE; -- DEPT_CODE
SELECT * FROM DEPARTMENT; -- DEPT_ID LOCATION_ID
SELECT * FROM LOCATION; -- LOCAL_CODE
-- ANSI 구문 (다중 조인은 순서가 중요 !)
SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE, L.LOCAL_NAME
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
INNER JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE); -- INNER JOIN의 순서가 바뀌면 안된다.
-- 오라클 구문
SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE, L.LOCAL_NAME
FROM EMPLOYEE E, DEPARTMENT D, LOCATION L
WHERE E.DEPT_CODE = D.DEPT_ID AND D.LOCATION_ID = L.LOCAL_CODE;
