oracle中的多表查询方式

来源：互联网发布：免费下载涂涂乐软件编辑：程序博客网时间：2024/06/05 08:25

1.等值连接

SELECT e.employee_id, e.last_name, e.department_id,       d.department_id, d.location_idFROM   employees e , departments dWHERE  e.department_id = d.department_id;

2.非等值连接

SELECT e.last_name, e.salary, j.grade_levelFROM   employees e, job_grades jWHERE  e.salary        BETWEEN j.lowest_sal AND j.highest_sal;

3.左（右）外连接

①右外连接

SELECT e.last_name, e.department_id, d.department_nameFROM   employees e, departments dWHERE  e.department_id(+) = d.department_id ;

4.自连接

返回“Xxx works for Xxx”

说明：worker和manager同属于一个表，别名

SELECT worker.last_name || ' works for '        || manager.last_nameFROM   employees worker, employees managerWHERE  worker.manager_id = manager.employee_id ;

5.join......on......

SELECT e.employee_id, e.last_name, e.department_id,        d.department_id, d.location_idFROM   employees e JOIN departments dON     (e.department_id = d.department_id);

6.on子句创建多个表连接

SELECT employee_id, city, department_nameFROM   employees e JOIN   departments d ON     d.department_id = e.department_id JOIN   locations l   ON     d.location_id = l.location_id;

7.左外连接

SELECT e.last_name, e.department_id, d.department_nameFROM   employees eLEFT OUTER JOIN departments dON   (e.department_id = d.department_id) ;

8.右外连接

SELECT e.last_name, e.department_id, d.department_nameFROM   employees eRIGHT OUTER JOIN departments dON    (e.department_id = d.department_id) ;

9.满外连接

SELECT e.last_name, e.department_id, d.department_nameFROM   employees eFULL OUTER JOIN departments dON   (e.department_id = d.department_id) ;

0 0