子查询之组合查询

概述

Ø  概念：子查询返回的值，与父查询的每一行的值都相关。这种子查询叫做~。

Ø  判断相关子查询的方法：子查询不能脱离父查询而独立运行。

Ø  理解的要点：父查询返回N行，子查询就分别执行N次。

 

计算列子查询

示例：查询各班有多少人。

select c_id,c_name,

(select count(*) from t_stu s where s.c_id=c.c_id) as 人数

from t_class c;

其中：

父查询是：select c_id,c_name from t_class c

子查询是：select count(*) from t_stu s where s.c_id=c.c_id

 

父查询返回：

c_id   c_name

1   一班

2   二班

3   三班

父查询返回3行，子查询就必须分别执行3次：

c_id   c_name

1   一班  select count(*) from t_stu s where s.c_id=1  返回4

2   二班  select count(*) from t_stu s where s.c_id=2  返回2

3   三班  select count(*) from t_stu s where s.c_id=3  返回0

可以看到，子查询的返回值(4,2,0)分别与父查询的值(1,2,3)是息息相关的。即：

select c_id,c_name,

(select count(*) from t_stu s where s.c_id=c.c_id) as 人数

from t_class c;

返回：

c_id c_name 人数

1   一班    4

2   二班    2

3   三班    0

 

exists子查询

查询哪些班上没有人，最简单的方法是：

select * from t_class c

where c_id not in (select distinct c_id from t_stu s)

它的意思是如果一个班级编号，不在学生表的班级编号中，就证明这个班上没有学生。

但是运行效率很低，其中之一就是not in用不到索引，所以在实际工作中，这种查询“不存在”的情况，往往要用not exists来代替。“not exists与not in”的区别必须掌握，这也是面试常问的问题之一。

用not exists替代的代码是：

select * from t_class c

where not exists

(select * from t_stu s where s.c_id=c.c_id)         

注意：使用exists是唯一where后面不需要指定字段的情况。

理解的要点仍然是：父查询返回N行，子查询就分别执行N次。

父查询“select * from t_class c”返回：

c_id   c_name

1   一班

2   二班

3   三班

父查询返回3行，子查询分别执行3次：

c_id   c_name

1   一班  select * c_id from t_stu s where s.c_id=1

2   二班  select * c_id from t_stu s where s.c_id=2

3   三班  select * c_id from t_stu s where s.c_id=3

执行“select * c_id from t_stu s where s.c_id=1”，返回4行数据。这证明exists为true，而not exists为false。所以1班有人，而要的是没有人的班级，即1班不满足条件，不显示。

同理：执行“select * c_id from t_stu s where s.c_id=2”，也不满足条件，不显示。

当执行“select * c_id from t_stu s where s.c_id=3”时，返回0行数据，这证明满足not exists的条件，所以3班就显示出来了。

 

重点，exists的高效的原因之一：

子查询只要找到任何一条满足条件的数据，即可证明exists为true，不会查询所有满足条件的记录，这大大提高的查询的效率。

例如：上面1班有4行数据，但exists只会找到任何一个1班学生，就证明1班不满足条件了，马上就会接着判断下一个班。

exists的高效的原因之二：

子查询返回的数据没有任何限制，因为只要证明有数据返回就可以了，而无须关心是什么数据。所以上面的查询经常也写为：

select * from t_class c

where not exists

(select ‘x’  from t_stu s where s.c_id=c.c_id)

注意：子查询返回的数据不再是“*”，而是“x”。

   这表示，如果有一行数据返回，则必有一个“x”返回，这是计算列的相关知识。用“x”代表分配char(1)，是最节约内存的做法。

exists的高效的原因之三：exists子查询用的是相等比较，如“where s.c_id=c.c_id”，如果在子查询上建立相应的索引(如t_stu表的c_id字段有创建索引，索引名称任意)，则用自动用到该索引，从而大大提高效率。