Computer/DB

SQL join 관련

미처서 2014. 9. 1. 15:13

desc departments //칼럼정보보기


select first_name, hire_date, salary, employees.department_id, department_name

from employees, departments

where employees.department_id = departments.department_id


select first_name, hire_date, salary, department_id, department_name

from employees join departments using (department_id)


select e.first_name, e.hire_date, e.salary, e.department_id, d.department_name

from employees e join departments d on e.department_id = d.department_id


select e.ename, e.hiredate, e.sal, s.grade, s.losal, s.hisal

from emp e, salgrade s

where e.sal between s.losal and s.hisal


select e.first_name, e.hire_date, e.salary, j.job_id, j.job_title, j.min_salary, j.max_salary

from employees e, jobs j

where e.job_id = j. job_id





select first_name, hire_date, department_name

from employees e full join departments d


select e.first_name, e.hire_date, d.department_name

from employees e left join departments d

on e.department_id = d.department_id


select e.first_name, j.job_title

from employees e right join jobs j

on e.job_id=j.job_id


select * from jobs


select e.first_name, e.hire_date, nvl(d.department_name, '부서없음')

from employees e left join departments d

on e.department_id = d.department_id


select e.first_name, e.hire_date, nvl2(d.department_name, d.department_name||'**', '부서없음')

from employees e left join departments d

on e.department_id = d.department_id




//natural 조인 자동으로해줌

select emp.ename, dept.dname

from dept natural join emp


select emp.ename, dept.dname

from dept, emp

where dept.deptno = emp.deptno


//Cross 조인 다 곱해서 해줌

select e.first_name, d.department_name

from employees e, departments d


select e.first_name, d.department_name

from employees e cross join departments d