oracle9i中的多表连接

没有使用任何连接条件的两个表的链接，叫做笛卡尔积情况：

EMPLOYEES表有20行记录，DEPARTMENTS表有10条记录，那么

select * from employees e,departments d;

结果就是employees表中的每一行都会和department表中的每行做连接，那么得出的结果20*10 行的笛卡尔结果集；这样的结果集对我们的应用没有用处；

加入条件后，就有几种连接的类型：

oracle自己的连接类型:

• Equijoin 等连接
• Non-equijoin 不等连接
• Outer join 外连接
• Self join 自连接

SQL:1999 的链接：

• Cross joins
• Natural joins
• Using clause
• Full or two sided outer joins
• Arbitrary join conditions for outer joins

• Equijoin等连接,也叫做 简单连接或者内连接

SELECT e.last_name,d.department_name

FROM  employees e,departments d

WHERE e.last_name='Goyal' and  e.department_id =d.department_id

超过两个表的相等连接：

SELECT e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;

•    Non-Equijoin，通过连接条件使用的操作符：

>,,!=,^,BEGIN...AND..

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;

• out-join ，同时会返回一些不满足连接条件的row

外连接的语法：在条件连接中加入：(+)

右连接：

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;

左连接：

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column= table2.column(+);

例如：

查找雇员的名字，所属的部门号，和部门的名字时，同时需要查询出没有任何雇员的部门名字，可以使用下面的右连接；

可以这样来区分左右连接：两个表的先后顺序已经定下来，如下所写,如果(+)在左边，那么可以叫做右连接，结果集中包括：满足条件的集合，和第二个表中不满足0条件的集合；

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

外连接符号(+) 能够放置在where条件中的任意一边，但是不能同时放置在两边；

Self-join自连接，自连接的表必须使用别名，可以想象成是两个不同的表来理解；

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

如果是使用SQL99标准的查询语法是：

SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)];

• CROSS JOIN 交叉连接:这和两张表的无条件连接是一样的结果，也是上面说的迪卡儿积；

如：SELECT last_name, department_name
FROM employees
CROSS JOIN departments ;

• NATURL JOIN 自然连接：基于两个表中的所有相同名字的列，连接的条件是等于所有相同列名分别相同的row，并且相同名字的列的字段类型也是相同的，否则会报错!

SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations ;

从这里可以看出，和带where的条件的写法上的不同，默认会进行同名字段的相等连接！书写方式比较简单！ 假设这两个表里只有一个相同名字的字段；那么可以改写成下面的写法：

SELECT department_id, department_name,
departments.location_id, city
FROM departments, locations
WHERE departments.location_id = locations.location_id;

另外在自然连接上也可以增加其他的条件限制，即加where条件，如下应用：

SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations
WHERE department_id IN (20, 50);

• 使用USING 的连接

join的字段也必须是两个表里都有的字段才行，如下是正确的：

SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;

它也能够被写成是一个相等连接：
SELECT employee_id, last_name,
employees.department_id, location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

下面的这个则是错误的：

SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE d.location_id = 1400;
ORA-25154: column part of USING clause cannot have qualifier

• 使用ON的连接

使用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);

不同名的：

SELECT e.last_name emp, m.last_name mgr
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id);

• Creating Three-Way Joins with the ON Clause 三路连接

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;

可以改写成等连接：
SELECT employee_id, city, department_name
FROM employees, departments, locations
WHERE employees.department_id = departments.department_id
AND departments.location_id = locations.location_id;

也可以改写成使用USING:

SELECT e.employee_id, l.city, d.department_name
FROM employees e
JOIN departments d
USING (department_id)
JOIN locations l
USING (location_id);

• left outer join

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) ;

相同效果的oracle内部的写法：

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

• right outer join

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) ;

相同效果的oracle内部的写法：

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

• full outer join

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) ;

由于oracle内部也没有两边都是用的(+)的写法，但是可以使用union的操作

SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id (+) = d.department_id
UNION
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE 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 ;

以上SQL中连接的语法，oracle 内部的写法，和SQL99标准的写法！

可以根据自己应用的情况，选择自己喜欢的写法！

 

                

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/241699/viewspace-594829/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/241699/viewspace-594829/