[DB] JOIN

목차

1. INNER JOIN (내부조인)

2. OUTER JOIN (외부조인)

1) LEFT [OUTER] JOIN

2) RIGHT [OUTER] JOIN

3) FULL [OUTER] JOIN

3. CROSS JOIN (교차 조인)

4. NON EQUAL JOIN (비등가 조인)

5. SELF JOIN (자체 조인)

6. NATURAL JOIN (자연 조인)

7. 다중 조인

[ JOIN 용어 정리 ]

오라클	SQL : 1999표준(ANSI)
등가 조인 (= 을 사용하기 때문에)	내부 조인(INNER JOIN), JOIN USING / ON + 자연 조인(NATURAL JOIN, 등가 조인 방법 중 하나)
포괄 조인	왼쪽 외부 조인(LEFT OUTER), 오른쪽 외부 조인(RIGHT OUTER)     + 전체 외부 조인(FULL OUTER, 오라클 구문으로는 사용 못함)
자체 조인, 비등가 조인	JOIN ON
카테시안(카티션) 곱 (CARTESIAN PRODUCT)	교차 조인(CROSS JOIN)

➡ 미국 국립 표준 협회(American National Standards Institute, ANSI) 미국의 산업 표준을 제정하는 민간단체.
국제표준화기구 ISO에 가입되어 있음.

---

✍JOIN

: 하나 이상의 테이블에서 데이터를 조회하기 위해 사용
수행 결과는 하나의 Result Set으로 나온다. 

주의❗

JOIN할 수록 커지니깐 SELECT할 때 시간이 많이 걸리니 무작정하는게 좋지는 않다

 

관계형 데이터베이스에서 SQL을 이용해 테이블간 '관계'를 맺는 방법
:  관계형 데이터베이스는 최소한의 데이터를 테이블에 담고 있어
  원하는 정보를 테이블에서 조회하려면 한 개 이상의 테이블에서 
  데이터를 읽어와야 되는 경우가 많다.
  이 때, 테이블간 관계를 맺기 위한 연결고리 역할이 필요한데,
 두 테이블에서 같은 데이터를 저장하는 컬럼이 연결고리가 된다. 

 

예제1. 직원번호, 직원명, 부서코드, 부서명
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON( DEPT_CODE = DEPT_ID );

-- DEPT_CODE 컬럼과 DEPT_ID 컬럼이 같은 값을 저장하는 컬럼(연결고리)

---

🚩  INNER JOIN  (내부 조인)

 == 등가 조인(EQUAL JOIN))

연결되는 컬럼의 값이 일치하는 행들만 조인.  
(== 일치하는 값이 없는 행은 조인에서 제외된다. )

 

참고) 작성 방법 크게  오라클 구문과 ANSI구문으로 나뉘고 ANSI에서  USING과 ON을 쓰는 방법으로 나뉜다.

오라클 전용 구문	ANSI 표준 구문
FROM절에 쉼표(,) 로 구분하여 합치게 될 테이블명을 기술하고 WHERE절에 합치기에 사용할 컬럼명을 명시한다	ANSI는 미국 국립 표준 협회를 뜻함, 미국의 산업표준을 제정하는 민간단체로  국제표준화기구 ISO에 가입되어있다. ANSI에서 제정된 표준을 ANSI라고 하고  여기서 제정한 표준 중 가장 유명한 것이 ASCII코드이다.

---

1.  연결에 사용할 두 컬럼명이 다른 경우

예제1. EMPLOYEE 테이블, DEPARTMENT 테이블을 참조하여 사번, 이름, 부서코드, 부서명 조회

[ANSI]
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE
FROM EMPLOYEE
/*INNER*/ JOIN DEPARTMENT ON( DEPT_CODE = DEPT_ID );

👉 ON
: 연결에 사용할 컬럼명이 다른경우 ON()을 사용

➡ JOIN하는 DEPARTMENT의 DEPT_ID컬럼에 연결되는 값이 없어서 DEPT_CODE가 NULL인 값은 제외된다.
➡  ANSI에서 JOIN의 기본은 INNER JOIN이기때문에 INNER를 생략해도 괜찮다.

---

[오라클]
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT  
WHERE DEPT_CODE = DEPT_ID;

➡ 오라클 방식은 사용할 테이블을 모두 FROM절에 작성
연결고리가 되는 두 컬럼의 값이 같은 경우에 연결한다. 

➡ EMPLOYEE 테이블에 DEPT_CODE컬럼과 DEPARTMENT 테이블에 DEPT_ID 컬럼은 서로 같은 부서 코드를 나타낸다.
이를 통해 두 테이블이 관계가 있음을 알고 조인을 통해 데이터 추출이 가능하다.

 

 

2. 연결에 사용할 두 컬럼명이 같은 경우

예제 1. EMPLOYEE 테이블, JOB테이블을 참조하여 사번, 이름, 직급코드, 직급명 조회

[ANSI]
SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE);

➡ 연결에 사용할 컬럼명이 같은 경우 USING(컬럼명)을 사용

---

