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';
'Daily Report' 카테고리의 다른 글
120926 Oracle view의 분류(스칼라뷰,인라인뷰,서브쿼리), 분석함수(rank(),over()) (0) | 2012.09.26 |
---|---|
120924 Oracle 하위질의01 (0) | 2012.09.24 |
120919 Oracle Subquery 보충 (0) | 2012.09.19 |
120917 Oracle ER Diagram_modeler (0) | 2012.09.17 |
120912 Oracle data type (0) | 2012.09.12 |