D29 오라클4 <JOIN>
20241104 D29
<JOIN>
- 두 개 이상의 테이블에서 함께 데이터를 조회하고자 할 때 사용되는 구문
- 조회 결과는 하나의 결과물(RESULT SET)으로 반환
JOIN은 "오라클 전용구문" , ANSI 표준구문으로 나뉜다.
JOIN을 해야하는 이유?
- 관계형 데이터베이스에서는 데이터의 중복을 피하기 위해
- 최소한의 데이터만 각 테이블에 보관을 하고 있다
EX) 사원정보는EMPLOYEE, 부서정보는 DEPARTMENT, 직급정보는 JOB등...- 따라서 내가 사원의 정보와 부서의 정보를 하나의 결과 값으로 보기 위해서는
- 여러개의 테이블을 관계가 있는 칼럼을 통해 JOIN문을 사용하여 조회
JOIN문의 분류
-- JOIN을 사용 하지 않는 경우
--전체 사원들의 사번, 사원명, 부서코드, 부서명까지 알아내야 한다면?
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE;
SELECT DEPT_ID, DEPT_TITLE
FROM DEPARTMENT;
1. 등가조인( EQUI JOIN) / 내부조인 ( INNER JOIN)
- 연결시키고자 하는 칼럼의 값이 서로 일치 하는 행들만 조인
- 조인시 동등비교연사자를 사용하여 조인 조건을 제시
[표현법]
등가조인 (오라클)
SELECT 칼럼명들
FROM 조인 테이블명들 나열
WHERE 연결 할 칼럼에 대한 조건을 제시(=)
내부조인(ANSI 구문)
SELECT 칼럼명들
FROM 기준 삼을 테이블 1개만 제시
JOIN 위 테이블과 연걸 할 테이블 1개만 제시
1.ON (연결 할 칼럼에 대한 조건 제시(=))
2.USING (연결 할 칼럼 1개만 제시) 단 , 연결 할 칼럼의 칼럼명이 100% 동일해야 한다.오라클 전용 구문
- FROM 절에 조회할 테이블들을 나열,
- WHERE절에 조인에 대한 조건을 제시
-- 전체 사원들의 사번 이름 부서코드 부서명을 알아내고자 한다.
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_ID, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT --카데시안의 곱 방식
ORDER BY EMP_ID; --정렬을 통해 확인 결과 모든 행의 결과값을 반환
--등가조인 방식
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_ID, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID; --NULL 값은 제외되고 출력
-- 전체 사원들의 사번, 사원명, 직급코드, 직급명
SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
FROM EMPLOYEE, JOB
WHERE JOB_CODE = JOB_CODE;
-- 에러) COLUMN AMBIGUOUSIY SPECIFIED -> 컬럼명이 애매하다.
--즉, JOB_CODE와 같이 EMPLOYEE와 JOB에 모두 존재하는 칼럼명일 경우
--어떤 테이블의 JOB_CODE 인지 분명한 정의가 필요
--해결방법1) 테이블에 별칭 부여
SELECT EMP_ID, EMP_NAME, E.JOB_CODE, JOB_NAME
FROM EMPLOYEE "E" , JOB "J" --AS키워드 사용 불가 "" 는 사용 가능
WHERE E.JOB_CODE = J.JOB_CODE;
--해결방법2) 테이블명을 컬럼앞에 붙여주기
SELECT EMP_ID, EMP_NAME, EMPLOYEE.JOB_CODE, JOB_NAME
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOB_CODE = JOB.JOB_CODE;ANSI 구문
- FROM절 뒤에 테이블 "한개만" 기술
- 그뒤에 JOIN절을 붙여서 조회 하고자 하는 테이블을 기술, 매칭할 칼럼에 대한 조건으도 함께 기술
- 칼럼에 대한 조건을 제시시 ON구문 / USING 구문 2개가 존재
-- 전체 사원들의 사번, 이름, 부서코드, 부서명 알아내기
--ON구문
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE
FROM EMPLOYEE
/* INNER */JOIN DEPARTMENT ON (DEPT_ID = DEPT_CODE) ;
-- USING
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT USING ( DEPT_CODE ) ;
-- USING구문은 조인할 두테이블이 동일한 칼럼명을 가지고 있을때만 사용 가능
-- 사번, 사원명, 직급코드 , 직급명 -> USING 구문 사용가능.
--1) ON 구문
SELECT EMP_ID, EMP_NAME, E.JOB_CODE, JOB_NAME
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE);
--2) USING 구문 : 조인할 테이블 간의 칼럼명이 동일한 경우 사용이 가능
-- 이름이 동일한 칼럼명을 작성하면 해당 칼럼을 기준으로 행을 매칭
SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE); -- USING에 사용된 칼럼은 RESULT SET에서 하나의 칼럼으로 통합
--자연조인(NATURAL JOIN) : 등가조인의 방법 중 하나
--=> 두 테이블을 JOIN 할 때 "동일한 타입과 이름"을 가진 칼럼을 조인 조건으로 사용하는 JOIN문
SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
FROM EMPLOYEE
NATURAL JOIN JOB; -- 조인에 대한 조건을 제시하지 않았다.
-- 조인시 추가적인 조건 제시하기.
-- 직급이 대리인 사원들의 정보를 조회 ( 사번, 사원명, 월급, 직급명)
-- 오라클 전용 구문
SELECT EMP_ID, EMP_NAME, SALARY,JOB_NAME
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE
AND JOB_NAME = '대리';
-- ANSI구문
SELECT EMP_ID, EMP_NAME, SALARY,JOB_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '대리';
SELECT EMP_ID, EMP_NAME, SALARY, JOB_NAME
FROM EMPLOYEE
JOIN JOB ON (EMPLOYEE.JOB_CODE = JOB.JOB_CODE
AND JOB_NAME = '대리');연습부분
1. 부서가 '인사관리부' 인 사원들의 사번 사원명 보너스를 조회 (오라클/ ANSI구문으로 나눠서)
--오라클
SELECT EMP_ID, EMP_NAME,DEPT_CODE, BONUS
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_ID
AND DEPT_TITLE = '인사관리부';
--ANSI구문
SELECT EMP_ID, EMP_NAME,DEPT_CODE, BONUS
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_ID = DEPT_CODE --AND DEPT_TITLE = '인사관리부';
WHERE DEPT_TITLE = '인사관리부';
--2. 부서가 '총무부'가 아닌 사원들의 사원명 급여 입사일을 조회 (오라클/ ANSI구문으로 나눠서)
--오라클
SELECT EMP_NAME,DEPT_CODE, SALARY, HIRE_DATE
FROM EMPLOYEE , DEPARTMENT
WHERE DEPT_CODE = DEPT_ID
AND DEPT_TITLE != '총무부';
--ANSI구문
SELECT EMP_NAME,DEPT_CODE, SALARY, HIRE_DATE
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
WHERE DEPT_TITLE != '총무부';
--3. 보너스를 받는 사월들의 사번 사원명 보너스 부서명을 조회 (오라클/ ANSI구문으로 나눠서)
--오라클
SELECT EMP_ID, EMP_NAME, BONUS, DEPT_TITLE
FROM EMPLOYEE , DEPARTMENT
WHERE DEPT_CODE = DEPT_ID
AND BONUS IS NOT NULL;
--ANSI구문
SELECT EMP_ID, EMP_NAME, BONUS, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
WHERE BONUS IS NOT NULL;
--4. 아래의 두 테이블을 참고하여 부서코드 부서명 지역코드 국가명을 조회 (오라클/ ANSI구문으로 나눠서)
--오라클
SELECT * FROM DEPARTMENT;
SELECT* FROM NATION;
SELECT DEPT_ID, DEPT_TITLE, LOCATION_ID, NATIONAL_NAME
FROM DEPARTMENT, LOCATION L, NATION N
WHERE LOCATION_ID = LOCAL_CODE
AND L.NATIONAL_CODE = N.NATIONAL_CODE;
--ANSI구문
SELECT DEPT_ID, DEPT_TITLE, LOCATION_ID, NATIONAL_NAME
FROM DEPARTMENT
JOIN LOCATION ON LOCATION_ID = LOCAL_CODE
JOIN NATION USING(NATIONAL_CODE);
-- 전체 사원들의 사원명 급여 부서명
SELECT EMP_NAME, SALARY, DEPT_CODE, DEPT_ID, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID; --DEPT_CODE NULL인 사원은 제외되어 출력2. 포괄조인 / 외부조인(OUTER JOIN)
- 테이블간의 JOIN시 "일치하지 않는 행도" 포함시켜서 조회.
- 단, 반드시 LEFT/RIGHT를 지정해 줘야 한다.
-- 전체 사원의 사번 급여 부서명
-- 1) LEFR OUTER JOIN : 두개의 테이블 중 왼편에 기술된 테이블을 기준으로 JOIN을 한다.
-- , 왼편에 기술된 테이블의 행은 JOIN조건을 만족하지 못하더라도 항상 데이터를 조회한다.
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+);
--EMPLOYEE 테이블의 행은 왼쪽의 JOIN조건을 만족하지 못하더라고 항상 조회
-- ANSI 구문
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE
LEFT /*OUTER*/ JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID; --OUTER 생략가능
-- EMPLOYEE 테이블을 기준으로 조회, EMPLOYEE에 존재하는 데이터는 모두 조회
-- 2) REGHT OUTER JOIN : 두 테이블 중 오른쪽에 기술된 테이블을 기준으로 데이터 JOIN.
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;
-- 3) FULL OUTER JOIN : 두 테이블이 가진 모든 행을 조회
-- INNER 해당하는 행들 + LEFT OUTER 해당하는 행 + RIGHT OUTER행을 모두 조회
-- ANSI 구문
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE
FULL /* OUTER JOIN */ DEPARTMENT ON DEPT_CODE = DEPT_ID ;
-- 오라클 전용구문은 문법상 FULL OUTER JOIN이 불가하다.3. 카테이산 곱(CARTESIAN PRODUCT) / 교차 조인(CROSS JOIN)
- 모든 테이블의 각 행들이 서로 매핑된 데이터가 조회된다(곱집합)
- 두 테이블의 행들이 모두 곱해진 행들의 조합이 출력
ex) A테이블에 N개의 행 B테이블에 M개의 행이 존재할 경우 결과 행의 집합의 수는 N*M행 즉 모든 경우희 수를 조회,방대한 데이터 출력, 성능이 느려 질 수 있다.--사원명, 부서명 --사원 테이블 (24개의 행) --부서 테이블 (9개의 행이 있다) SELECT EMP_NAME, DEPT_TITLE FROM EMPLOYEE, DEPARTMENT; -- ANSI SELECT EMP_NAME, DEPT_TITLE FROM EMPLOYEE CROSS JOIN DEPARTMENT;
4. 비등가 조인(NON EQUI JOIN)
- '=' 를 사용하지 않는 JOIN문, 다른 비교 연사자를 써서 JOIN함(>, < >=, <=, BETWEEN 등)
- => 지정한 칼럼 값들이 일치하는 경우가 아니라 일치하는 "범위"에 포함되는 경우 매칭해서 조회
-- 사원명 급여
SELECT EMP_NAME, SALARY
FROM EMPLOYEE;
SELECT *
FROM SAL_GRADE; --단순 조회
-- 사원명, 급여, 급여등급(SAL_LEVER)
-- 오라클 전용 구문
SELECT EMP_NAME, SALARY, SAL_GRADE.SAL_LEVEL
FROM EMPLOYEE, SAL_GRADE
--WHERE SALARY >= MIN_SAL AND SALARY <= MAX_SAL;
WHERE SALARY BETWEEN MIN_SAL AND MAX_SAL;
-- ANSI 구문
SELECT EMP_NAME, SALARY, SAL_GRADE.SAL_LEVEL
FROM EMPLOYEE
JOIN SAL_GRADE ON (SALARY BETWEEN MIN_SAL AND MAX_SAL);5. 자체조인 (SELF JOIN)
- 같은 테이블 JOIN시 사용.
- 자체조인의 경우 조회하고자 하는 칼럼명이 반드시 겹치기 때문에 항상 별칭 부여 필요
SELECT * FROM EMPLOYEE E; -- 모든 사원에 대한 정보 SELECT * FROM EMPLOYEE M; -- 사수에 대한 정보를 도출하기 위한 테이블
-- 사원의 사번, 사원명, 사수의 사번, 사수명
-- 오라클 전용 구문
SELECT
E.EMP_ID,
E.EMP_NAME,
E.MANAGER_ID,
M.EMP_NAME
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER_ID = M.EMP_ID(+) ;
-- ANSI 구문
SELECT
E.EMP_ID,
E.EMP_NAME,
E.MANAGER_ID,
M.EMP_NAME
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE M ON (M.EMP_ID = E.MANAGER_ID);다중 JOIN
- 3개 이상의 테이블을 조인 할때 부르는 명칭
-- 사번, 사원명, 부서명, 직급명
--오라클 전용 구문
SELECT EMP_ID,
EMP_NAME,
DEPT_TITLE,
JOB_NAME
FROM EMPLOYEE, DEPARTMENT, JOB
WHERE DEPT_CODE = DEPT_ID
AND EMPLOYEE.JOB_CODE = JOB.JOB_CODE;
-- ANSI 구문
SELECT EMP_ID,
EMP_NAME,
DEPT_TITLE,
JOB_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_ID =DEPT_CODE
JOIN JOB USING(JOB_CODE); --2개이상일때 USING 사용이 제한된다
