SQL Server in和exists区别

数据准备,比较in和exists的效率

注意:本文章只是针对sql server和mysql

-- 1,准备表和存储过程
CREATE TABLE [aaa] (
	[id] INT NULL ,
	[username] VARCHAR(200) NULL ,
	[pwd] VARCHAR(200) NULL ,
	[content] VARCHAR(2000) NULL 
)
--mysql
DROP TABLE IF EXISTS `aaa`;
CREATE TABLE `aaa` (
  `id` INT(11) NOT NULL,
  `username` VARCHAR(255) DEFAULT NULL,
  `pwd` VARCHAR(255) DEFAULT NULL,
  `content` VARCHAR(255) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=latin1;
CREATE TABLE [bbb] (
	[id] INT NULL ,
	[username] VARCHAR(200) NULL ,
	[pwd] VARCHAR(200) NULL ,
	[content] VARCHAR(2000) NULL 
)
--mysql
DROP TABLE IF EXISTS `bbb`;
CREATE TABLE `bbb` (
  `id` INT(11) NOT NULL,
  `username` VARCHAR(255) DEFAULT NULL,
  `pwd` VARCHAR(255) DEFAULT NULL,
  `content` VARCHAR(255) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=latin1;

create PROCEDURE [dbo].[pro_create_bbb_500001_1500000]
AS
BEGIN
DECLARE 
@id INT,
@username VARCHAR(50),
@pwd VARCHAR(50),
@content VARCHAR(50),
@i INT
SET @id=0
SET @username ='Bob'
SET @pwd='888888'
SET @content='Bob is a good boy'
SET @i=500001
WHILE @i<=1500000
	BEGIN
	print @i
	INSERT INTO bbb ( id, username, pwd, content )
	VALUES  ( 	@i, 
				@username,
				@pwd, 
				@content )
	SET @i=@i+1
	END
END

create PROCEDURE [dbo].[pro_create_aaa_1_1000000]
AS
BEGIN
DECLARE 
@id INT,
@username VARCHAR(50),
@pwd VARCHAR(50),
@content VARCHAR(50),
@i INT
SET @id=0
SET @username ='Alice'
SET @pwd='123456'
SET @content='Alice今天做了一件事情'
SET @i=1
WHILE @i<=1000000
	BEGIN
	print @i
	INSERT INTO aaa ( id, username, pwd, content )
	VALUES  ( 	@i, 
				@username,
				@pwd, 
				@content)
	SET @i=@i+1
	END
END

-- 2,执行proc
exec pro_create_bbb_500001_1500000; --108s

exec pro_create_aaa_1_1000000; --108s

交集是50w-100w

效果如图:
在这里插入图片描述
在这里插入图片描述

select * from aaa where id not in(select id from bbb) 
--大约100s,查出500000条数据
select * from aaa where  not  EXISTS(select id from bbb  where aaa.id =bbb.id) 
--仅仅3s,查出500000条数据

EXISTS的好处

--创建两个表,插入初始值
create table test1 (id1 int);
create table test2 (id2 int);

insert into test1 (id1) values (1),(2),(3);
insert into test2 (id2) values (1),(2);

select id1 from test1 where id1 in (select id2 from test2);--这是在test1表中查询test2表存在的值

select id1 from test1 where id1 in (select id1 from test2);--这是故意将子查询写成id1,但是居然不报错,还查出来了

insert into test2 (id2) values (NULL);--给test2插入一个空值

select id1 from test1 where id1 not in (select id2 from test2);
--查询,在test2中不存在的  test1中的id 。结果是空白!显然这个结果不是我们想要的。我们想要3。为什么会这样呢?
--原因是:NULL不等于任何非空的值啊!如果id2只有1和2, 那么3<>1 且 3<>2 所以3输出了,但是 id2包含空值,那么 3也不等于NULL 所以它不会输出。
--???我还是不懂

--解决办法1、用 EXISTS 或 NOT EXISTS 代替
select * from test1 
where EXISTS (select * from test2  where id2 = id1 );

select * from test1  
where NOT EXISTS (select * from test2  where id2 = id1 );

--解决办法2、用JOIN 代替
select id1 from test1 
INNER JOIN test2 ON id2 = id1; 
   
select id1 from test1 
LEFT JOIN test2 ON id2 = id1 
where id2 IS NULL;

--PS:那我们死活都不能用 in 和 not in了么?并没有,如果是确定且有限的集合时,可以使用。如 in(0,1,2)

--拓展文章
https://blog.csdn.net/run65536/article/details/80679313
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页