大数据量下not in, not exists, left join的比较

原文：http://blog.csdn.net/feegle_develop/article/details/5861551

/*
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秒（本机测试20秒）

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