子查询与连接使用指南

 子查询就是查询中又嵌套的查询,嵌套的级数随各数据库厂商的设定而有所不同,一般最大嵌套数不超过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 )

        )