sql 子查询和连接查询

chriszg

　　子查询就是查询中又嵌套的查询,嵌套的级数随各数据库厂商的设定而有所不同,一般最大嵌套数不超过15级,实际应用中,一般不要超过2级,否则代码难以理解.一般来说,所有嵌套子查询都可改写为非嵌套的查询,但是这样将导致代码量增大.子查询就如递归函数一样,有时侯使用起来能达到事半功倍之效,只是其执行效率同样较低,有时用自身连接可代替某些子查询,另外,某些相关子查询也可改写成非相关子查询.
　　子查询常用于复杂的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.ID,A.Level,A.desc from table1 A
　　where A.Level in
　　( select B.Level from table2 B where
　　( A.ID = B.ID and B.ID is not null )
　　)