子查询多嵌套一层有什么不一样？

比如我有如下两张表

tb_group

id	name
1	管理员
2	销售

tb_user

id	group_id	age
1	1	18
2	1	20
3	2	20

下列两个语句和结果：

SELECT * FROM tb_group WHERE id IN(
    SELECT group_id FROM tb_user GROUP BY age
)

id	name
1	管理员
2	销售

SELECT * FROM tb_group WHERE id IN(
    SELECT group_id FROM (
        SELECT group_id FROM tb_user GROUP BY age
    ) a
)

id	name
1	管理员

我知道这么写有区别，但不知道原因。明明子查询单独使用的结果都是一样的，但做为in条件就不一样了，有大佬能解释一下吗？

子查询

世界最好语言的追随者

renxiaotu

377 声望

i++

1 人点赞

Rache1

658 声望

最佳答案

你的子查询里面使用了 group by age 而 select 部分仅取了 group_id 字段，实际上你这里 group by 的结果中，group_id 确实存在 1 和 2 两种结果。group_id 现在既不属于被 group by 的字段，自身又没有被聚合函数所使用，所以这会变成一个未定义的情况，即这里的 group_id 的结果可能会不符合预期。

最终构成的 SQL

SQL 1

/* select#1 */ select `test`.`tb_group`.`id` AS `id`, `test`.`tb_group`.`name` AS `name`
               from `test`.`tb_group` semi
                        join (`test`.`tb_user`)
               where (`test`.`tb_user`.`group_id` = `test`.`tb_group`.`id`)

SQL 2

/* select#1 */ select `test`.`tb_group`.`id` AS `id`, `test`.`tb_group`.`name` AS `name`
               from `test`.`tb_group` semi
                        join ((/* select#3 */ select `test`.`tb_user`.`group_id` AS `group_id`
                                              from `test`.`tb_user`
                                              group by `test`.`tb_user`.`age`) `a`)
               where (`a`.`group_id` = `test`.`tb_group`.`id`)

2年前评论

renxiaotu （楼主）

你这个最终构成的sql是在哪里可以看？感觉挺有用的，以前没见过

renxiaotu （楼主）

是否可以粗略理解为，不加嵌套的话其实mysql内部会进行优化不一定是当作子查询处理，只有加了才是强制使用子查询方式禁止解析器进行优化？

Rache1 （作者）

@renxiaotu

你这个最终构成的sql是在哪里可以看？感觉挺有用的，以前没见过

SQL 语句前面加上 explain，然后额外执行一句 show warnings; 就可以看到。

MySQL :: MySQL 8.0 Reference Manual :: 8.8.3 Extended EXPLAIN Output Format

explain select ....; # 查询语句前面加上 explain
show warnings;

是否可以粗略理解为，不加嵌套的话其实mysql内部会进行优化不一定是当作子查询处理，只有加了才是强制使用子查询方式禁止解析器进行优化？

主要是优化器策略的问题了，如果你使用 MySQL 5.7 以后的版本，默认的 SQL_MODE 下，如果你的 select 的列，没有在 group by 后面也没有使用聚合函数的话，就会报错了。

renxiaotu （楼主）

@Rache1 也就是官方认为这样的写法是有误导性的，不明确的，所以5.7以后的版本这句都直接报错了？SELECT group_id FROM tb_user GROUP BY age

Rache1 （作者）

@renxiaotu 准确地说，这种查询方式在 SQL 标准中就是非法的。

参考 MySQL :: MySQL 5.7 Reference Manual :: 12.20.3 MySQL Handling of GROUP BY

MySQL

renxiaotu （楼主）

@Rache1 了解了，感谢大佬

讨论数量: 9

Rache1

658 声望

最终构成的 SQL

SQL 1

/* select#1 */ select `test`.`tb_group`.`id` AS `id`, `test`.`tb_group`.`name` AS `name`
               from `test`.`tb_group` semi
                        join (`test`.`tb_user`)
               where (`test`.`tb_user`.`group_id` = `test`.`tb_group`.`id`)

SQL 2

/* select#1 */ select `test`.`tb_group`.`id` AS `id`, `test`.`tb_group`.`name` AS `name`
               from `test`.`tb_group` semi
                        join ((/* select#3 */ select `test`.`tb_user`.`group_id` AS `group_id`
                                              from `test`.`tb_user`
                                              group by `test`.`tb_user`.`age`) `a`)
               where (`a`.`group_id` = `test`.`tb_group`.`id`)

2年前评论

renxiaotu （楼主）

你这个最终构成的sql是在哪里可以看？感觉挺有用的，以前没见过

renxiaotu （楼主）

是否可以粗略理解为，不加嵌套的话其实mysql内部会进行优化不一定是当作子查询处理，只有加了才是强制使用子查询方式禁止解析器进行优化？

Rache1 （作者）

@renxiaotu

你这个最终构成的sql是在哪里可以看？感觉挺有用的，以前没见过

SQL 语句前面加上 explain，然后额外执行一句 show warnings; 就可以看到。

MySQL :: MySQL 8.0 Reference Manual :: 8.8.3 Extended EXPLAIN Output Format

explain select ....; # 查询语句前面加上 explain
show warnings;

是否可以粗略理解为，不加嵌套的话其实mysql内部会进行优化不一定是当作子查询处理，只有加了才是强制使用子查询方式禁止解析器进行优化？

renxiaotu （楼主）

@Rache1 也就是官方认为这样的写法是有误导性的，不明确的，所以5.7以后的版本这句都直接报错了？SELECT group_id FROM tb_user GROUP BY age

Rache1 （作者）

@renxiaotu 准确地说，这种查询方式在 SQL 标准中就是非法的。

参考 MySQL :: MySQL 5.7 Reference Manual :: 12.20.3 MySQL Handling of GROUP BY

MySQL

renxiaotu （楼主）

@Rache1 了解了，感谢大佬

php-wzb

0 声望

我的就可以成功
file
file

2年前评论

renxiaotu （楼主）

我的语句里select的字段不是group by的字段，你的是同一个，情况不一样

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容，与人为善，比聪明更重要！

帮助

子查询多嵌套一层有什么不一样？

最终构成的 SQL

SQL 1

SQL 2

最终构成的 SQL

SQL 1

SQL 2

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

子查询多嵌套一层有什么不一样？

最终构成的 SQL

SQL 1

SQL 2

最终构成的 SQL

SQL 1

SQL 2

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

请登录