[ORACLE] JOIN

hyem·2021년 7월 8일

JOIN oracle

ORACLE

목록 보기

3/3

JOIN

두 테이블을 특정 컬럼을 기준으로 연결시켜 데이터 표현

1. ORACLE Syntax

1) Equi Join

두 테이블에 공통으로 존재하는 컬럼이 일치되는 행들을 연결해서 결과 표시

<기본 형식>

SELECT table1.column1, ..., table2.column2, ...
FROM table1, table2
WHERE table1.column1 = table2.column2
[ AND 다른 조건 ... ]

예) job_id를 기준으로 두 테이블의 데이터를 연결시켜 출력

SELECT *
FROM employees, jobs
WHERE employees.job_id = jobs.job_id;

3개 이상 테이블 조인 : 컬럼 연결 조건들을 AND로 이어서 써줌

SELECT e.employee_id, e.last_name, e.salary, e.department_id,
       d.department_id, d.department_name,
       l.*	   
FROM employees   e 
    ,departments d  
    ,locations   l 
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;

2) Nonequi Join

특정 컬럼을 범위로 조인

예) emp 테이블의 sal 컬럼을 salgrade 테이블의 losal과 hisal 사이 범위로 조인시켜서 등급을 표시해보자

SELECT e.empno, e.ename, e.job, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

 EMPNO ENAME      JOB              SAL      GRADE
------ ---------- --------- ---------- ----------
  7369 SMITH      CLERK            800          1
  7900 JAMES      CLERK            950          1
  7876 ADAMS      CLERK           1100          1
  7521 WARD       SALESMAN        1250          2
  7654 MARTIN     SALESMAN        1250          2
  7934 MILLER     CLERK           1300          2
  7844 TURNER     SALESMAN        1500          3
  7499 ALLEN      SALESMAN        1600          3
  7782 CLARK      MANAGER         2450          4
  7698 BLAKE      MANAGER         2850          4
  7566 JONES      MANAGER         2975          4
  7788 SCOTT      ANALYST         3000          4
  7902 FORD       ANALYST         3000          4
  7839 KING       PRESIDENT       5000          5

14개 행이 선택되었습니다.

3) Self Join

동일 테이블 사이에서의 조인.
원하는 데이터들이 동일 테이블에 있을 때 사용
식별을 위해 반드시 테이블 alias 지정해주고, 컬럼명 앞에 테이블 alias 붙여서 어느 테이블의 컬럼인지 식별해줘야 함

예) 7782 사원보다 큰 급여를 받는 사원을 출력해라
: 원하는 데이터 (사원번호, 급여)가 employee 테이블에 있음

SELECT e.empno, e.sal
FROM emp e, emp j
WHERE j.empno=7782 AND e.sal > j.sal;

예 2) 사원 이름 옆에 매니저 이름을 출력해보자
: 원하는 데이터 (사원번호, 매니저번호)가 employee 테이블에 있음

SELECT e.empno, e.ename, e.mgr, j.empno, j.ename
FROM emp e, emp j
WHERE e.mgr = j.empno;

 EMPNO ENAME             MGR MGR_NAME  
------ ---------- ---------- --------
  7902 FORD             7566 JONES     
  7788 SCOTT            7566 JONES     
  7900 JAMES            7698 BLAKE     
  7844 TURNER           7698 BLAKE     
  7654 MARTIN           7698 BLAKE     
  7521 WARD             7698 BLAKE     
  7499 ALLEN            7698 BLAKE     
  7934 MILLER           7782 CLARK     
  7876 ADAMS            7788 SCOTT     
  7782 CLARK            7839 KING      
  7698 BLAKE            7839 KING 
  7566 JONES            7839 KING      
  7369 SMITH            7902 FORD      

13개 행이 선택되었습니다.

4) Outer Join

일치하는 행만 반환(= Inner JOIN)하는 것이 아니라, 일치하지 않는 행도 반환
- Left Outer Join : 왼쪽 테이블의 일치하지 않는 행도 모두 반환
- Right Outer Join : 오른쪽 테이블의 일치하지 않는 행도 모두 반환
값이 없는쪽 테이블에 (+) 붙임
Oracle 조인에서 (+)는 한쪽 테이블에만 지정할 수 있다.

예) 사원번호, 이름과 각 사원의 매니저인 사원번호, 이름을 출력해보자

SELECT e.empno, e.ename, e.mgr, j.empno, j.ename
FROM emp e, emp j
WHERE e.mgr = j.empno(+);

-- (= Left Outer Join)

 EMPNO ENAME             MGR      EMPNO ENAME     
------ ---------- ---------- ---------- ----------
  7902 FORD             7566       7566 JONES     
  7788 SCOTT            7566       7566 JONES     
  7900 JAMES            7698       7698 BLAKE     
  7844 TURNER           7698       7698 BLAKE     
  7654 MARTIN           7698       7698 BLAKE     
  7521 WARD             7698       7698 BLAKE     
  7499 ALLEN            7698       7698 BLAKE     
  7934 MILLER           7782       7782 CLARK     
  7876 ADAMS            7788       7788 SCOTT     
  7782 CLARK            7839       7839 KING      
  7698 BLAKE            7839       7839 KING 
  7566 JONES            7839       7839 KING      
  7369 SMITH            7902       7902 FORD      
  7839 KING                                       

