ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle9i中的多表连接

oracle9i中的多表连接

原创 Linux操作系统 作者:pino 时间:2009-05-12 13:22:40 0 删除 编辑

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

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-04-10

  • 博文量
    9
  • 访问量
    9350