Oracle知识基础（二）

连接查询

test1表

test2表

内联接

内连接就是把两个表的数据通过某个公用的字段联系起来，只返回在两个表中互相匹配的数据。如下通过name字段将两张表的数据联系起来：

select * from test1 a,test2 b where a.name=b.name;select * from test1 a inner join test2 b on a.name=b.name;

左联接（左外连接）

左连接就是将左表的数据全部查找出来，然后将与左表匹配的右表数据查找出来。即便左表有数据与右表不对应也显示出来，以下是左连接的三种写法：

select * from test1 a left join test2 b on a.name=b.name;select * from test1 a left outer join test2 b on a.name=b.name;select * from test1 a,test2 b where a.name=b.name(+);

右联接（右外连接）

右连接和左连接一样，是优先返回右表全部数据，然后将与右表匹配的左表数据查找出来。

select * from test1 a right join test2 b on a.name=b.name;select * from test1 a right outer join test2 b on a.name=b.name;select * from test1 a,test2 b where a.name(+)=b.name;

全连接

FULL JOIN 关键字会从左表 和右表 那里返回所有的行。如果 左表 中的行在右表 中没有匹配，或者如果 右表 中的行在 左表 中没有匹配，这些行同样会列出。

select * from test1 a full join test2 b on a.name=b.name;

合并查询

合并查询就是用于合并两个或多个 SELECT 语句的结果集，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。合并查询中，只能有一个 order by 子句且放在最后。

union查询会将多个结果集中相同的数据去掉

select name from test1 union select name from test2;

union all查询则返回全部数据，不管是否有重复

select name from test1 union all select name from test2 order by name;

Intersect
取数个结果集中的交集,

select name from test1 Intersect  select name from test2;

Minus
取数个结果集中的差集

select name from test1 Minus  select name from test2;

子查询

单行子查询

单行子查询就是子查询返回值只有一个的查询。

select * from test1 where  name=(select name from test2 where age='30');

多行子查询

多行子查询就是子查询返回值有多个的时候

test1所要查询的数据为在test2中存在的name的数据select * from test1 where  name in (select name from test2);test1所要查询的数据为除去在test2中所查询出数据select * from test1 where  name not in (select name from test2);

相关子查询

参考http://www.cnblogs.com/netserver/archive/2008/12/25/1362615.html
下面用的exists和in效果差不多，但是效率比in高。in在查询时会通过子查询返回的数据进行查询，而exists不关心子查询返回的是什么值，只关心是否返回了值。

select * from test1 a  where  exists (select 1 from test2 b where a.name=b.name);select * from test1 where  name in (select name from test2);

not exists 和not in等效。

select * from test1 a  where not  exists (select name from test2 b where a.name=b.name);select * from test1 where  name not in (select name from test2);

既然exists和in，not exists 和not in查询结果一样，那么可以认为这两种方法是一样的吗？

重点：这两种方式在不同的环境下会有查询效率的问题。

in是把外表和内表作hash 连接，而exists 是对外表作loop 循环，每次loop 循环再对内表进行查询。

一直以来认为exists 比in 效率高的说法是不准确的。如果查询的两个表大小相当，那么用in 和exists 差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。

not in 和not exists

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；

而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists 都比not in 要快。

总结：exist会针对子查询的表使用索引， not exist会对主子查询都会使用索引，in与子查询一起使用的时候,只能针对主查询使用索引，
not in则不会使用任何索引。