MySql关联条件on与where条件的踩坑

Mysql 表关联条件ON与where条件区别踩坑

前言

前段时间，生产上遇到了个奇怪的问题，同一个用户信息，页面上出现了两次。展现出来的还一模一样，这让运营人员很是不解。（别说他不理解，写程序的我也不是很理解）

得知问题后，拿到关键参数，结合自己的SQL语句去生产数据库查询了一下，嘿，还真是两条。但是有点差别，一条是有效的，一条是无效的。但是我查询条件也确实带了有效记录的筛选。为什么没用呢？

结论

先说结论。结论就是我把查询生效记录的过滤条件写在了关联条件上，而不是 where条件上。所以导致出现了问题。问题不是大问题，是我将关联条件与过滤条件搞混了。

表关联的关联条件 就是字如其意，控制两个表之间关联的条件，也就是满足条件才进行关联。而过滤条件则是 对已有的数据进行筛选。 一个控制源头，一个控制结果。

第一次见你的时候，我的心里已经炸成了烟花，需要用一生来打扫灰炉。——钱钟书

还原案件

这里就用经典的EMP 和 DEPT 两个表来进行还原。数据进行了删减，我们只关注 **40**部门的记录即可。 人员与部门添加了一条生效的记录，一条失效的记录。

表结构和脚本如下：

-- 部门表
drop table if exists DEPT;
CREATE TABLE DEPT
(
  ID     TINYINT PRIMARY KEY, -- ID
  DEPTNO INT,                 -- 部门编号
  DNAME  VARCHAR(14),         -- 部门名称
  LOC    VARCHAR(13),         -- 部门地址
  STATE  TINYINT              -- 状态
);

INSERT INTO DEPT
VALUES (40, 40, 'OPERATIONS', 'BOSTON', 1);
INSERT INTO DEPT
VALUES (50, 40, 'OPERATIONS', 'BOSTON', 0);

-- 员工表
drop table if exists EMP;
CREATE TABLE EMP
(
  EMPNO    INT PRIMARY KEY, -- 员工编号
  ENAME    VARCHAR(10),     -- 员工名称
  JOB      VARCHAR(9),      -- 工作
  MGR      DOUBLE,          -- 直属领导编号
  HIREDATE DATE,            -- 入职时间
  SAL      DOUBLE,          -- 工资
  COMM     DOUBLE,          -- 奖金
  DEPTNO   INT,             -- 部门号
  STATE    TINYINT          -- 状态
);



INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, STATE)
VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 40, 1);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, STATE)
VALUES (7900, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 40, 0);

当你发现自己和大多数人站在一边，你就该停下来反思一下。 ——马克・吐温

线上版本的写法

来看下线上版本的写法，（SQL不是真实的，只是模拟了写法，实际业务比这复杂得多，所以不用纠结这里的业务）
select * from EMP em left join DEPT dpt on em.DEPTNO = dpt.DEPTNO and dpt.STATE = 1 and em.STATE = 1;

表关联查询人员和部门皆是生效状态的记录，来看一下结果：

我们想要的结果是，部门和人员都是生效状态的记录。然而查到了两条记录。一条是生效的，一条是失效的。 这就是线上的情况。

很明显这不是我们想要的结果。我以为这里的关联条件最终会对结果进行过滤，最终会返回满足关联条件的，但是这里的关联条件仅仅是关联条件，满足才关联，而不是关联后过滤。

所以我们可以看到，失效的emp并没有被过滤掉，而是展现出来了。同时并没有关联部门信息。然后失效的dept部门记录也没有被关联出来。如果上面的查询中，不加dpt.state =1 那么查询结果将会多一条记录，结果如下：

那么怎么改呢？很简单。我们可以用where条件进行过滤。

改正版

select * from EMP em left join DEPT dpt on em.DEPTNO = dpt.DEPTNO where em.STATE = 1 and dpt.STATE = 1;
这样的话，关联条件变成了只要部门编号相同就可以进行关联。，然后再根据where条件进行筛选。我们来看一下结果：

可以看到那条记录已经被过滤掉了。

总结

基础知识很重要。基础概念很重要。
关联条件就是关联条件，不会进行过滤，仅仅是满足条件的才进行关联
过滤条件是对满足条件的结果进行过滤，这是两个概念。不可混淆。

凡心所向，素履所往，生如逆旅，一苇以航。 ——《尘曲》