作为程序员一定要保持良好的睡眠，才能好编程

  2. 首页
  3. Mysql

查找jf_a 不在jf_b表中的数据

发布时间：2018-06-26

以下有jf_a 和 jf_b表，请查找出jf_a 表中不存在jf_b表中的数据 的sql语句


看下面




 desc jf_a;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)     | YES  |     | NULL    |                |
| age   | int(10) unsigned | YES  |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+

jf_a表
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | jone2 |   11 |
|  2 | luch2 |   22 |
|  3 | jack  |    0 |
|  4 | jim   |    0 |
|  5 | james |    0 |
+----+-------+------+



jf_b表结构
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| a_id  | int(10) unsigned | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

jf_b 数据
+----+------+
| id | a_id |
+----+------+
|  1 |    3 |
+----+------+

请查找到jf_a不在jf_b中的数据

select id,name from jf_a where id not in(select a_id from jf_b);

+----+-------+
| id | name  |
+----+-------+
|  1 | jone2 |
|  2 | luch2 |
|  4 | jim   |
|  5 | james |
+----+-------+


#然后通过explain 来看看执行结果：

explain select id,name from jf_a where id not in(select a_id from jf_b) \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: jf_a
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: jf_b
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

使用 not in 这种方法，如果数据比较少还是可以的，如果数据特别多，执行时间比较长，不是一个较好的解决方案。




建议使用  join 来试试：


先普及一下join知识


left join 以左表为主 ，右表匹配不到以NULL 补齐


right join 以右表为主 左表匹配不到以NULL补齐  因为右表的数据都存在于左表，所以没有显示出null来。


inner join 两表都存在 才显示

mysql> #查找出b表不存在a表的中的数据
mysql> select * from jf_a as a left join jf_b as b on a.id=b.a_id;
+----+-------+------+------+------+
| id | name  | age  | id   | a_id |
+----+-------+------+------+------+
|  1 | jone2 |   11 | NULL | NULL |
|  2 | luch2 |   22 | NULL | NULL |
|  3 | jack  |    0 |    1 |    3 |
|  4 | jim   |    0 | NULL | NULL |
|  5 | james |    0 | NULL | NULL |
+----+-------+------+------+------+
5 rows in set (0.00 sec)

mysql> select * from jf_a as a right join jf_b as b on a.id=b.a_id;
+------+------+------+----+------+
| id   | name | age  | id | a_id |
+------+------+------+----+------+
|    3 | jack |    0 |  1 |    3 |
+------+------+------+----+------+
1 row in set (0.00 sec)

mysql> select * from jf_a as a inner join jf_b as b on a.id=b.a_id;
+----+------+------+----+------+
| id | name | age  | id | a_id |
+----+------+------+----+------+
|  3 | jack |    0 |  1 |    3 |
+----+------+------+----+------+
1 row in set (0.00 sec)


最后sql语句：


仔细观察可以发现，我们需要的结果集[1, 2, 4, 5]所对应的id1字段都是null。


这样，在查询语句中加入条件B.id is null，我们只需要A表中的数据，B表的数据忽略。

因此 添加  b.id  is null

就是下面的结果了：


select a.id,a.name from jf_a as a left join jf_b as b on a.id=b.a_id where b.id is NULL;
+----+-------+
| id | name  |
+----+-------+
|  1 | jone2 |
|  2 | luch2 |
|  4 | jim   |
|  5 | james |
+----+-------+

有这四条数据不在b表中。


那么来看看explain

explain select a.id,a.name from jf_a as a left join jf_b as b on a.id=b.a_id where b.id is NULL \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where; Not exists
2 rows in set (0.00 sec)


看到数据都是全表扫描  类型是 simple



如果我们加一个索引呢？  在jf_b 表中 a_id 加一个 index 索引后，看看结果

explain select a.id,a.name from jf_a as a left join jf_b as b on a.id=b.a_id where b.id is NULL \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ref
possible_keys: a_id
          key: a_id
      key_len: 5
          ref: test.a.id
         rows: 1
        Extra: Using where; Using index; Not exists
2 rows in set (0.00 sec)


如果A、B表的id、和B表的a_id都加了索引，那么join方式就能够命中索引

看到使用了 ref ，索引使用了a_id  在大量的数据下，效率是非常高的。






更多>> Mysql