连接查询
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则不会使用任何索引。