create table abin6(id integer,
name nvarchar2(100),
score integer,
constraint pk_abin6 primary key(id));
create table abin7(id integer,
address nvarchar2(100),
sid integer,
constraint pk_abin7 primary key(id),
constraint fk_abin7 foreign key (sid) references abin6(id)
);
select * from abin6 t left join abin7 s on t.id=s.sid and t.id=1;
select * from abin6 t left join abin7 s on t.id=s.sid where t.id=1;
select * from abin6 t,abin7 s where t.id=s.sid(+) ;
select * from abin6 t,abin7 s where t.id(+)=s.sid;
select * from abin6 t,abin7 s where s.sid(+)=t.id;
select * from abin6 t,abin7 s where s.sid=t.id(+);
select * from abin6 t inner join abin7 s on t.id=s.sid;
select * from abin6 t union select * from abin7 s where exists (select * from abin6 k where s.sid=k.id and k.id
=1);
select * from abin6 t full join abin7 s on t.id=s.sid;
select * from abin7 s full join abin6 t on s.sid=t.id;
select * from abin6 natural join abin7;
select * from abin6 t cross join abin7;
以下两句是等价查询：
select * from abin6 t where id=1 or id=2;
select * from abin6 t where t.id=1 union all select * from abin6 s where s.id=2;
一。查找重复记录
1。查找全部重复记录
select * from abin4 s where s.name in (select t.name from abin4 t
group by t.name having count(t.name)>1);
select * from abin4 s where exists (select * from abin4 t where t.name=s.name
group by t.name having count(t.name)>1 );
2。过滤重复记录(只显示一条)
select * from abin4 s where s.id in (select max(id) from abin4 t group by t.name );
二。删除重复记录
1。删除全部重复记录（慎用）
Delete 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)
2。保留一条（这个应该是大多数人所需要的 ^_^）
Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title)
注：此处保留ID最大一条记录
http://blog.csdn.net/csskysea/article/details/6987760