子查询常用于复杂的SQL操作中,包括select,insert,delete,update等语句中都可嵌套子查询.子查询分为两种:相关子查询和不相关子查询,顾名思义,相关子查询不能独自运行,必须依赖于外部父查询提供某些值才能运行.
子查询可以返回:结果集,逻辑值.仅当使用exists子句时,子查询才不返回结果集,而只返回true或false这样的逻辑值.可用于子查询的关键字有:IN,ANY(任一),ALL等.具体的说,子查询可返回单值,元组(即两个或两个以上值),及多值结果集.
下面举一些例子来说明子查询的使用:
1. 假设数据表emp中有empID和empName两个字段,其中empID(主键)有重复,现在要求删除表中empID值有重复的记录,并且必须保留一条empID值重复的记录.
解决思路:用相关子查询和自身连接即可,下面是实现的SQL语句
Delete from emp e1 where empID in ( select empID from emp where empID = e1.empID and empName != e1.empName )2. 用子查询来更新数据库表.假设数据表salary中有2个字段empID和salaryAmount,数据表emp中有2个字段empID和Age,请用emp表中的Age字段来代替salary表中的salaryAmount字段的值,下面是实现的SQL语句
Update salary S Set salaryAmount = ( select Age from emp where empID = S.empID )
说明:在(相关)子查询中也可使用未出现在父查询中的字段值,如上例中的empID字段
3. 连接:连接分为4种,分别是内连接(inter join),外连接(outer join,分为左,右和全外连接三种),交叉连接(cross join)和自身连接(self join).外连接的关键字和标识符有:left,right,full,(+),*等.当为(+)标识时,则靠近(+)的表为副表,而远离(+)的表则为主表(这依赖于数据库实现,在Oracle中是这样规定的 )
4. 假设有客户表customer,其中字段有custID,custName,Addr,客户定货表orders,其中字段有 orderID,custID,orderDate,procID.请查询出在2000年1月1日以后有订货的客户及(没有订货的)所有客户
Select custID,custName,Addr,orderDate from customer left join orders on
(customer.custID = orders.custID) and ( orderDate >'2000-1-1')说明:左外连接,则左边的表为主表,在本例中,表customer即为主表;右外连接,则右边的表即为主表. 若不用坐外连接,则2000年1月1日前未订货的客户则查询不出来.一般来说,如果要使查询中不遗漏任何记录,则只有用全外连接(full outer join)才行.
5. 查询的6个关键字有:select,from,where,group by,having,order by,以上是它们在查询语句中一般应出现的顺序.having与group by的关系就犹如where 与select的关系一样,都是用于限定哪些行被选中.以下是一些注意事项:
- 子查询中不能排序,即不能有order by子句
- order by子句中的字段可以不出现在select子句后的字段列表中,但但当有distinct限定词时,order by中的字段必须出现在select子句中.
- 子查询可以嵌套,并且是从里往外开始执行
6.假设有学生表student,其中有字段sno,sname,birthday,课程表course,其中有字段cno,cname,ccent,学生选课表sc,其中有字段cno,sno,grade
- 请查询出未选修任何课的学生
select sno,sname from student S where not exists
( select sno from sc where cno in
( select cno from course where cno = sc.cno and sc.sno = s.sno )
)- 查询出未被任何学生选修的课程号及课程名
select cno,cname from course where cno not in ( select cno from sc )- 删除未被任何学生选修的课程
delete from course where cno in ( select cno from course where cno not in
( select cno from sc )
)
7.并union,交intersect,差minus运算
- 请查询出同时选修了2门以上课程的学生名单,如英语和汉语
select * from student where sno in
( select sno from sc
where cno in
( select cno from course where cname ='英语' )
)
union
select * from student where sno in
( select sno from sc
where cno in
( select cno from course where cname ='汉语' )
)- 查询出选修了英语而未选修汉语的学生名单
select * from student where sno in
( select sno from sc
where cno in
( select cno from course where cname ='英语' )
)
minus
select * from student where sno in
( select sno from sc
where cno in
( select cno from course where cname ='汉语' )
)- 查询出既选修了英语又选修了汉语的学生名单
select * from student where sno in
( select sno from sc
where cno in
( select cno from course where cname ='英语' )
)
intersect
select * from student where sno in
( select sno from sc
where cno in
( select cno from course where cname ='汉语' )
)8.删除或修改子句不能加join条件,如:delete from table1 where condition1 and condition2 类似这样的语句不合法,即condition1条件后不能有condition2或更多的条件,解决的办法可以用子查询,如:
delete from table1 where ( ... ),同样,对update 子句也类似9.内连接是等值连接,所以当两个表间不匹配时,很容易遗漏数据,解决的办法是用外连接,但无论是左外连接还是右外连接,在理论上都只能使遗漏的数据少些,只有全外连接才能保证不遗漏数据.
需要注意NULL(空值)对(子)查询的影响,比如下例,如要查询出A和B表中存在相同ID的等级时,就必须限定ID不为NULL,否则子查询返回空结果集:
select ,A.Level,A.desc from table1 A
where A.Level in
( select B.Level from table2 B where
( = and is not null )
)
