JOIN
- 여러 테이블을 묶어서 하나의 결과물을 반환받을 때, 서로다른 테이블을 묶는 행위를 '조인' 이라고 함
- 조인 구문: SELECT문에서 값 조회시 두 개 이상의 테이블에서 조인 조건을 만족하는 행을 반환하는 구문
- 필요한 데이터가 두 개 이상의 테이블에 나눠져 있을 때 사용하며, 조인을 통하여 데이터를 합친 후 반환받음
(2개 이상 테이블의데이터를 같이 조회할 때 사용되는 구문)
서로 다른 테이블을 어떻게 연결할 수 있을까?
- 서로 다른 두 개 , 또는 여러개 테이블을 연결하기 위해서는 테이블간의 '연결고리'역할을 하는 컬럼이 존재해야함
(서로 다른 두 테이블에서 서로 중복되는 값으로만 이루어진 컬럼)
- 테이블간 연결고리에 해당하는 컬럼을 매칭시켜 하나의 테이블로 연결
(A 테이블의 a컬럼 = B테이블의 b 컬럼) 형식으로 매칭
- 연결고리 컬럼은 두 컬럼을 이어주는 역할을 하기 때문에 동일한 값의 집합을 담고있는, 공통된 데이터를 갖는 컬럼이 와야함
- 조회결과는 하나의 결과물(RESULT SET)으로 나옴
| 오라클 전용 구문 | ANSI 구문 (범용성이 더 좋음) |
| 등가조인 (EQUI JOIN) | 내부조인(INNER JOIN) |
|
|
| 포괄조인 LEFT OUTER RIGHT OUTER |
외부조인 (OUTER JOIN) LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN (오라클엔 없음) |
|
|
| 카테시안곱(CARTESIAN PRODUCT) | 교차조인(CROSSJOIN) |
| SELF JOIN | |
1. 등가조인 (EQUI JOIN) (oracle)
다중의 테이블을 연결한 후,
해당 테이블들에서 컬럼들의 값이 일치하는 행(값이 중복되는 행)들만 조인하여 조회
일치하지 않는 값은 조회에서 제외
가장 많이 사용하는 조인문
조인 조건이 정확히 일치하는 경우에 사용 (PK(기본키) 와 FK(외래키) 를 사용한 조인)
조인 조건에 ' = ' 를 이용하는 조인을 보통 등가 조인(Equi-join)이라 칭함
매칭이 안되는 부분은 누락시키고, 매칭이 되는 행들만 join
select 컬럼1, 컬럼2, 컬럼3.... ->조회가능 컬럼은 테이블 1, 테이블2의 모든 컬럼
from 테이블1, 테이블2
where (테이블1의)컬럼명=(테이블2의)컬럼명;
-> 두 컬럼을 이어줄 수 있는, 동일한 값의 집합을 담고있는 컬럼명
반드시 공통된 데이터를 갖는 컬럼이 와야함--사번, 사원명, 직급코드, 직급명
--두 테이블에서 연결할 두 컬럼 명이 같음 (둘다 JOB CODE임)
/*
SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
FROM EMPLOYEE, JOB
WHERE JOB_CODE = JOB_CODE; -- "column ambiguously defined"
*/
-- 방법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 ~ FROM ~ WHERE ~ AND 조건>
--EMP_ID, EMP_NAME, SALARY, JOB_NAME
SELECT
EMP_ID,
EMP_NAME,
SALARY,
JOB_NAME
FROM
EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE
AND JOB_NAME = '대리';2. 내부조인 (ANSI)
- FROM절에 기존 테이블 기술
- FROM절 뒤 JOIN 절에 같이 조회하고자 하는 테이블 기술 + 매칭시킬 컬럼에 대한 조건 기술
- 연결할 컬럼의 이름이 같은 경우:USING 을 사용하여 테이블 연결
- 연결할 컬럼의 이름이 다른 경우: ON을 사용하여 테이블 연결
<연결할 컬럼명이 다른 경우> : join - on ( a = b)
select 컬럼명1, 컬럼명 2... -> join으로 연결된 모든 테이블의 컬럼 조회가능
from 테이블1 -> 주 table
(inner)join 테이블2 on ( 테이블1.컬럼명 = 테이블2.컬럼명 )
(inner)join 테이블3 on ( 테이블1.컬럼명 = 테이블3.컬럼명 )
(inner)join 테이블4 on ( 테이블1.컬럼명 = 테이블3.컬럼명 )
where ~ ; 조건문
< 연결할 컬럼명이 일치하는 경우> : 1 > join - using ( a )
select 컬럼명1, 컬럼명 2... -> join으로 연결된 모든 테이블의 컬럼 조회가능
from 테이블1 -> 주 table
(inner)join 테이블2 using ( 테이블1.컬럼명 = 테이블2.컬럼명 )
where ~ ; 조건문
< 연결할 컬럼명이 일치하는 경우> : 1 > join - using ( a )
select 컬럼명1, 컬럼명 2... -> join으로 연결된 모든 테이블의 컬럼 조회가능
from 테이블1 별칭1 -> 주 table
(inner)join 테이블2 별칭2 using ( 별칭1.컬럼명 = 별칭2.컬럼명 )
where ~ ; 조건문<ON 구문>
SELECT EMP_ID, EMP_NAME, E.JOB_CODE, JOB_NAME
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE);
<USING 구문>
SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
FROM EMPLOYEE E
JOIN JOB
USING (JOB_CODE) ;
->USING구문은 컬럼명이 동일할 경우 알아서 매칭을 해줌, 별칭으로 구분필요 없음
<추가조건: SELECT ~ FROM ~ JOIN ~ USING/ON ~ WHERE)
SELECT EMP_ID,
EMP_NAME,
SALARY,
JOB_NAME
FROM EMPLOYEE
JOIN JOB
USING (JOB_CODE)
WHERE JOB_NAME = '대리';
3. 포괄조인 (오라클) / OLD OUTER JOIN
테이블간 JOIN 시 일치하지 않은 행도 포함시켜 조회 가능
단, 반드시 LEFT 또는 RIGHT를 지정해야함
(기준이 되는 테이블 지정)
왼편에 기술된 테이블의 데이터는 무조건 조회(NULL 포함), 오른쪽과 왼쪽이 일치하는 값이 없어도 조회
내가 기준으로 할 테이블 컬럼의 반대편에 (+)추가
SELECT 컬럼1, 컬럼2, 컬럼3...
FROM 주 테이블1, 연결할테이블2, 연결할테이블3 ....
WHERE (테이블1의)컬럼명=(테이블2의)컬럼명(+);
AND (테이블1의)컬럼명(+)=(테이블3의)컬럼명;
AND 조건식!
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE (DEPT_CODE = DEPT_ID(+));
--내가 기준으로 할 테이블 컬럼의 반대편에 (+)추가
--ORACLE
--내가 기준으로 할 테이블 컬럼의 반대편 컬럼 이름뒤 (+)추가
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE (DEPT_CODE(+) = DEPT_ID);
4. 외부조인 (OUTER JOIN) - ANSI
테이블간 JOIN 시 일치하지 않은 행도 포함시켜 조회 가능
단, 반드시 LEFT 또는 RIGHT를 지정해야함
(기준이 되는 테이블 지정)
- LEFT OUTER JOIN (LEFT JOIN) : 왼쪽 테이블을 기준으로 조인
왼편에 기술된 테이블의 데이터는 무조건 조회(NULL 포함), 오른쪽과 왼쪽이 일치하는 값이 없어도 조회
- RIGHT OUTER JOIN (RIGHT JOIN) : 오른쪽 테이블을 기준으로 조인
- FULL OUTER JOIN (FULL JOIN) : 모든 NULL값까지 출력
1. LEFT (OUTER) JOIN
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);
--> JOIN앞에 LEFT를 붙이면 DEPARTMENT의 NULL값까지 다 나옴
-- EMPLOYEE테이블을 기준으로 했기 때문에 EMPLOYEE에 존재하는 모든 데이터가 조회됨 ( 연결되는 내용이 없어도)
2) RIGHT (OUTER) JOIN
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE
RIGHT JOIN DEPARTMENT
ON (DEPT_CODE = DEPT_ID);
3)FULL OUTER JOIN : 오른쪽 왼쪽 다 나오게, 두 테이블이 가진 모든 행을 조회
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE
FULL OUTER JOIN DEPARTMENT
ON (DEPT_CODE = DEPT_ID);
5. 카테시안 곱 / 교차조인 (CARTESIAN PRODUCT , CROSS JOIN)
모든 테이블의 각 행들이 서로서로 매핑된 데이터 조회(곱집합)
두 테이블의 행들이 모두 곱해진 조합 출력
--> 방대한 데이터 출력, 과부하 위험
--사원, 부서명 카테시안 곱
--ORACLES
--
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT;
--ANSI
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
CROSS JOIN DEPARTMENT;6. 비등가 조인 (NON EQUAI JOIN)
- '='를 사용하지 않는 조인문
- 지정해주는 컬럼값이 일치하는 경우가 아닌 "범위"에 포함되는 경우 매칭
--사원명, 급여
SELECT EMP_NAME, SALARY
FROM EMPLOYEE;
SELECT*
FROM SAL_GRADE;
--사원명, 급여, 급여등급 (SAL_LEVEL, SAL_GRADE테이블)
SELECT EMP_NAME, SALARY, E.SAL_LEVEL
FROM EMPLOYEE E, SAL_GRADE S
--WHERE SALARY < MAX_SAL AND MIN_SAL <= SALARY;
WHERE SALARY BETWEEN MIN_SAL AND MAX_SAL;
SELECT EMP_NAME, SALARY, E.SAL_LEVEL
FROM EMPLOYEE E
JOIN SAL_GRADE S
ON (SALARY BETWEEN MIN_SAL AND MAX_SAL);
8. 자체조인(SELF-JOIN)
자기 자신의 테이블과 조인,
자기 자신을 한번 더 복사(조인) 해서 RESULT SET으로 반환받음
------ <오라클>
SELECT
EMP_ID "사원 사번",
EMP_NAME 사원명,
SALARY "사원 급여",
MANAGER_ID "사수사번" -- 내 사수의 EMP_ID
FROM EMPLOYEE;
SELECT *
FROM EMPLOYEE; --- 사원 정보 도출용 테이블
---MANAGER ID - EMP ID , 나의 정보 =
--1) 사원 사번, 사원명, 사원 부서코드, 사원 급여 조회
SELECT
EMP_ID "사원 사번",
EMP_NAME 사원명,
SALARY "사원 급여",
MANAGER_ID "사수사번" -- 내 사수의 EMP_ID
FROM EMPLOYEE;
--2) 사수사번, 사수명, 사수부서코드, 사수급여
SELECT
E.EMP_ID "사원 사번",
E.EMP_NAME 사원명,
E.DEPT_CODE "부서명",
E.SALARY 급여,
M.EMP_ID "사수 사번",
M.EMP_NAME 사수명,
M.DEPT_CODE 사수부서,
M.SALARY 급여
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER_ID = M.EMP_ID(+);
--1)+2)
---<ANSI>
SELECT
E.EMP_ID "사원 사번",
E.EMP_NAME 사원명,
E.DEPT_CODE "부서명",
E.SALARY 급여,
M.EMP_ID "사수 사번",
M.EMP_NAME 사수명,
M.DEPT_CODE 사수부서,
M.SALARY 급여
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE M
ON (E.MANAGER_ID = M.EMP_ID);
---사원 사번, 사원명, 사원 부서코드, 사원 부서명(DEPARTMENT -DEPT_ID = EMPLOYEE -DEPT_CODE ) DEPT_CODE, 사원급여
--사수 사번, 사수명, 사수 부서코드, 사수 부서명, 사수급여
SELECT
E.EMP_ID ,
E.EMP_NAME,
E.DEPT_CODE,
D1. DEPT_TITLE,
E.SALARY,
M.EMP_ID,
M.EMP_NAME,
M.DEPT_CODE,
D2.DEPT_TITLE,
M.SALARY
FROM
EMPLOYEE E
LEFT
JOIN EMPLOYEE M ON (E.MANAGER_ID = M.EMP_ID)
LEFT
JOIN DEPARTMENT D1 ON E.DEPT_CODE = D1.DEPT_ID
JOIN DEPARTMENT D2 ON (M.DEPT_CODE = D2.DEPT_ID);
9. 다중조인
다중조인은 조인하는 순서가 엄~~청 중요!
3개 이상의 테이블을 LEFT JOIN을 할 경우
<유의사항>
- 어떤 순서로 테이블을 조인하는지에 따라 결과 테이블에 조회되는 테이블 구성이 달라짐
- JOIN 문을 작성시 LEFT JOIN을 하려면 가장 첫 번째의 테이블로 SELECT문에 가장 많은 열을 가져와야 할 테이블을 우선작성
-시작을 LEFT JOIN으로 했다면 보통의 경우 나머지 조인도 LEFT JOIN
사번(EMP), 사원명(EMP), 부서명(DEPARTMENT-DEPT-TITLE)직급명 (JOB - JOB_NAME)
-- EMP-DEPT연결(DEPT_ID-DEPT_CODE) , EMP-JOB연결 (JOB_CODE - JOB_CODE), DEPT - JOB연결불가
-- 따라서, 연관관계를 만들어줄 수 있는 기준 테이블이 필요
--ANSI
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID) -- 먼저 두 개를 붙인 테이블을 만듣면 해당 테이블과 JOB은 연결고리가 생겼음!
JOIN JOB USING (JOB_CODE);
-- ORACLE
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE E, DEPARTMENT, JOB J
WHERE DEPT_CODE = DEPT_ID(+)
AND E.JOB_CODE = J.JOB_CODE;/*
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID) -- 먼저 두 개를 붙인 테이블을 만듣면 해당 테이블과 JOB은 연결고리가 생겼음!
JOIN JOB USING (JOB_CODE);
*/
--사번, 사원명, 부서명, 직급명, 지역명(LOCATION-LOCAL_NAME)
--직급명 (JOB - JOB_TITLE) : 연결고리 DEPARTMEN- DEPT_ID JOB-JOB_CODE
-- 지역명(LOCATION-LOCAL_NAME): 연결고리 DEPARTMENT-LOCATION_ID, LOCATION-LOCAL_CODE
--1. ORACLE
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
SELECT * FROM EMPOYEE;
SELECT * FROM DEPARTMENT;
SELECT * FROM JOB;
SELECT *FROM LOCATION;
SELECT EMP_NAME, DEPT_TITLE, JOB_NAME, LOCAL_NAME
FROM EMPLOYEE E, DEPARTMENT, JOB J, LOCATION
WHERE DEPT_CODE = DEPT_ID(+) ---부서없는 두 명이 빠졌다
AND E.JOB_CODE = J.JOB_CODE
AND LOCATION_ID = LOCAL_CODE(+); -- 부서 없는 두 명은 LOCATION_ID가 없어서 빠졌음(부서가 없으면 지역도 없음)
--2.ANSI
SELECT EMP_NAME, DEPT_TITLE, JOB_NAME, LOCAL_NAME
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE); -- 다중쪼인 시에는 테이블 합치는 순서에 유의해야함
-- 다중 JOIN시 LOCATION 테이블이 DEPARTMENT테이블보다 먼저 조인되면 오류 발생
-- 사번, 사원명(EMP), 부서명(EMP / DEPT), 직급명(JOB), 근무지역명(DEPT, LOCATION), 근무국가명(LOCATION-NATIONAL_CODE), 급여등급(EMPLOYEE-SAL)
SELECT * FROM EMPLOYEE; -- DEPT_CODE JOB_CODE
SELECT * FROM DEPARTMENT; -- DEPT_ID LOCATION_ID
SELECT * FROM LOCATION; -- LOCAL_CODE NATIONAL_CODE
SELECT * FROM JOB; ----- JOB_CODE
SELECT * FROM NATIONAL; NATIONLA_CODE
SELECT * FROM SAL_GRADE; -- MIN_SAL, MAX_SAL
SELECT EMP_ID 사번,
D.DEPT_TITLE 부서명,
J.JOB_NAME "직급명",
L.LOCAL_NAME 근무지역명,
N.NATIONAL_NAME 근무국가명,
S.SAL_LEVEL 급여등급
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 (E.JOB_CODE = J.JOB_CODE)
JOIN NATIONAL N ON ( L.NATIONAL_CODE = N.NATIONAL_CODE)
JOIN SAL_GRADE S ON (E.SALARY BETWEEN MIN_SAL AND MAX_SAL);'Database' 카테고리의 다른 글
| [SQL] 서브쿼리 (SUBQUERY) (0) | 2022.10.04 |
|---|---|
| [SQL] 집합연산자 (SET OPERATOR) (0) | 2022.10.04 |
| [SQL] 함수 - 그룹함수 (0) | 2022.10.04 |
| [SQL] 함수 - 단일행함수 (문자열함수/ 숫자관련/형변환함수/ 선택함수) (1) | 2022.10.04 |
| [SQL] DML - SELECT문 (0) | 2022.10.04 |
