sql数据库开发-8：多表查询时，常用的子查询

多表查询时，常用的子查询

 

非关联子查询

     子查询的应用场景

    问题

      --哪些os账户的开通时间是最早的。

    核心点

      --如果能获得最早的开通时间，根据该结果很容易得到哪些os账号

    解决方法

      -- 使用子查询

 

子查询

    子查询就是在一条SQL语句中嵌入SELECT语句

子查询语法：

    select colname1,... ... from tabname where expr operator (select colname2 from subtabname);

 

 

子查询的执行过程

      先执行子查询，子查询的返回结果作为主查询的条件，再执行主查询。

      子查询只执行一遍。

      如子查询的返回结果为多个值，ORACLE会去掉重复之后，再将结果返回给主查询。

 

项目案例：

  哪些os账号的开通时间是最早的。

项目案例：

  哪些os账号的开通时间比unix服务器192.168.0.26上的huangr晚

项目案例：

   哪些os账号的开通时间比huangr晚。()多台unix服务器上都有名为huangr的os账号）

 

 

子查询常见错误

    单行子查询返回多条记录。

  select colname1,... ... from tabname where expr = (select colname1 from subtabname
 where condition1);

 ORA-01427:single-row subquery returns more than one row

 

 

子查询的注意事项

   比较运算符的选择

   若子查询的返回结果仅为一个值，可以用单值运算符 如 = 等

   若子查询的返回结果可能为多值，必须用多值运算符如 IN 等。

 

 

项目案例:

   哪些客户是推荐人
 
   哪些客户不是推荐人

 

子查询与空值

  若子查询的返回结果中包含空值(NULL) ,并且运算符为 NOT IN,那么整个查询不会返回任何行。NOT IN 等价于<>ALL,任何值跟NULL比(包括NULL本身)，结果都不为true

 
  select colname from tabname where colname not in (select colname from subtabname);
 

   no rows selected .

 

 

多列子查询

     where 子句后面可以跟多列条件表达式

   select colname1,... ...  from tabname where (colname1,colname2) IN (select colname1,AVG(colname2) from subtabname group by colname1);

 

 

项目案例：

   哪些os账号的开通时间是所在unix服务器上最早的？(每台unix服务器上最早开通的os账号)
。

   哪些os账号的开通时间比所在的unix服务器上最早的开通时间晚九天。

   哪些os账号的开通天数等于同一台unix服务器上的平均开通天数。

 

 

 

关联子查询

   关联子查询的应用场景

 
   问题
 
      --  哪些os账号的开通天数比同一台unix服务器上的平均开通天数长。

   核心点

      --  该问题的难点是同一台unix服务器的平均开通天数，如何表达同一台unix服务器是解决该问题的核心。

   解决方法

      --  使用关联子查询      

 

 

 

关联子查询

     关联子查询的语法形式

      select colname1,colname2,... from table1 o where colname1 orerator (select colname1,colname2 from table2 i where i.expr1 = o.expr2);

 

 

 

项目案例:

     哪些os账号的开通天数比同一台unix服务器上的平均开通天数长 。

 

 

关联子查询的执行过程

   关联子查询采用的是循环 （loop）的方式，执行步骤如下:

 
 
1.外部查询 得到一条记录(查询先从outer表中读取数据) 并将其传入到内部查询；

2.内部查询基于传入的值执行；

3.内部查询从其结果中包值传回到外部查询，外部查询使用这些值来完成其处理，如符合条件，outer表中得到的那条记录就放入结果集中，否则放弃，该记录不符合条件。

4.重复执行步骤1-3， 直到把outer表中的所有记录判断一遍。

 

项目案例:

    哪些客户是推荐人
 
        

 

EXISTS的执行过程

    EXISTS采用的是循环(loop) 方式,判断outer表中是否存在记录只要在inner表中找到一条匹配的记录即可。

1.外部查询得到一条记录(查询先从outer表中读取数据)并将其传入到内部查询的表。

2.对inner表中的记录依次扫描，若根据条件存在一条记录与outer表中的记录匹配，立即停止扫描，返回true,将outer表中的记录放入结果集中，若扫描了全部记录，没有任何一条记录符合匹配条件，返回false,outer表中的该记录被过滤掉，不能出现在结果集中。

3.重复执行步骤1-2，直到把outer表中的所有记录判断一遍。

 

 

 

项目案例：

   哪些客户申请了远程登录业务

 

 

NOT EXISTS 的执行过程

NOT EXISTS采用的是循环(loop) 方式，判断在outer表中是否不存在记录，它能在inner表中能找到匹配的记录。

 

1.外部查询得到一条记录(查询先从outer表中读取数据)并将其传入到内部查询的表。

2.对inner表中的记录依次扫描，若根据条件存在一条记录与outer表中的记录匹配，立即停止扫描，返回false,outer表中的该记录被过滤掉，不能出现在结果集中，若扫描了全部记录，没有任何一条记录符合匹配条件，返回true,将outer表中的记录放入结果集中。

3.重复执行步骤1-2,直到把outer表中的所有记录判断一遍。

 

 

项目案例：

    哪些客户不是推荐人

    哪些客户没有申请远程登录业务

 

 

IN和EXISTS的比较

   EXISTS是用循环(loop)的方式，由outer表的记录数决定循环的次数，对于exists影响最大，所以，外部的记录数要少。

   IN先执行子查询，子查询的返回结果去重之后，再执行主查询，所以，子查询的返回结果越少，越适合用该方式