본문 바로가기

Daily Report

120920 Oracle Join 예제(join의 4가지예)

join 문 4종류

: 같은 의미의 서로 다른  join문 4가지를 분석합니다


first_name이 king이고 last_name이 steven인 사람의 부서와 부서명을 검색합니다

이것을 표현한다면 4가지 중류가 있습니다


select departments.department_name                             

from employees, departments                                   

where employees.department_id = departments.department_id    

and employees.last_name = 'King'                                             

and employees.first_name = 'Steven';                                


select d.department_name            

from employees e, departments d        

where e.department_id = d.department_id

and e.last_name = 'King'

and e.first_name = 'Steven';


select d.department_name                   

from employees e join departments d                           

on e.department_id = d.department_id                                   

where e.last_name = 'King'                                          

and e.first_name = 'Steven';                               


select department_name       

from employees join departments 

using(department_id)  

where last_name = 'King' 

and first_name = 'Steven';   


예제)employees에 테이블에 있는 사람중 manager_id가 100번인 사람을 뽑아 그중 employees테이블과  departments테이블의 department_id가 같은 사람중에 last_name이 Da Haan이고 first_name이 Lex인 사람을 출력하세요 앞에서 소개한 4가지의 방법으로 출력합니다


select *                          

from employees, departments                                   

where employees.department_id = departments.department_id   

and employees.manager_id = 100

and employees.last_name = 'De Haan'                                             

and employees.first_name = 'Lex';         


select *                          

from employees, departments                                   

from employees e join departments d                           

on e.department_id = d.department_id       

where e.manager_id = 100

and e.last_name = 'De Haan'                                             

and e.first_name = 'Lex';

   


select *                          

from employees, departments                                   

from employees e departments d                           

where e.department_id = d.department_id       

where e.manager_id = 100

and e.last_name = 'De Haan'                                             

and e.first_name = 'Lex';


select *                          

from employees join departments 

using(department_id)  

where employees.manager_id = 100

and last_name = 'De Haan'                                             

and first_name = 'Lex';