[SQL] 데이터 조인(NATURAL JOIN, USING절, ON절, INNER JOIN, OUTER JOIN, CROSS JOIN)

-- ORDER BY 1: 테이블의 첫번째 컬럼 기준으로 정렬하겠다는 뜻
-- 웬만하면 order by절에 컬럼명을 써주는 것이 좋음
SELECT *
FROM emp, dept 
ORDER BY 1 ; 

-- 오류 발생 (emp에도 dept에도 deptno가 있기 때문)
SELECT * 
FROM emp, dept 
WHERE deptno = deptno ; 

SELECT * 
FROM emp, dept 
WHERE emp.deptno = dept.deptno ; 

-- 테이블명에 alias(별칭) 가능
SELECT * 
FROM emp e, dept d
WHERE e.deptno = d.deptno ; 

조인

• NATURAL JOIN
• USING절
• ON절
• INNER JOIN
• OUTER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN
• CROSS JOIN

Natural Join

• 두 테이블에서 이름이 같은 모든 열 기반
• 두 테이블에서 대응되는 모든 열의 값이 동일한 행 선택
• 동일한 이름을 가진 열이 서로 다른 데이터 유형을 가지면 오류가 반환됨
• column 이름이 동일하면서 데이터 타입도 동일한 경우만 출력

SELECT employee_id, first_name, job_id, job_title
FROM employees NATURAL JOIN jobs;

USING절

• 여러 열의 이름이 동일하지만 데이터 유형은 다를 경우 사용
• 두 개 이상의 열이 일치하는 경우, 하나의 열만 일치하도록 할 수 있음

SELECT employee_id, last_name, 
       location_id, department_id
FROM   employees JOIN departments
USING (department_id) ;

• 무엇으로 join 할 것인지 USING절에서 명시

SELECT employee_id, last_name, e.manager_id,
       location_id, department_id
FROM   employees e JOIN departments d
USING (department_id) ;

• 여기서 manager_id는 양쪽 테이블 모두에 있기 때문에 e.manager_id 이런 식으로 테이블을 지정해줘야 됨
• 하지만 여기서 d.department_id 이렇게 쓰면 에러 발생
  • Using절에서 이미 특정 column을 지정하여 join했기 때문에
    select절에서 호출 시 따로 호출하는 것으로 인식하기 때문에 에러 발생

ON절

• 임의 조건을 지정하거나 조인할 열을 지정

SELECT e.employee_id, e.last_name, e.department_id, 
       d.department_id, d.location_id
FROM   employees e JOIN departments d
ON    (e.department_id = d.department_id) ;

ON (e.department_id > d.department_id);
-> 이것도 가능
-> natural join과 using절은 무조건 같은 값끼리만 가능한데 on절은 >, < 이런 것도 가능

3-Way 조인 생성

-- ANSI JOIN 방식
SELECT employee_id, city, department_name
FROM   employees e 
JOIN   departments d
ON     d.department_id = e.department_id 
JOIN   locations l
ON     d.location_id = l.location_id;


-- Oracle JOIN 방식
SELECT employee_id, city, department_name
FROM   employees e 
       ,departments d
       ,locations 1
WHERE d.department_id = e.department_id 
  AND d.location_id = l.location_id;

• ANSI JOIN 방식을 더 권장

조인에 추가 조건 적용

• AND나 WHERE 사용

SELECT e.employee_id, e.last_name, e.department_id, 
       d.department_id, d.location_id
FROM   employees e JOIN departments d
ON    (e.department_id = d.department_id)
AND    e.manager_id = 149 ;

SELECT e.employee_id, e.last_name, e.department_id, 
       d.department_id, d.location_id
FROM   employees e JOIN departments d
ON    (e.department_id = d.department_id)
WHERE  e.manager_id = 149 ;

INNER JOIN과 OUTER JOIN

• INNER JOIN: 교집합. 일치하는 행만 반환하는 두 테이블의 조인
• LEFT OUTER JOIN: 왼쪽 테이블 기준으로 JOIN 하겠다는 것
  • 왼쪽 테이블 A의 모든 데이터와 A와 B 테이블의 중복데이터들이 검색됨
• RIGHT OUTER JOIN: 오른쪽 테이블 기준으로 JOIN 하겠다는 것
  • 오른쪽 테이블 B의 모든 데이터와 A와 B 테이블의 중복데이터들이 검색됨
• FULL OUTER JOIN: 왼쪽 테이블과 오른쪽 테이블의 합집합. 만약 A에는 데이터가 있지만 B에 데이터가 없으면, B부분은 null이 되고 반대의 경우에는 A부분이 null이 됨
  • 두 테이블 간의 모든 행을 포함하며, 일치하지 않는 경우에는 NULL로 표시

💡 OUTER JOIN의 경우, 테이블 전체를 검색해서 JOIN하기 때문에 성능 저하를 야기함. 꼭 필요할 때만 사용하기!

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e LEFT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

SELECT e.last_name, d.department_id, d.department_name
FROM   employees e RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

SELECT e.last_name, d.department_id, d.department_name
FROM   employees e FULL OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

Cartesian Product(카티시안 곱)

• 발생가능한 모든 경우의 수를 다 검색
• 다수의 행을 생성하므로 결과는 그다지 유용하지 X

N개의 행을 가진 테이블과 M개의 행을 가진 테이블의 카티시안 곱은 N*M

CROSS JOIN

• 두 테이블의 모든 조합을 생성
• 두 테이블의 Cartesian Product를 생성하는 조인 작업
• 각 테이블의 모든 행을 곱하여 결과를 생성
• 첫 번째 테이블의 행 수 * 두 번째 테이블의 행 수
• ON절 사용하지 X

SELECT last_name, department_name
FROM   employees
CROSS JOIN departments ;

✔️ CROSS JOIN과 OUTER JOIN의 차이

OUTER JOIN은 JOIN한 뒤 일부 행에서 특정컬럼 값이 NULL값인게 존재한다
OUTER JOIN: NULL값 O
CROSS JOIN: NULL값 X