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

-- 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,
@pwd VARCHAR(50),
@content VARCHAR(50),
@i INT
SET @id=0
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,
@pwd,
@content )
SET @i=@i+1
END
END

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

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

exec pro_create_aaa_1_1000000; --108s



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

• 点赞
• 评论
• 分享
x

海报分享

扫一扫，分享海报

• 收藏
• 手机看

分享到微信朋友圈

x

扫一扫，手机阅读

• 打赏

打赏

vayneXiao

你的鼓励将是我创作的最大动力

C币 余额
2C币 4C币 6C币 10C币 20C币 50C币
• 一键三连

点赞Mark关注该博主, 随时了解TA的最新博文
12-13 146

12-11 87
04-03 11万+
07-15
12-01 218
05-18
03-14 521
08-08 175
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客