MySQL--in和exists函数（8）持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第39天

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第39天，点击查看活动详情

in和exists函数

上面我们说了小表驱动大表,就是小的数据集驱动大的数据集, 主要是为了减少数据库的连接次数,根据具体情况的不同,又出现了两个函数 exists 和 in 函数

创建部门表与员工表,并插入数据

-- 部门表
CREATE TABLE department (
  id INT(11) PRIMARY KEY,
  deptName VARCHAR(30) ,
  address VARCHAR(40) 
) ;

-- 部门表测试数据
INSERT INTO `department` VALUES (1, '研发部', '1层');
INSERT INTO `department` VALUES (2, '人事部', '3层');
INSERT INTO `department` VALUES (3, '市场部', '4层');
INSERT INTO `department` VALUES (5, '财务部', '2层');

-- 员工表
CREATE TABLE employee (
  id INT(11) PRIMARY KEY,
  NAME VARCHAR(20) ,
  dep_id INT(11) ,
  age INT(11) ,
  salary DECIMAL(10, 2)
);

-- 员工表测试数据
INSERT INTO `employee` VALUES (1, '鲁班', 1, 15, 1000.00);
INSERT INTO `employee` VALUES (2, '后裔', 1, 22, 2000.00)
INSERT INTO `employee` VALUES (4, '阿凯', 2, 20, 3000.00);
INSERT INTO `employee` VALUES (5, '露娜', 2, 30, 3500.00);
INSERT INTO `employee` VALUES (6, '李白', 3, 25, 5000.00);
INSERT INTO `employee` VALUES (7, '韩信', 3, 50, 5000.00);
INSERT INTO `employee` VALUES (8, '蔡文姬', 3, 35, 4000.00);
INSERT INTO `employee` VALUES (3, '孙尚香', 4, 20, 2500.00);

1) in 函数

假设: department表的数据小于 employee表数据, 将所有部门下的员工都查出来,应该使用 in 函数

-- 编写SQL,使in 函数
SELECT * FROM employee e WHERE e.dep_id IN (SELECT id FROM department);

in函数的执行原理

in 语句, 只执行一次, 将 department 表中的所有id字段查询出来并且缓存.
检查 department 表中的id与 employee 表中的 dep_id 是否相等, 如果相等添加到结果集, 直到遍历完department 所有的记录.

-- 先循环: select id from department; 相当于得到了小表的数据
for(i = 0; i < $dept.length; i++){  -- 小表
    -- 后循环: select * from employee where e.dep_id  = d.id;
    for(j = 0 ; j < $emp.legth; j++){  -- 大表
    
        if($dept[i].id == $emp[j].dep_id){
            $result[i] = $emp[j]
            break;
        }
        
    }
}

结论: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用 in

2) exists 函数

假设: department表的数据大于 employee表数据, 将所有部门下的的员工都查出来,应该使用 exists 函数.
```
explain SELECT * FROM employee e WHERE EXISTS 
(SELECT id FROM department d WHERE d.id = e.dep_id);
```
exists 特点

exists 子句返回的是一个布尔值，如果有返回数据，则返回值是true，反之是false。

如果结果为 true , 外层的查询语句会进行匹配,否则外层查询语句将不进行查询或者查不出任何记录。

exists 函数的执行原理

-- 先循环: SELECT * FROM employee e;
-- 再判断: SELECT id FROM department d WHERE d.id = e.dep_id

for(j = 0; j < $emp.length; j++){  -- 小表

-- 遍历循环外表，检查外表中的记录有没有和内表的的数据一致的, 匹配得上就放入结果集。
    if(exists(emp[i].dep_id)){   -- 大表
        $result[i] = $emp[i];
    }
}

3) in 和 exists 的区别

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用 in
如果主查询得出的结果集记录较少，子查询中的表较大且又有索引时应该用 exists
一句话: in后面跟的是小表，exists后面跟的是大表。