NOT IN改为LEFT JOIN或NOT EXISTS的实现

最新推荐文章于 2023-12-14 11:12:22 发布

最新推荐文章于 2023-12-14 11:12:22 发布 · 651 阅读

本文介绍在SQL中如何优化使用NOT IN的情况，通过替换为LEFT JOIN或NOT EXISTS来提高查询性能。这两种方法可以避免索引失效的问题，从而提升数据库操作效率。

在SQL中用NOT IN会影响性能，导致的主要原因就是索引无效，所以最好是将NOT IN改为其他方式实现。

没修改之前的NOT IN写法：

SELECT DISTINCT
	t2.a_id
FROM
	temp_b t2
WHERE
	t2.a_id NOT IN(
		SELECT DISTINCT
			t4.a_id
		FROM
			b AS t3,
			temp_b AS t4
		WHERE
			t4.a_id = t3.a_id
	)

修改为LEFT JOIN写法：

SELECT DISTINCT
	t6.a_id
FROM
	(
		SELECT
			t2.a_id,
			t5.a_id AS tempId
		FROM
			temp_b t2
		LEFT JOIN(
			SELECT DISTINCT
				t4.a_id
			FROM
				b AS t3,
				temp_b AS t4
			WHERE
				t4.a_id = t3.a_id
		)AS t5 ON t2.a_id = t5.a_id
	)AS t6
WHERE
	t6.tempId IS NULL

修改为NOT EXISTS写法：

SELECT DISTINCT
	t2.a_id
FROM
	temp_b t2
WHERE
	NOT EXISTS(
		SELECT
			1
		FROM
			(
				SELECT DISTINCT
					t4.a_id
				FROM
					b AS t3,
					temp_b AS t4
				WHERE
					t4.a_id = t3.a_id
			)AS t5
		WHERE
			t5.a_id = t2.a_id
	)