SQL join 관련

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