-- 조인(JOIN)
-- JOIN : 두개의 테이블을 하나로 합쳐서 결과를 조회한다.
-- 오라클 전용 구문
-- FROM절에 ','로 구분하여 합치기 될 테이블명을 기술하고
-- WHERE절에 합치기에 사용할 컬럼명을 명시한다.
-- 연결에 사용할 두 컬럼명이 다른 경우
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, DEPT_TITLE
FROM EMPLOYEE
, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID;
-- 연결에 사용할 두 컬럼명이 같은 경우
SELECT
EMPLOYEE.EMP_ID
, EMPLOYEE.EMP_NAME
, EMPLOYEE.JOB_CODE
, JOB.JOB_NAME
FROM EMPLOYEE
, JOB
WHERE EMPLOYEE.JOB_CODE = JOB.JOB_CODE;
-- 테이블에 별칭 사용
SELECT
E.EMP_ID
, E.EMP_NAME
, E.JOB_CODE
, J.JOB_NAME
FROM EMPLOYEE E
, JOB J
WHERE E.JOB_CODE = J.JOB_CODE;
-- ANSI 표준 구문
-- 연결에 사용할 컬럼명이 같은 경우
-- USING(컬럼명)을 사용한다.
SELECT
EMP_ID
, EMP_NAME
, JOB_CODE
, JOB_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE);
-- 컬럼명이 같은 경우에도 ON()을 사용할 수 있다.
SELECT
E.EMP_ID
, E.EMP_NAME
, E.JOB_CODE
, J.JOB_NAME
FROM EMPLOYEE E
JOIN JOB J ON( E.JOB_CODE = J.JOB_CODE);
-- 연결에 사용할 컬럼명이 다른 경우 ON()을 사용
SELECT
E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, D.DEPT_TITLE
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID);
-- 부서 테이블(DEPARTMENT)과 지역 테이블(LOCATION)을 조인하여 테이블에 모든 데이터를 조회하세요
-- 오라클 전용
SELECT
D.DEPT_ID, D.DEPT_TITLE, D.LOCATION_ID
, L.LOCAL_CODE, L.NATIONAL_CODE, L.LOCAL_NAME
FROM DEPARTMENT D
, LOCATION L
WHERE D.LOCATION_ID = L.LOCAL_CODE;
-- ANSI 표준
SELECT
D.DEPT_ID, D.DEPT_TITLE, D.LOCATION_ID
, L.LOCAL_CODE, L.NATIONAL_CODE, L.LOCAL_NAME
FROM DEPARTMENT D
JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE);
-- 조인은 기본이 EQUAL JOIN이다. (EQU JOIN이라고도 함)
-- 연결되는 컬럼의 값이 일치하는 행들만 조인된다.
-- 일치하는 값이 없는 행은 조인에서 제외되는 것을 INNER JOIN이라고 한다.
-- OUTER JOIN : 두 테이블의 지정하는 컬럼 값이 일치하지 않는 행도 조언에 포함을 시킨다.
-- 반드시 OUTER JOIN임을 명시해야한다.
-- 1. LEFT OUTER JOIN : 합치기에 사용한 두 테이블 중 왼편에 기술된 테이블의 행의 수를 기준으로 SUM
-- 2. RIGHT OUTER JOIN : 합치기에 사용한 두 테이블 중 오른편에 기술된 테이블의 행의 수를 기준으로 SUM
-- 3. FULL OUTER JOIN : 합치기에 사용한 두 테이블이 가진 모든 행을 결과에 포함하여 JOIN
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
-- LEFR OUTER JOIN
-- 오라클 전용 구문
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+);
-- ANSI 표준
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
-- LEFT OUTER JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID); /outer 생략 가능/
-- RIGHT OUTER JOIN
-- 오라클 전용
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
, DEPARTMENT
WHERE DEPT_CODE(+) = DEPT_ID;
-- ANSI 표준
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
RIGHT OUTER JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
-- RIGHT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
-- FULL OUTER JOIN
-- 오라클 전용 구문으로는 FULL OUTER JOIN을 하지 못한다.
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
, DEPARTMENT
WHERE DEPT_CODE(+) = DEPT_ID(+);
-- ANSI 표준
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
-- FULL OUTER JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
FULL JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
-- CROSS JOIN : 카테이션곱이라고도 한다.(모든경우의수..?)
-- 조인되는 테이블의 각 행들이 모두 매핑된 데이터가 검색되는 방법이다.
SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
CROSS JOIN DEPARTMENT;
-- NON EQUAL JOIN(NON EQU JOIN)
-- 지정한 컬럼의 값이 일치하는 경우가 아닌, 값의 범위에 포함되는 행들을 연결한느 방식
-- ANSI 표준
SELECT
E. EMP_NAME
, E. SALARY
, E. SAL_LEVEL
, S. SAL_LEVEL
FROM EMPLOYEE E
JOIN SAL_GRADE S ON(E.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL);
-- 오라클전용
SELECT
E. EMP_NAME
, E. SALARY
, E. SAL_LEVEL
, S. SAL_LEVEL
FROM EMPLOYEE E
, SAL_GRADE S
WHERE E.SALARY BETWEEN S.MIN_SAL AND S.MAX_SAL;
-- SELF JOIN : 같은 테이블을 조인하는 경우 (자기자신과 조인을 맺는 것)
-- 오라클 전용
SELECT
E1.EMP_ID
, E1.EMP_NAME 사원이름
, E1. DEPT_CODE
, E1. MANAGER_ID
, E2. EMP_NAME 관리자이름
FROM EMPLOYEE E1
, EMPLOYEE E2
WHERE E1.EMP_ID = E2.MANAGER_ID;
-- ANSI 표준
SELECT
E1.EMP_ID
, E1.EMP_NAME 사원이름
, E1. DEPT_CODE
, E1. MANAGER_ID
, E2. EMP_NAME 관리자이름
FROM EMPLOYEE E1
JOIN EMPLOYEE E2 ON( E1.EMP_ID = E2. MANAGER_ID);
-- 다중 JOIN : N개의 테이블을 조회할 때 사용
-- 오라클 전용
-- 조인 순서 상관없다.
SELECT
E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, 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;
-- ANSI 표준
-- 조인 순서 중요함
SELECT
E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, D.DEPT_TITLE
, L.LOCAL_NAME
FROM EMPLOYEE E
-- JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE);
-- 직급이 대리이면서 아시아 지역에 근무하는 직원 조회
-- 사번, 이름, 직급명, 부서명, 근무지역명, 급여를 조회하세요
-- ANSI 표준
SELECT
E.EMP_ID 사번
, E.EMP_NAME 이름
, J.JOB_NAME 직급명
, D.DEPT_TITLE 부서명
, L.LOCAL_NAME 근무지역명
, E.SALARY 급여
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
WHERE J.JOB_NAME = '대리'
AND L.LOCAL_NAME LIKE 'ASIA%';
-- 오라클 전용
SELECT
E.EMP_ID 사번
, E.EMP_NAME 이름
, J.JOB_NAME 직급명
, D.DEPT_TITLE 부서명
, L.LOCAL_NAME 근무지역명
, E.SALARY 급여
FROM EMPLOYEE E
, DEPARTMENT D
, JOB J
, LOCATION L
WHERE E.JOB_CODE = J.JOB_CODE
AND E.DEPT_CODE = D.DEPT_ID
AND D.LOCATION_ID = L.LOCAL_CODE
AND J.JOB_NAME = '대리'
AND L.LOCAL_NAME LIKE 'ASIA%';
-- JOIN 연습문제
-- 1. 주민번호가 70년대 생이면서 성별이 여자이고,
-- 성이 전씨인 직원들의 사원명, 주민번호, 부서명, 직급명을 조회하시오.
-- ANSI 표준
SELECT
E.EMP_NAME 사원명
, E.EMP_NO 주민번호
, D.DEPT_TITLE 부서명
, J.JOB_NAME 직급명
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
WHERE SUBSTR(E.EMP_NO, 8,1) =2
AND E.EMP_NAME LIKE '전%'
AND SUBSTR(E.EMP_NO, 1, 2) >= 70
AND SUBSTR(E.EMP_NO, 1, 2) < 80;
-- 오라클전용
SELECT
E.EMP_NAME 사원명
, E.EMP_NO 주민번호
, D.DEPT_TITLE 부서명
, J.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(E.EMP_NO, 8,1) =2
AND E.EMP_NAME LIKE '전%'
AND SUBSTR(E.EMP_NO, 1, 2) >= 70
AND SUBSTR(E.EMP_NO, 1, 2) < 80;
-- 2. 이름에 '형'자가 들어가는 직원들의
-- 사번, 사원명, 부서명을 조회하시오.
-- ANSI 표준
SELECT
E.EMP_ID 사번
, E.EMP_NAME 이름
, D.DEPT_TITLE 부서명
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
WHERE E.EMP_NAME LIKE '%형%';
-- 오라클전용
SELECT
E.EMP_ID 사번
, E.EMP_NAME 이름
, D.DEPT_TITLE 부서명
FROM EMPLOYEE E
, DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_ID
AND E.EMP_NAME LIKE '%형%';
-- 3. 해외영업팀에 근무하는 사원명,
-- 직급명, 부서코드, 부서명을 조회하시오.
-- ANSI표준
SELECT
E.EMP_NAME 사원명
, J.JOB_NAME 직급명
, D.DEPT_ID 부서코드
, D.DEPT_TITLE 부서명
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
WHERE D.DEPT_TITLE LIKE '해외%';
-- WHERE D.DEPT_ID IN ('D5', 'D6', 'D7');
-- 오라클전용
SELECT
E.EMP_NAME 사원명
, J.JOB_NAME 직급명
, D.DEPT_ID 부서코드
, D.DEPT_TITLE 부서명
FROM EMPLOYEE E
, DEPARTMENT D
, JOB J
WHERE E.DEPT_CODE = D.DEPT_ID
AND E.JOB_CODE = J.JOB_CODE
AND D.DEPT_TITLE LIKE '해외%';
-- 4. 보너스포인트를 받는 직원들의 사원명,
-- 보너스포인트, 부서명, 근무지역명을 조회하시오.
-- ANSI표준
SELECT
E.EMP_NAME 사원명
, E.BONUS 보너스포인트
, D.DEPT_TITLE 부서명
, L.LOCAL_NAME 근무지역명
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
WHERE E.BONUS IS NOT NULL;
-- WHERE E.BONUS NOT LIKE 'null';
-- 오라클전용
SELECT
E.EMP_NAME 사원명
, E.BONUS 보너스포인트
, 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
AND E.BONUS NOT LIKE 'null';
-- 5. 부서코드가 D2인 직원들의 사원명,
-- 직급명, 부서명, 근무지역명을 조회하시오.
-- ANSI 표준
SELECT
E.EMP_NAME 사원명
, J.JOB_NAME 직급명
, D.DEPT_TITLE 부서명
, L.LOCAL_NAME 근무지역명
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
JOIN JOB J ON(J.JOB_CODE = E.JOB_CODE)
WHERE E.DEPT_CODE = 'D2';
-- 오라클전용
SELECT
E.EMP_NAME 사원명
, J.JOB_NAME 직급명
, D.DEPT_TITLE 부서명
, L.LOCAL_NAME 근무지역명
FROM EMPLOYEE E
, DEPARTMENT D
, LOCATION L
, JOB J
WHERE E.DEPT_CODE = D.DEPT_ID
AND D.LOCATION_ID = L.LOCAL_CODE
AND J.JOB_CODE = E.JOB_CODE
AND E.DEPT_CODE = 'D2';
-- 6. 한국(KO)과 일본(JP)에 근무하는 직원들의
-- 사원명, 부서명, 지역명, 국가명을 조회하시오.
-- ANSI 표준
SELECT
E.EMP_NAME 사원명
, D.DEPT_TITLE 부서명
, L.LOCAL_NAME 지역명
, N.NATIONAL_NAME 국가명
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
JOIN NATIONAL N ON(L.NATIONAL_CODE = N.NATIONAL_CODE)
WHERE NATIONAL_NAME IN ('한국', '일본');
-- 오라클전용
SELECT
E.EMP_NAME 사원명
, D.DEPT_TITLE 부서명
, L.LOCAL_NAME 지역명
, N.NATIONAL_NAME 국가명
FROM EMPLOYEE E
, DEPARTMENT D
, LOCATION L
, NATIONAL N
WHERE E.DEPT_CODE = D.DEPT_ID
AND D.LOCATION_ID = L.LOCAL_CODE
AND L.NATIONAL_CODE = N.NATIONAL_CODE
AND NATIONAL_NAME IN ('한국', '일본');
서브쿼리
하나의 SELECT 문장의 절 안에 포함된 또 하나의 SELECT 문장이다.
서브쿼리는 메인쿼리가 실행되기 이전에 한번만 실행되며 비교연산자의 오른쪽에 기술해야하며 반드시 괄호로 묶어야 한다.
또한 서브쿼리와 비교할 항목은 반드시 서브쿼리의 SELECT한 항목의 개수와 자료형을 일치 시켜야한다.
서브쿼리의 유형
단일행 서브쿼리 -> 서브쿼리의 조회 결과 값의 개수가 1개일때
FROM EMPLOYEE 왼편 base table (기준)
JOIN DEPARTMENT 오른편
-----한줄로 보면 JOIN을 기준으로 EMPLOYEE는 왼쪽 DEPARTMENT는 오른쪽이다
LEFT -> 왼편함수
EMPLOYEE
-> 부서있는 JOIN
-> 부서없는것까지 할려면 LEFT JOIN
RIGHT -> 오른편함수
DEPARTMENT
다중행일때 =으로는 값비교가 안되어서 IN을 사용한다
