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

/*

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