Oracle学习----多表连接

来源：互联网发布：linux fd 编辑：程序博客网时间：2024/04/28 14:31

连接类型

笛卡连接、等值连接、非等值连接、外部连接、自连接

笛卡尔积在下列情况下产生：

— 忽略了一个连接条件

— 一个连接条件失效

— 第一张表的所有行和第二张表的所有行连接

为了避免笛卡尔积的产生，通常包含一个有效连接条件的WHERE子句。

使用一个连接去查询多张表的数据

SELECT table1.column,table2.clumn FROM table1,table2 WHERE table1.column1 = table.column2;

在WHERE子句中设定连接条件，当多张表的列重名时，以表名作为列名的前缀。

等值连接

SELECT employees.employee_id,employees.last_name,employees.department_id,departments.department_id,departments.location_id

FROM employees,departments WHERE employees.department_id = departments.department_id;

用表前缀在多张表中限定列名、用表前缀提高系统性能、用列别名区分不同表中同名的列

使用表别名

用表别名简化查询、用表名前缀提高性能

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;

连接n张表至少需要n-1个连接条件。例如：连接3张表至少要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;

外部连接

利用一个外部连接查询那些无法满足连接条件的行，外部连接运算符是一个加号（+）

SELECT e.last_name,e.department_id,d.department_name FROM employees e,departments d WHERE e.department_id(+) = d.department_id;

自连接

WORKER表中的MANAGER_ID等于MANAGER表中的EMPLOYEE_ID

SELECT worker.last_name || ' works for ' || manager.last_name FROM employees worker,employees manager WHERE worker.manager_id = manager.employee_id;

SQL1999语法介绍

Cross join 解释：CROSS JOIN产生了一个笛卡尔积（Cartesian product），就象是在连接两个表格时忘记加入一个WHERE子句一样

eg：SELECT last_name,dept_id FROMemp,dept; SELECT ename,loc FROMemp CROSS JOIN dept;

Natural join NATURALJOIN能够通过在两个表的列名来自动的检查join。NATURAL JOIN要求在每一个表格中的字段名相同

eg：SELECTename,dname,loc FROM dept,emp WHERE dept.deptno = emp.deptno; SELECT ename,dname,loc FROM dept NATURAL JOIN emp;

Join using() 如果几个字段有同样的名字，而你又不想用所有的这些字段来连接的时候，你就可以用USING子句，指定连接列名。

eg：SELECT dept_id, city FROM departments, locations WHERE departments.location_id =location.location_id;

SELECT department_name,city FROM departments JOIN locations USING (location_id);

Join on() ON子句被用于当在两个表中的字段名不一致时来连接表

eg：SELECT department_name,city FROM department, location WHEREdepartment.location_id =location.loc_id;

SELECTdepartment_name, city FROM department d JOIN location l ON (d.location_id =l.id);

Left outer join on() 在LEFTOUTER JOIN中，会返回所有左边表中的行，甚至在被连接的表中没有可对应的列名的情况下也如此。

eg：SELECT last_name,dept_id FROM emp e, dept d WHERE e.department_id =d.department_id(+);

SELECT last_name,dept_id FROM emp LEFT OUTER JOIN Dept ONe.dept_id = d.dept_id;

Right outer join() 在RIGHTOUTER JOIN中返回的是表中所有右边的行，甚至在被连接的表中没有对应的情况下也如此。

eg：SELECT last_name,d.dept_id FROM employees e, departments d WHERE e.department_id(+) =d.department_id;

SELECT last_name,d.dept_id FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id =d.department_id);

Full outer join on() FULL OUTER JOIN=LEFT OUTJOIN+RIGHT OUT JOIN

eg： SELECT e.last_name,d.dept_id FROM employees e，departments d WHERE e.department_id = d.department_id（+）

UNION AL SELECT e.last_name,d.dept_id FROM employees e，departments d WHERE e.department_id （+）=d.department_id;

SELECT e.last_name,d.dept_id FROM employees e FULL OUTER JOIN departments d ON (e.department_id =d.department_id);

附加条件

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) and e.manager_id = 149;