Mysql中in, exists与or关键字的区别

Mysql中in, exists与or关键字的区别

写法上的区别：

假设有表A，A表中有字段name。name的值可能为"x"，"y"，"z"。现要找出A表中name为"y"，"z"的记录：

or的写法：

select * from A where name="y" or name="z"

in的写法：

select * from A where name in ("y", "z")

 

in，or执行效率大比拼：

如果字段name是主键或者有索引的话，那上述or和in两条SQL语句的执行效率是差不多的。

否则的话，随着or的个数越多，执行效率下降得就越利害。当然在表中记录不多的情况下，影响也不会太大。

但or越多写着也越麻烦不是，而且还不容易维护。

select * from A where A.name in (select B.name from B)

等价于

1. select B.name from B
2. select * from A

 

exists：

要使用exists，我们再来新建一个B表。B表中也有字段name。name的值可能为"w"，"x"，"y"（注意A表可能值为"x"，"y"，"z"）。现要找出A表中name与B表中name相同的记录：

exists的写法：

SELECT * FROM A WHERE EXISTS (SELECT B.name FROM b WHERE a.name = b.name)

等价于

1. SELECT * FROM A
2. SELECT B.name FROM b WHERE a.name = b.name

 

in，exists执行原理介绍：

先来看个例子，下面代码1和代码2哪个的执行效率高（只是把循环次数的位置调换了一下）：

代码1：

for(i=1; i<100; i++）

{

    for(j=1; j<10000; j++）

    {

        call fun(); // 调用函数

    }

}

代码2：

for(i=1; i<10000; i++）

{

    for(j=1; j<100; j++）

    {

        call fun(); // 调用函数

    }

}

都执行相同的循环次数。难道效率不是一样的吗？其实不然，代码1要优于代码2。原因如下：

虽然在函数的调用上没有区别，便在变量【i】，【j】运算上效率却是不同的。

代码1中【i】为赋值了100次，而【j】被赋值了100*10000次。

代码2中【i】为赋值了10000次，而【j】被赋值了100*10000次。

 

说上面的原理的目的在于引入in和exists执行机制。

我们假设i循环代表了外表也就是之前提到的A表，而j循环代表了内表，也就是之前提到的B表。

【in】相当于先选择j循环（内表，B表），后执行i循环（外表，A表）；

【exist】相当于先选择i循环（外表，A表），后执行j循环（内表，B表）；

类比差不多

in，exists执行效率大比拼：

还是以下面的SQL语句为例：

select * from A where A.name in (select B.name from B)

等价于

1. select B.name from B
2. select * from A

SELECT * FROM A WHERE EXISTS (SELECT B.name FROM b WHERE a.name = b.name)

等价于

1. SELECT * FROM A
2. SELECT B.name FROM b WHERE a.name = b.name

结合上述的理论，推出以下结论：

1. 如果A表记录条数多于B表，则选择为in效率更高；

2. 如果A表记录条数少于B表，则选择为exists效率更高；

 

说明1：

in操作符不仅能像下面这样用字符数组的形式，

select * from A where name in ("y", "z")

也可以向exists一样，用子查询：

select * from A where A.name in (select B.name from B)

 

说明2：

在In与exists的查询结果基本之上，如果想取反，加上NOT关键字就可以了。

select * from A where name in ("y", "z") 变成：

select * from A where name not in ("y", "z")

exists 变成 not exists。