极客时间-轻松学习，高效学习-极客邦

看，有只猪

2019-07-01

IN表是外边和内表进行hash连接，是先执行子查询。
EXISTS是对外表进行循环，然后在内表进行查询。
因此如果外表数据量大，则用IN，如果外表数据量小，也用EXISTS。
IN有一个缺陷是不能判断NULL，因此如果字段存在NULL值，则会出现返回，因为最好使用NOT EXISTS。

 4

 69
Sam

2019-07-01

not in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次；
not exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次。



 21
humor

2019-07-03

exist和in的区别这一块，感觉没有讲清楚呀，我听下来还是不太明白哪种情况应该用in，哪种情况应该用exists。选择的标准是看能否使用表的索引吗？

作者回复: 索引是个前提，其实和选择与否还是要看表的大小。选择的标准，你可以理解为：小表驱动大表。这种方式下效率是最高的。比如
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)
当A小于B时，用EXIST。因为EXIST的实现，相当于外表循环，实现的逻辑类似于：
for i in A
    for j in B
        if j.cc == i.cc then ...

当B小于A时，用IN，因为实现的逻辑类似于：
for i in B
    for j in A
        if j.cc == i.cc then ...
所以哪个表小就用哪个表来驱动，A表小就用EXIST，B表小就用IN

 10

 20
Hero

2019-07-01

1.    SELECT
    player_id,
    team_id,
    player_name
FROM
    player a
WHERE
    EXISTS ( SELECT b.player_id FROM player_score b GROUP BY b.player_id HAVING AVG( b.score ) > 20 and a.player_id = b.player_id);
2.SELECT
    player_id,
    team_id,
    player_name
FROM
    player a
WHERE
    EXISTS ( SELECT b.player_id FROM player_score b WHERE a.player_id = b.player_id GROUP BY b.player_id HAVING AVG( b.score ) > 20);
3.SELECT
    player_id,
    team_id,
    player_name
FROM
    player
WHERE
    player_id IN ( SELECT player_id FROM player_score GROUP BY player_id HAVING AVG( score ) > 20 );
推荐3，因为子查询只会执行一次。2比1好，因为where会先过滤数据行，然后分组，然后对分组过滤。

展开

作者回复: Good Job 总结的不错

 3

 11
Serendipity

2019-09-01

我怎么感觉简单的地方讲的比较多，难的地方一笔带过了？看的好懵逼。

作者回复: 后面有些内容是和SQL优化相关的，前面相对基础

 3

 8
悟空

2019-07-01

# 使用exists
SELECT player_name,player_id,team_id from player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id and score >20)

# 使用 in
SELECT player_name,player_id,team_id from player WHERE player_id IN (SELECT player_id FROM player_score WHERE score >20)

使用：join 语句
SELECT p.player_name,p.player_id,p.team_id,t.score from player AS p JOIN player_score as t WHERE p.player_id = t.player_id and t.score >20

展开



 8
华夏

2019-07-01

SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id);
老师，文稿中这句的WHERE player.player_id = player_score.player_id可以不要了哈。



 5
Liam

2019-07-01

老师您好，关于exist和in的差别，总结和举例的不符吧？主表大于从表不应该是使用in更好吗

 3

 5
Mr.H

2019-07-01

最后的总结in和exist写反了吧



 5
hsj

2019-09-29

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)
A 表有 n 条数据，B 表有 m 条数据
两条语句的执行效率（假设都用 btree 索引）
用 in ：m * log (n)
用 exists： n * log (m)
所以小表驱动大表能够大幅度提高执行速度

展开

作者回复: 总结的不错！

 1

 4
小星星

2019-07-09

SELECT player_name, height, team_id FROM player AS a
WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id);

SELECT player_name, height, team_id FROM player WHERE height > (SELECT avg(height) FROM player as b);
这两句执行的结果一样的；请问为什么需要加上a.team_id = b.team_id 有什么区别吗？