[오라클 ]
SELECT EMP_ID, EMP_NAME, E.JOB_CODE, JOB_NAME
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE;

➡ 별칭 사용
테이블 별로 별칭을 등록할 수 있다.

---

🚩 OUTER JOIN  (외부 조인)

 == 등가 조인(EQUAL JOIN))

두 테이블의 지정하는 컬럼값이 일치하지 않는 행도 조인에 포함을 시킨다.
반드시 OUTER JOIN임을 명시

제외된 NULL도 포함시키는 JOIN

---

1.  LEFT [OUTER] JOIN 

합치기에 사용한 두 테이블 중
왼편에 기술된 테이블의 컬럼 수를 기준으로 JOIN

예제1. EMPLOYEE 테이블, DEPARTMENT 테이블을 참조하여 사번, 이름, 부서코드, 부서명 조회

[ANSI]
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

👉 LEFT JOIN
: EMPLOYEE 테이블에 있는 모든 행이 조인 결과에서 제외되지 않는 조인

➡ DEPT_CODE가 NULL인 값은 DEPARTMANT 테이블과 연결되는 컬럼이 없으므로 
     DEPARTMANT 관련 컬럼 조회 시 모두 NULL값을 가지게 된다. 

---

[오라클]
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT  
WHERE DEPT_CODE = DEPT_ID;

➡ 오라클 방식은 사용할 테이블을 모두 FROM절에 작성
연결고리가 되는 두 컬럼의 값이 같은 경우에 연결한다. 

➡ EMPLOYEE 테이블에 DEPT_CODE컬럼과 DEPARTMENT 테이블에 DEPT_ID 컬럼은 서로 같은 부서 코드를 나타낸다.
이를 통해 두 테이블이 관계가 있음을 알고 조인을 통해 데이터 추출이 가능하다.

 

2) RIGHT [OUTER] JOIN 

합치기에 사용한 두 테이블 중 
오른편에 기술된 테이블의  컬럼 수를 기준으로 JOIN

예제1. EMPLOYEE 테이블, DEPARTMENT 테이블을 참조하여 사번, 이름, 부서코드, 부서명 조회

[ANSI]
SELECT EMP_NAME, DEPT_CODE, DEPT_ID, DEPT_TITLE
FROM EMPLOYEE
RIGHT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

➡ DEPT_CODE가 NULL인 값은 DEPARTMANT 테이블과 연결되는 컬럼이 없으므로 
     DEPARTMANT 관련 컬럼 조회 시 모두 NULL값을 가지게 된다. 

---

[오라클]
SELECT EMP_NAME, DEPT_CODE, DEPT_ID, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE(+)= DEPT_ID;

➡ 오라클 방식은 사용할 테이블을 모두 FROM절에 작성
연결고리가 되는 두 컬럼의 값이 같은 경우에 연결한다. 

➡ LEFT JOIN : EMPLOYEE를 기준으로 NULL값도 포함된다 하지만 D3, D4 D7가 포함되지 않는다
➡ RIGHT JOIN : D3, D4 D7가 결과에 포함되지만 NULL값이 나오지 않는다. 나오는 결과가 반대다.

 

3) FULL [OUTER] JOIN

합치기에 사용한 두 테이블이 가진 모든 행을 결과에 포함
오라클 구문은 FULL OUTER JOIN을 사용 못함❗

예제1. EMPLOYEE 테이블, DEPARTMENT 테이블을 참조하여 사번, 이름, 부서코드, 부서명 조회

[ANSI]
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
FULL JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

➡ INNER JOIN : 21행
LEFT JOIN : 23행( NULL값을 가진 값 2개 행 포함)
RIGHT JOIN : 24행 (D3,D4,D7 포함)
FULL JOIN : 26행(LEFT JOIN) + (RIGHT JOIN))  // 21 + 2(LEFT JOIN) + 3(RIGHT JOIN))

---

[오라클] (FULL OUTER JOIN 불가)
SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT  
WHERE DEPT_CODE = DEPT_ID;

➡ INNER JOIN : WHERE
LEFT JOIN : 오른쪽에 (+)
RIGHT JOIN : 왼쪽에 (+)
FULL JOIN : ORACLE에서는 불가

---

🚩 CROSS JOIN  (교차 조인)

 == CARTESIAN PRODUCT

조인되는 테이블의 각 행들이 모두 매핑된 데이터가 검색되는 방법(곱집합)

두 테이블이 연결될 수 있는 모든 값을 보여주는 방법 (복잡하다)

예제

SELECT EMP_NAME,DEPT_TITLE
FROM EMPLOYEE
CROSS JOIN DEPARTMENT;

결과 : 두 테이블이 모두 곱해진 만큼의 행이 나온다(23 *9)

➡ 실제로 사용되는 경우는 적지만 조인이 잘못 되었을 경우

조회 결과가 CROSS JOIN의 형태로 조회된다.

 

➡ 조인을 잘못했을 때 나오는 형태 : JOIN 했는데 CROSS JOIN 결과가 나온다.

---

🚩 NON EQUAL JOIN  (비등가 조인)

