Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1372420
  • 博文数量: 205
  • 博客积分: 6732
  • 博客等级: 准将
  • 技术积分: 2835
  • 用 户 组: 普通用户
  • 注册时间: 2008-09-04 17:59
文章分类

全部博文(205)

文章存档

2016年(1)

2015年(10)

2014年(1)

2013年(39)

2012年(23)

2011年(27)

2010年(21)

2009年(55)

2008年(28)

我的朋友

分类: Oracle

2009-09-29 11:53:20

一、内连接和外连接 内连接用于返回满足连接条件的记录;而外连接则是内连接的扩展,它不仅会满足连接条件的记录,而且还会返回不满足连接条件的记录,
语法如下:
select table1.column,table2.column from table1 [inner|left|right|full]join table2 on table1.column=table2.column;
inner join表示内连接、left join表示左外连接、right join表示右外连接、full join表示全连接;on用于指定连接条件。
注意:
如果使用form内、外连接,则必须使用on操作符指定连接条件;如果使用(+)操作符连接,则必须使用where指定连接条件。

1、内连接 内连接查询返回满足条件的所有记录,默认情况下没有指定任何连接则为内连接,
例如:
select t1.name,t2.name from cip_temps t1 inner join cip_tmp t2 on t1.ID=t2.id;

2、左外连接 左外连接查询不仅返回满足条件的所有记录,而且还会返回不满足连接条件的连接操作符左边表的其他行,
例如:
select t1.name,t2.name from cip_temps t1 left join cip_tmp t2 on t1.ID=t2.id;

3、右外连接 右外连接查询不仅返回满足条件的所有记录,而且还会返回不满足连接条件的连接操作符右边表的其他行,
例如:
select t1.name,t2.name from cip_temps t1 right join cip_tmp t2 on t1.ID=t2.id;

4、全连接 全连接查询不仅返回满足条件的所有记录,而且还会返回不满足连接条件的其他行,
例如:
select t1.name,t2.name from cip_temps t1 full join cip_tmp t2 on t1.ID=t2.id;

5、(+)操作符 在oracle9i之前,当执行外连接时,都是使用连接操作符(+)来完成的,尽管可以使用操作符(+)执行外连接操作,但是oracle9i开始oracle建议使用outer join执行外连接,
使用(+)操作符执行外连接的语法如下:
select table1.column,table2.column from table1,table2 where table1.column(+)=table2.column;
注意:
当使用(+)操作符执行外连接时,应当将该操作符放在显示较少行(完全满足连接条件行)一端。
(+)操作符只能出现在where子句中,并且不能与outer join语法同时使用。
当使用(+)操作符执行外连接时,如果在where语句中包含多个条件,则必须在所有的条件中都包含(+)操作符。
(+)操作符只能适用于列,而不能适用于表达式。
(+)操作符不能与or和in操作符一起使用。
(+)操作符只能用于左外连接和右外连接,不能用于实现完全连接。

(1)、使用(+)操作符执行左外连接 当使用左外连接时,不仅会返回满足连接条件的所有行,而且还会返回不满足连接条件的左边边的其他行。
因为(+)操作符要放到行数较少的一端,所以在where子句中应当将该操作符放到右边表的一端,
示例如下:
select t1.name,t2.name from cip_temps t1,cip_tmp t2 where t1.ID=t2.id(+);
(2)、使用(+)操作符执行右外连接 当使用右外连接时,不仅会返回满足连接条件的所有行,而且还会返回不满足连接条件的右边边的其他行。
因为(+)操作符要放到行数较少的一端,所以在where子句中应当将该操作符放到左边表的一端,
示例如下:
select t1.name,t2.name from cip_temps t1,cip_tmp t2 where t1.ID(+)=t2.id;

举个例子:
  假设a表和b表的数据是这样的。
  a          b
  id name  id stock 
  1  a       1  15
  2  b       2  50
  3  c      4  25

内连接(对等连接):
 select * from a inner join b on a.id=b.id
 这个语法是连接查询中的内连接,它产生的结果是 两个表相匹配的记录出现在结果列表中。
 根据上面的表,出现的结果是这样的
 a.id name b.id stock
 1    a    1    15
 2    b    2    50
 ----------------------------

 select * from a,b where a.id=b.id (也被称作:对等连接)
 这个语法是内连接的另外一种写法,其执行结果与inner join 一样

 对等连接
 普通写法:
  select * from a join b on a.id = b.id join c on b.id = c.id;
 传统写法:
  select * from a, b, c where a.id = b.id and b.id = c.id;
 上面的就是一种对等连接,即默认的join方式。特点是只显示连接的表中存在而且相等的记录,
 其它的记录均不显示.传统的写法也是一种对等连接,只显示匹配条件的记录.

