Chinaunix首页 | 论坛 | 博客
  • 博客访问: 697636
  • 博文数量: 160
  • 博客积分: 8847
  • 博客等级: 中将
  • 技术积分: 1656
  • 用 户 组: 普通用户
  • 注册时间: 2010-11-25 16:46
个人简介

。。。。。。。。。。。。。。。。。。。。。。

文章分类

全部博文(160)

文章存档

2015年(1)

2013年(1)

2012年(4)

2011年(26)

2010年(14)

2009年(36)

2008年(38)

2007年(39)

2006年(1)

分类: Oracle

2007-03-09 19:28:06

一般的相等连接:
select * from a, b where a.id = b.id;
这个就属于内连接。

对于外连接:
Oracle中可以使用“(+) ”来表示,9i可以使用LEFT/RIGHT/FULL OUTER JOIN

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);
等价于
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id(+)
结果为:所有员工及对应部门的记录,包括没有对应部门编号department_id的员工记录。

SELECT * FROM LIUJUN;

AA                   BB                     AA                   BB                    
-------------------- ---------------------- -------------------- ----------------------
B                    10                     B                    2                     
B                    10                     B                    2                     

2 rows selected

SELECT * FROM LIUJUN2;

AA                   BB                     AA                   BB                    
-------------------- ---------------------- -------------------- ----------------------
A                    1                                                                 
B                    10                     B                    2                     
B                    10                     B                    2  

SELECT * FROM LIUJUN A,LIUJUN2 B
WHERE A.AA(+)=B.AA;

上边的左外连接是以B表为准,即使B.AA字段的值在A.AA中找不到匹配,也把B中的记录显示出来;

右边的记录为3条,所以结果为:

AA                   BB                     AA                   BB                    
-------------------- ---------------------- -------------------- ----------------------
A                    1                                                                 
B                    10                     B                    2                     
B                    10                     B                    2                     

3 rows selected      

SELECT * FROM LIUJUN A,LIUJUN2 B
WHERE A.AA=B.AA(+);
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);
等价于
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+)=d.department_id
结果为:所有员工及对应部门的记录,包括没有任何员工的部门记录。

SELECT * FROM LIUJUN;

AA                   BB                     AA                   BB                    
-------------------- ---------------------- -------------------- ----------------------
B                    10                     B                    2                     
B                    10                     B                    2                     

2 rows selected

SELECT * FROM LIUJUN2;

AA                   BB                     AA                   BB                    
-------------------- ---------------------- -------------------- ----------------------
A                    1                                                                 
B                    10                     B                    2                     
B                    10                     B                    2  

SELECT * FROM LIUJUN A,LIUJUN2 B
WHERE A.AA=B.AA(+);

上面的右外连接是以A表为准,虽然B表中有三条记录,只会显示A.AA=B.AA和A中剩余的记录。

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);
结果为:所有员工及对应部门的记录,包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。

SELECT * FROM LIUJUN;

AA                   BB                     AA                   BB                    
-------------------- ---------------------- -------------------- ----------------------
B                    10                     B                    2                     
B                    10                     B                    2                     

2 rows selected

SELECT * FROM LIUJUN2;

AA                   BB                     AA                   BB                    
-------------------- ---------------------- -------------------- ----------------------
A                    1                                                                 
B                    10                     B                    2                     
B                    10                     B                    2  

SELECT * FROM LIUJUN A
FULL OUTER JOIN LIUJUN2 B
ON (A.AA=B.AA);

这是一个完全外连接,所以,会把A和B中的所有记录显示出来;

AA                   BB                     AA                   BB                    
-------------------- ---------------------- -------------------- ----------------------
B                    10                     B                    2                     
B                    10                     B                    2                     
A                    1                                                                 

ORACLE8i是不直接支持完全外连接的语法,也就是说不能在左右两个表上同时加上(+),下面是在ORACLE8i可以参考的完全外连接语法
select t1.id,t2.id from table1 t1,table t2 where t1.id=t2.id(+)
union
select t1.id,t2.id from table1 t1,table t2 where t1.id(+)=t2.id

阅读(1037) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~