EXIT 和 IN 的区别

  对于sql中的EXIT和IN，他们有时候查询出来的结果可能是一致的，例如下面这两条语句：

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)；

SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc = A.cc)；

  此时他们的区别就在于，IN是在A中选择一条记录，然后在B中查找该记录是否存在。EXISTS是在B中选择一条符合条件的记录，然后在A中查找该记录是否存在。

  因此，当我们需要提高sql查询效率的时候，在对cc列建立索引后，当A表小的话就用EXISTS，当B表小的话就用IN。 原理就是尽量用小表作为驱动表，这样搜索效率更高。

  下面是我针对EXIT和IN练习的几条sql语句，查询的结果都是一致的。

SELECT player_id, player_name, team_id 
FROM player 
WHERE
	player_id = ( 
	SELECT player_id 
	FROM player_score 
	WHERE score > 20 AND player_score.player_id = player.player_id );

SELECT player_id,player_name,team_id 
FROM player 
WHERE
	EXISTS ( 
	SELECT player_id 
	FROM player_score 
	WHERE score > 20 AND player_score.player_id = player.player_id );

SELECT player_id, player_name, team_id
FROM player
WHERE player_id IN (SELECT player_id FROM player_score WHERE score > 20);

SELECT player.player_id, player_name, team_id
FROM player JOIN player_score
WHERE player.player_id = player_score.player_id AND score > 20;

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 ) 
	);