14개 행이 선택되었습니다.

: emp j 테이블은 7839 사원에 대해 표시할 값이 없지만 출력됨

2. ANSI Syntax

1) JOIN ON (= Inner join)

ON절을 사용하여 조인할 컬럼 지정

<기본 형식>

SELECT table1.column1, table2.column2, ...
FROM table1 JOIN table2
ON table1.column1 = table2.column2

2) Outer Join

일치하지 않는 행도 반환
- LEFT JOIN : 왼쪽 테이블의 일치하지 않는 행도 모두 반환
- RIGHT JOIN : 오른쪽 테이블의 일치하지 않는 행도 모두 반환
- FULL OUTER JOIN : 양쪽 테이블의 일치하지 않는 행도 모두 반환
  ANSI 조인에서는 FULL OUTER JOIN 가능하다

예) 사원이 없는 부서도 모두 출력

-- ORACLE Syntax
SELECT e.employee_id, d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;

👇 같은 표현

-- ANSI Syntax (RIGHT JOIN)
SELECT e.employee_id, d.department_id, d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME               
----------- ------------- ------------------------------
        200            10 Administration                
        201            20 Marketing                     
        202            20 Marketing 
        
        ...           
        
        205           110 Accounting                    
        206           110 Accounting                    
                      120 Treasury                      
                      130 Corporate Tax                 
                      140 Control And Credit            
                      150 Shareholder Services
                      160 Benefits                      
                      170 Manufacturing                 
                      180 Construction                  
                      190 Contracting                   
                      200 Operations                    
                      210 IT Support                    
                      220 NOC                           
                      230 IT Helpdesk                   
                      240 Government Sales              
                      250 Retail Sales                  
                      260 Recruiting
                      270 Payroll                       

122개 행이 선택되었습니다.

예2) 사원이 없는 부서, 부서정보가 없는 사원 모두 출력 (FULL OUTER JOIN)

SELECT e.employee_id, e.department_id, d.*
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME               
----------- ------------- ----------------
    100                90 Executive                     
    101                90 Executive                     
    102                90 Executive                     
                              
    ...         
   
    176                80 Sales
    177                80 Sales                         
    178                              ( => 부서가 없는 사원도 출력 )        
    179                80 Sales                         
    180                50 Shipping                      
    
    ...      
    
    203                40 Human Resources               
    204                70 Public Relations              
    205               110 Accounting                    
    206               110 Accounting                    
   ( 사원이 없는  =>   220 NOC                         
    부서도 출력  )     170 Manufacturing                 
                      240 Government Sales
                      210 IT Support                    
                      160 Benefits                      
                      150 Shareholder Services          
                      250 Retail Sales                  
                      140 Control And Credit            
                      260 Recruiting                    
                      200 Operations                    
                      120 Treasury                      
                      270 Payroll                       
                      130 Corporate Tax                 
                      180 Construction
                      190 Contracting                   
                      230 IT Helpdesk                   

123개 행이 선택되었습니다.

3) CROSS JOIN

한 테이블의 모든 행을 다른 테이블의 모든 행과 조인
너무 많은 행을 반환해서 잘 쓰지 않음

4) NATURAL JOIN

이름과 데이터타입이 같은 모든 컬럼들을 조건으로 해서 조인
조인 조건을 명시하지 않아도 됨
SELECT 리스트에 컬럼명 쓸때도 테이블명 명시하지 않아도 됨

예1)

테이블	컬럼
departments	department_id, department_name, manager_id, location_id
locations	location_id, street_address, postal_code, city, state_province, country_id

location_id 컬럼이 같으므로 이를 조건으로 조인된다.

SELECT department_id, department_name, location_id, city
FROM departments NATURAL JOIN locations
WHERE city = 'Seattle';

예2) 이름이 같은 컬럼이 두개 있는 경우 주의!!

테이블	컬럼
employees	employee_id, first_name, ..., manager_id, department_id
departments	department_id, department_name, manager_id, location_id

-- 1) 부서번호를 기준으로 두 테이블을 조인
SELECT employee_id, first_name, d.department_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- 2) ????
SELECT employee_id, first_name, department_id, department_name
FROM employees NATURAL JOIN departments;

e.manager_id는 각 사원의 상사 역할을 하는 사원번호를 나타내는 데이터이고, d.manager_id는 각 부서의 부서장 역할을 하는 사원번호를 나타내는 데이터이다.
의미하는 바가 다른 컬럼이지만, 두 테이블에서 이름과 데이터타입이 같기 때문에 NUTURAL JOIN에서 조건으로 사용되어 버린다.
NATURAL JOIN은 테이블 간의 동일한 컬럼이 하나만 존재할 때 사용해야 한다.

5) JOIN USING

조인 조건으로 사용될 컬럼을 USING절에 명시해준다.
위의 예2 처럼 만약 USING절에 조인 조건으로 명시된 컬럼 외에 동일한 컬럼이 또있다면 SELECT 리스트에서 앞에 테이블명을 명시한다 (e.manager_id)

SELECT employee_id, first_name, e.manager_id, department_id, department_name
FROM employees JOIN departments
USING (department_id);

hyem

이전 포스트