/*
IN、EXISTS和LEFT JOIN，NOT IN、NOT EXISTS和INNER JOIN在存在与不存在的查询效率
*/
IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
GO
CREATE TABLE A
(
ID INT
)
GO
IF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
GO
CREATE TABLE B
(
ID INT
)
GO
DECLARE @ID INT,@IDD INT
SET @ID=1
SET @IDD=2
WHILE @ID<1000000
BEGIN
INSERT INTO A VALUES (@ID)
INSERT INTO B VALUES (@IDD)
SET @ID=@ID+1
SET @IDD=@IDD+2
END
GO
--总结：表A中的数据在表B中存在的效率比较 INNER JOIN = EXISTS = IN
SELECT *
FROM A
WHERE ID IN (SELECT ID FROM B)--4秒
GO
SELECT *
FROM A
WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.ID)--4秒
GO
SELECT A.ID,B.ID
FROM A
INNER JOIN B
ON A.ID=B.ID--4秒
GO
--总结：表A中的数据在表B中不存在的效率比较 LEFT JOIN > NOT EXISTS > NOT IN
SELECT *
FROM A
WHERE ID NOT IN (SELECT ID FROM B)--45秒
GO
SELECT *
FROM A
WHERE NOT EXISTS (SELECT * FROM B WHERE A.ID=B.ID)--4秒
GO
SELECT A.ID,B.ID
FROM A
LEFT JOIN B
ON A.ID=B.ID
WHERE B.ID IS NULL--3秒
GO
1477
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