'='(등호)를 사용하지 않는 조인문

지정한 컬럼 값이 일치하는 경우가 아닌, 값의 범위에 포함되는 행들을 연결하는 방식

SELECT EMP_NAME, SALARY, SAL_LEVEL, MIN_SAL, MAX_SAL
FROM EMPLOYEE
JOIN SAL_GRADE USING (SAL_LEVEL);

👉 USING
: 같을 때는 ON이 아니라 USING을 쓴다.

---

🚩 SELF JOIN (자체 조인)

같은 테이블을 조인.
자기 자신과 조인을 맺는다.

✔ TIP
같은 테이블이 왼쪽, 오른쪽에 각각 있다고 생각하고 조인하면 쉽다.

예제1. 직속상사가 NULL인 사람도 조회
LEFT JOIN + 별칭 사용, NULL은 없음이라고 바꾸기

SELECT E1.EMP_ID, E1.EMP_NAME, NVL(E2.EMP_NAME, '없음') 직속상사
FROM EMPLOYEE E1
LEFT JOIN EMPLOYEE E2 ON (E1.MANAGER_ID = E2.EMP_ID);

👉 NVL
: 컬럼 값이 NULL일 경우 다른 값으로 변경

➡ MANAGER_ID를 찾기 위해 E1의 MANAGER_ID와 E2의 EMP_ID를 조인해야한다

➡ 해석

같은 테이블 두 개를 두고 조인할때

E1, E2로 별칭 붙여주고 E1.MANAGER_ID로 직속상사 사수를 찾고 E2에서 그 상사의 사번을 찾는다

그리고 해당사원의 사번, 이름과 그 사원의 직속상사 이름을 조회

값이 NULL인 사람은 제외하고 조회된다.

---

🚩 NATURAL JOIN  (자연 조인) 

 

동일한 타입과 이름을 가진 컬럼이 있는 테이블 간의 조인을 간단히 표현하는 방법
반드시 두 테이블 간의 동일한 컬럼명, 타입을 가진 컬럼이 필요
--> 없을 경우 교차조인이 된다.

예제1.  EMPLOYEE, JOB 테이블을 참조하여 사번, 이름, 직급코드, 직급명을 조회

SELECT EMP_NO 사번, EMP_NAME 이름, JOB_CODE 직급코드, JOB_NAME 직급명
FROM EMPLOYEE
NATURAL JOIN JOB;

➡ JOIN JOB USING (JOB_CODE); (기존 방식)
➡ EMPLOYEE, JOB 두 테이블에서 같은 이름의 컬럼(JOB_CODE)을 기준으로 JOIN이 수행된다.

---

예제2. NATURAL JOIN 실패

SELECT EMP_NAME , DEPT_TITLE
FROM EMPLOYEE
NATURAL JOIN DEPARTMENT;

➡ 두 개의 테이블에는 같은 이름의 컬럼이 없기 때문에 
NATURAL JOIN 실패 -> CROSS JOIN 결과가 조회된다.

➡ 실패원인: 두 테이블에 같은 이름의 컬럼이 없어서 NATURAL JOIN 실패
--> 해결 방법: INNER JOIN방법으로 하기

---

🚩 다중 조인

N개의 테이블을 조회할 때 사용  (순서 중요!)

예제1.  사번, 이름, 직급명, 부서명, 지역명 조회
사번, 이름 : EMPLOYEE
직급명     : JOB
부서명     : DEPARTMENT
지역명     : LOCATION

ANSI 표준
SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE, LOCAL_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE) --JOB 테이블 JOIN
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID) 
JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE); -- LOCATION 테이블 JOIN

➡ DEPARTMENT 테이블 JOIN 임플로이 테이블이 가진 DEPT_CODE를 가져온다 

---

예제2. NATURAL JOIN 실패
주의 사항!! 다중 조인은 반드시 순서대로 작성되어야한다. (오류가 생기는 코드)
조인 순서를 지키지 않은 경우(에러발생)

SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE, LOCAL_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE) -- "LOCATION_ID": invalid identifier 에러 발생
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID); 

➡ "LOCATION_ID": invalid identifier
-- LOCATION JOIN 시 LOCATION_ID 컬럼을 연결고리 역할로 사용하려고 했으나
-- EMPLOYEE, JOB 두 테이블이 조인된 결과에 LOCATION_ID 컬럼이 없어서 오류가 발생한다. 

---

오라클 전용 : AND 연산을 이용해서 JOIN을 여러 번 진행할 수있다.

SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE, LOCAL_NAME
FROM EMPLOYEE E, JOB J, DEPARTMENT, LOCATION -- EMPLOYEE와 JOB의 JOB_CODE가 겹치기 때문에 구분해준다.
WHERE E.JOB_CODE = J.JOB_CODE
AND DEPT_CODE = DEPT_ID
AND LOCATION_ID = LOCAL_CODE;

➡ 기존의 JOIN 방법을 사용하면

EMPLOYEE + JOB + DEPARTMENT +LOCATION

이런식으로 순차적으로 JOIN한다