oracle中的查询方式,oracle中的多表查询方式

1.等值连接

SELECT e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

FROM employees e , departments d

WHERE e.department_id = d.department_id;

2.非等值连接

SELECT e.last_name, e.salary, j.grade_level

FROM employees e, job_grades j

WHERE e.salary

BETWEEN j.lowest_sal AND j.highest_sal;

3.左(右)外连接

①右外连接

SELECT e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id(+) = d.department_id ;

4.自连接

返回“Xxxworks for Xxx”

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

SELECT worker.last_name || ' works for '

|| manager.last_name

FROM employees worker, employees manager

WHERE worker.manager_id = manager.employee_id ;

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

SELECT e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

FROM employees e JOIN departments d

ON (e.department_id = d.department_id);

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

SELECT employee_id, city, department_name

FROM 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_name

FROM employees e

LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id) ;

8.右外连接

SELECT e.last_name, e.department_id, d.department_name

FROM employees e

RIGHT OUTER JOIN departments d

ON (e.department_id = d.department_id) ;

9.满外连接

SELECT e.last_name, e.department_id, d.department_name

FROM employees e

FULL OUTER JOIN departments d

ON (e.department_id = d.department_id) ;