作者回复: 第一个SQL 18条记录
第二个SQL 19条记录

 1

 4
OperaX

2019-07-07

SELECT
    t1.player_id,
    t1.player_name,
    t1.team_id
FROM
    player t1
WHERE
    t1.player_id IN (
        SELECT
            t2.player_id
        FROM
            player_score t2
        GROUP BY
            t2.player_id
        HAVING
        ( AVG( t2.score ) > 20 )
    )

展开

作者回复: 正确，而且SQL代码结构清晰



 4
OperaX

2019-07-07

实际工作中没用到果any all。什么样的情况下会可能用到呢？老师举的例子的话，直接比较最大值或者最小值就好了吧



 4
华夏

2019-07-01

SELECT player_id, player_name, team_id FROM player AS a WHERE (SELECT score FROM player_score AS b WHERE a.player_id = b.player_id) > 20;
+-----------+------------------+---------+
| player_id | player_name | team_id |
+-----------+------------------+---------+
| 10001 | 韦恩-艾灵顿 | 1001 |
| 10002 | 雷吉-杰克逊 | 1001 |
+-----------+------------------+---------+
2 rows in set (0.01 sec)

展开



 3
张驰皓

2019-12-21

SELECT player_id, team_id, player_name
FROM player
WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

这段代码里，子查询里 where 部分删去后运行结果与此前一致，是否说明此 where 语句是无意义的？

作者回复: 是因为这些player在player_score中都有记录，player这张表的数据量有些少，如果数据量大的话，没有在player_score（即没有打过比赛的球员）是不会显示出来的



 2
峻铭

2019-09-03

前提条件：
1、我理解的场均得分大于20，第一场得了40分，第二场得了2分，场均为21分满足条件
2、一场比赛中，球员可以出现多次
解析思路，先得出球员在每场比赛中的总分数，然后对球员分组，计算球员在所参加比赛中的平均数
SELECT
    t2.player_id,
    t2.player_name,
    t2.team_id,
    t3.v
FROM
    player AS t2
    JOIN (
    SELECT
        t1.player_id,
        avg( t1.total ) AS v
    FROM
        (
        SELECT
            player_id,
            sum( score ) AS total
        FROM
            player_score
        WHERE
            game_id IN ( SELECT game_id FROM player_score GROUP BY game_id )
        GROUP BY
            player_id #对球员分组，计算在每场比赛中的总分数
        ) AS t1
    GROUP BY
        t1.player_id #对球员分组，计算所参加比赛的平均数
    HAVING
    v > 20 #场均得分大于20
    ) AS t3 ON t2.player_id = t3.player_id;

+-----------+------------------+---------+---------+
| player_id | player_name | team_id | v |
+-----------+------------------+---------+---------+
| 10001 | 韦恩-艾灵顿 | 1001 | 26.0000 |
| 10002 | 雷吉-杰克逊 | 1001 | 22.0000 |
+-----------+------------------+---------+---------+

展开

作者回复: 整理和分析的不错

 2

 2
苏籍

2019-07-02

WHERE a.team_id = b.team_id
想确定一下这个条件是在两个相同的表做笛卡尔积后做了啥操作，能帮梳理一下思路吗

 1

 2
Fred

2019-07-01

SELECT player_id , player_name, team_id FROM player AS a WHERE a.player_id IN (SELECT player_id FROM play_score AS b WHERE a.player_id=b.player_id AND AVG(b.score)>20)



 2
太精

2019-07-01

SELECT player_id, player_name, team_id FROM player WHERE player_id IN (SELECT player_id FROM player_score WHERE score > 20);
+-----------+------------------+---------+
| player_id | player_name | team_id |
+-----------+------------------+---------+
| 10001 | 韦恩-艾灵顿 | 1001 |
| 10002 | 雷吉-杰克逊 | 1001 |
+-----------+------------------+---------+

展开



 2
Cary

2019-07-01

in 和 exist 上面和最后总结的好像不一致



 2