左右外连接:
 select * from a left/right join b on a.id=b.id
 这个是外连接语法中的左外连接或右外连接

 如果是左外连接的话,它将显示a表的所有记录,
 select a.*,b.* from a left join b on a.id=b.id
 ==
 select a.*,b.* from a , b where a.id=b.id(+)
 查询的结果是这样的:
 a.id name b.id stock
 1    a    1    15
 2    b    2    50
 3    c    null null 
 --------------------------------------------

 如果是右外连接的话,它将显示b表的所有记录,
 select a.*,b.* from a right join b on a.id=b.id
 ==
 select a.*,b.* from a , b where a.id(+) = b.id
 查询的结果是这样的:
 a.id name b.id stock 
 1    a    1    15
 2    b    2    50
 null null 4    25
 --------------------------------------------

 select a.*,b.* from a left join b on a.id = b.id
 ==
 select a.*,b.* from a left outer join b on a.id = b.id

 select a.*,b.* from a right join b on a.id = b.id 
 ==
 select a.*,b.* from a right outer join b on a.id = b.id

 ----------上面两种一样left join是left outer join的简写
 同样 right join 是 right outer  join 的简写
 但是:
   select a.*,b.* from a left inner join b on a.id = b.id
 没有这种写法,错误的语句.

全连接:
 select * from a full join b on a.id=b.id
 这个语法是连接查询中的全连接,它产生的结果是
 两个表相匹配的记录和不满足连接条件的其他行出现在结果列表中。
 根据上面的表,出现的结果是这样的
 a.id name b.id stock
 1    a    1    15
 2    b    2    50
 3    c    null null
 null null 4    25
 ------------------------------------------- 

交叉连接(笛卡尔积):
 没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。
不过,如果添加一个WHERE 子句,则交叉联接的作用将同内联接一样。
eg:
select * from a cross join b where a.id = b.id;
==
select * from a (inner) join b on a.id = b.id;
注意:不能使用ON 关键字,只能用WHERE条件

就是两个表在数据库上的排列组合,效果就是笛卡尔积。一个n行数据的表与一个m行数据的表进行交叉连接之后,结果中包含n*m行数据。
 具体语法:
 select * from a cross join b;
 或
 select * from a, b;
 查询结果为:
 a.id name b.id stock 
 1    a    1    15
 1    a    2    50
 1    a    4    25
 2    b    1    15
 2    b    2    50
 2    b    4    25
 3    c    1    15
 3    c    2    50
 3    c    4    25
 ------------------------------------------- 
 

自然连接:
 
这是Oracle的一种特有的连接方式。它自动连接两个表中数据类型和名称相同的字段,然后根据条件自动地将他们连接起来。
  具体语法:
   select ... from table1 t1 natural left join table2 t2;
  比如:
   select * from a natural (inner/left) join b  ;
  这里并没有指定连接的条件,实际上oracle自动的将a表中的id和b表中的id做了连接。
  也就是实际上相当于:
   select * from a (inner/left) join b on a.id= b.id;
  因为这两张表的这两个字段id的类型和个名称完全相同。所以使用natural join时被自然的连接在一起了。
  PS:
如果自然连接的两个表仅是字段名称相同,但数据类型不同,那么将会返回一个错误。
并且查询列中不能单独出现a.id 或 b.id ,因为id 是关联的查询条件.
  注意:
  select * from a (inner/left) natural join b ; 这种写法不会报错,但是查询结果是有问题的

自连接:
 简单地说就是自己和自己进行连接,连接的条件就是本表的主键。通常认为在数据库中存在父子关系时,应该设计成两张表。但如果父子都是同一实体,就可以简化设计成一张表。
 只要是相同类型实体的多级的分类结构,都可以使用一张表来存储。在实体的所有属性基础上,在添加一个所属上级的ID即可。比如,大小类,多级菜单,组织机构都可以采用这样的方式设计表结构。
eg
 销售品目录树自连接查询

select a.catalog_item_id,
       a.catalog_item_name,
       b.catalog_item_name,
       b.catalog_item_id
  from catalog_item a, catalog_item b
 where a.up_catalog_item_id = b.catalog_item_id
   and a.catalog_id = b.catalog_id
   and a.catalog_id = 2


 员工上下级查询

select yg.empno as 员工, yg.ename as 工号, sj.ename as 上级
  from emp yg, emp sj
 where yg.mgr = sj.empno



二、交集、并集、差集、笛卡尔积
交集:JOIN
 select * from a join b on a.id = b.id;

并集:UNION 
 使用 UNION 运算符组合多个结果
 SELECT name,num FROM Table1
 UNION
 SELECT name,num FROM Table2
 注意:
 查询的字段个数必须相同,Table2的字段类型要跟Table1的相同.
 如果使用 UNION 运算符,那么单独的 SELECT 语句不能包含其自己的 ORDER BY 或 COMPUTE 子句。
 只能在最后一个 SELECT 语句的后面使用一个 ORDER BY 或 COMPUTE 子句;该子句适用于最终的组合结果集。
 GROUP BY 和 HAVING 子句只能在单独的 SELECT 语句中指定。
 只用UNION有重复记录只取一条,用UNION ALL 时取所有重复记录 
 FULLl JOIN是表示并集
 SELECT * FROM Table1 FULLl JOIN Table2 ON table1.id=table2.id

差集:
 NOT IN 表示差集
 SELECT * FROM table1 WHERE name NOT IN (SELECT name FROM table2)

笛卡尔积:
 见上面讲的.

union 和join不一样,union这个运算子是将资料列合并,而join是将栏位合并(我前面所讲)!
如果从栏位合并来讲,full join 算是并集,inner join 算是交集!left join 或right join 不完全是差集,也包括交集的结果,具体你的语句的查询结果如何还是要看实际的语句,就如cross join,加上where就变成inner join,前后的结果相差甚远 。
阅读(1075) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~