oracle连接和层次查询

--内连接

SELECT t.employee_id, t.department_id, t1.department_name, t2.job_title

  FROM EMPLOYEES t

  JOIN departments t1

    ON (t.department_id = t1.department_id)

  JOIN jobs t2

    ON (t.job_id = t2.job_id)

 where t.employee_id >= 100;

SELECT t.employee_id, t.department_id, t1.department_name, t2.job_title

  FROM EMPLOYEES t

  JOIN departments t1

    ON (t.department_id = t1.department_id)

  JOIN jobs t2

    ON (t.job_id = t2.job_id)

 and t.employee_id >= 100;

 

--自连接

select t.employee_id,t.first_name,t1.employee_id,t1.first_name from employees t

join employees t1

on (t.manager_id=t1.employee_id);

--左外连接

select e.last_name,e.department_id,d.department_name

from employees e left outer join departments d

on (e.department_id=d.department_id);

--右外连接

select e.last_name,d.department_id,d.department_name

from employees e right outer join departments d

on (e.department_id=d.department_id); 

--全外连接

select e.last_name,d.department_id,d.department_name

from employees e full outer join departments d

on (e.department_id=d.department_id);

--交叉连接

select e.last_name,d.department_id,d.department_name

from employees e cross join departments d;

--INTERSECT 显示共有的，消除重复的

SELECT employee_id, job_id

  FROM employees

INTERSECT

SELECT employee_id, job_id FROM job_history;

--MINUS 返回第一个查询有但第二个查询中不存在的所有唯一行记录

SELECT employee_id, job_id

  FROM employees

MINUS

SELECT employee_id, job_id FROM job_history;

--层次查询，自底到顶

select t.employee_id,t.last_name,t.job_id,t.manager_id,level from employees t

start with t.employee_id=101

connect by prior t.manager_id=t.employee_id;

--层次查询，自顶到底

select t.employee_id,t.last_name,t.job_id,t.manager_id,level from employees t

start with t.employee_id=101

connect by prior t.employee_id=t.manager_id;