mysql 子查询

子查询（subquery），查询其结果用来作为另一个查询的参数。

 

单值的子查询

一个子查询如果产生一个单纯的数据，该子查询就如同一个常量，那么就可以像使用常量一样使用它。在实际应用中，我们经常要求子查询只返回一个值，这样就可以将一列值和单个子查询返回值进行比较，这时，可以使用等于（=）、不等于（<>）、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）等运算符。

SELECT * FROM user
where dept_id  = (select id from dept where dept_name = 'IT')

在子查询中使用聚合函数

SELECT * FROM user
where salary  > (select avg(salary) from user)

比较判式的两边均采用聚合分析的子查询

SELECT * FROM user
where (select avg(salary) from user as user2 where user2.dept_id = user.dept_id )  > (select avg(salary) from user)

在SELECT子句中使用子查询

SELECT *,
(select count(1) from user where `user`.dept_id = dept.id) as user_count
FROM dept 

　　

多行的子查询

一个子查询除了可以产生一个单一值外，也可以产生一个关系，该关系可以包含若干元组。SQL提供了若干对于关系的操作符，并产生一个布尔型的结果，这些操作符主要用在子查询的结果关系上，它主要包括：IN、EXISTS、SOME（ANY）、ALL、UNIQUE等。

采用IN子查询实现集合交运算

当使用IN运算符来引入子查询时，就是告诉DBMS执行子查询集合成员测试，即把源表中的列值与子查询的返回结果进行比较，如果列值包与返回结果集中的列数据值之一相匹配，那么IN判别式求值为True，查询结果就包含这行数据。

SELECT * FROM user
where id in (select id from user where dept_id = 1)

采用IN子查询实现集合差运算

SELECT * FROM user
where id not in (select id from user where dept_id = 1)

EXISTS子查询

在某些情况下，我们只需要子查询返回一个True或者False，子查询数据内容本身并不重要，这时，可使用EXISTS判式来定义子查询。EXISTS判式用来测试集合是否为空，它总是与子查询结合使用，而且只要子查询中至少返回一个值，EXISTS判式的值就为True。如果子查询的结果表中没有值（表中没有行满足子查询的WHERE子句的搜索条件），那么EXISTS判式的值为False。

SELECT * FROM user
where exists (select * from dept where id = user.dept_id)

SELECT * FROM user
where NOT exists (select * from dept where id = user.dept_id)

SOME/ALL子查询

只要我们使用了SQL比较运算符（等于=、不等于<>、大于>、小于<、大于等于>=、小于等于<=）中的一个来比较两个表达式的值，那么运算符前后的表达式都必须为单一值。只有当子查询返回值为单值时，才可使用子查询作为比较判式的表达式之一。而数量词SOME、ANY和ALL则允许使用比较运算符将单值与子查询返回的值加以比较，这里的子查询返回的结果可以是多行的。

SELECT * FROM user
where salary  >= ALL (select avg(salary) from user)

UNIQUE子查询

UNIQUE运算符用来测试集合是否存在重复元组。与EXISTS判式相似，它总是与子查询结合使用，而且只要子查询结果中没有重复记录，UNIQUE判式的值就为True；如果子查询的结果表中有重复的记录，那么UNIQUE判式的值为False。

SELECT * FROM user
where UNIQUE (select dept_id from dept where id = user.dept_id)  ×

myslq没有提供对UNIQUE判式的支持

可以在子查询中采用聚合函数实现上述查询

SELECT * FROM user
where  (select count(1) from dept where id = user.dept_id) = 1

  

相关子查询

由前面介绍的一些子查询我们不难发现，有些复杂的子查询需要执行若干次，因为每次执行时，都需要来自子查询外部的元组变量的值，也就是说，子查询的执行要依赖于上一层查询元组的当前值，我们将这种子查询称之为相关子查询。前面介绍的EXISTS子查询，基本上全部是相关子查询。

在HAVING子句中使用相关子查询

在SQL中，DBMS使用WHERE子句中的搜索条件来过滤查询结果表中不想要的行，使用HAVING子句中的搜索条件删除那些不想要的。

SELECT * FROM user
HAVING  salary  > (select avg(salary) from user)

　　

嵌套子查询

子查询也可以出现在其他子查询中。位于其他子查询内的子查询被称为嵌套的子查询

SELECT * FROM user
where salary  > (select avg(salary) from user where dept_id in (select id from user where dept_id = 1)  )

　　

使用子查询创建视图

使用子查询，根据已有的表或者视图创建出新的视图。

create view view_user_s as
SELECT * FROM user
where salary  > (select avg(salary) from user )

　　

附:示例sql

CREATE TABLE `dept` (
  `id` int(11) DEFAULT NULL,
  `dept_name` varchar(255) DEFAULT NULL
) ;

CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL
) ;

INSERT INTO `test`.`dept` (`id`, `dept_name`) VALUES ('1', 'IT');
INSERT INTO `test`.`dept` (`id`, `dept_name`) VALUES ('2', 'HR');
INSERT INTO `test`.`user` (`id`, `dept_id`, `user_name`, `salary`) VALUES ('1', '1', '1a', '100');
INSERT INTO `test`.`user` (`id`, `dept_id`, `user_name`, `salary`) VALUES ('2', '1', '1b', '401');
INSERT INTO `test`.`user` (`id`, `dept_id`, `user_name`, `salary`) VALUES ('3', '2', '2a', '300');
INSERT INTO `test`.`user` (`id`, `dept_id`, `user_name`, `salary`) VALUES ('4', '2', '2b', '200');
INSERT INTO `test`.`user` (`id`, `dept_id`, `user_name`, `salary`) VALUES ('5', '3', '3a', '500');

　　

 

参考:SQL查询的艺术-第10章 子查询