MySQL找出在左表但是不在右表的字段

一个松

已于 2022-04-18 16:05:47 修改

阅读量2.4k

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： MySQL

于 2022-04-15 17:54:53 首次发布

本文链接：https://blog.youkuaiyun.com/kakadiablo/article/details/124199932

MySQL 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了三种在MySQL中查找A表中不包含B表中g值的方法，包括left join配合isnull，notexists子句，以及using notin。展示了使用这些技巧的SQL语句及执行计划。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

已知有两张表，找出在A.g中，不在B.g中的g。

mysql> select * from A;
+---+
| g |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)

mysql> select * from B;
+---+
| g |
+---+
| 1 |
| 3 |
| 5 |
+---+
3 rows in set (0.00 sec)

方法一
使用left join注意is null ，和null判断不能用=

mysql> explain select * from A left join B on A.g =B.g where B.g is null;
+----+-------------+-------+------------+-------+---------------+------+---------+---------------------+------+----------+--------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref                 | rows | filtered | Extra                                |
+----+-------------+-------+------------+-------+---------------+------+---------+---------------------+------+----------+--------------------------------------+
|  1 | SIMPLE      | A     | NULL       | index | NULL          | g    | 42      | NULL                |    4 |   100.00 | Using index                          |
|  1 | SIMPLE      | B     | NULL       | ref   | g             | g    | 42      | aiplatform_face.A.g |    1 |   100.00 | Using where; Not exists; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+---------------------+------+----------+--------------------------------------+
2 rows in set, 1 warning (0.00 sec)

方法二
使用not exists

mysql> explain select A.g from A where not exists (select 1 from B where A.g = B.g);
+----+-------------+-------+------------+-------+---------------+------+---------+---------------------+------+----------+--------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref                 | rows | filtered | Extra                                |
+----+-------------+-------+------------+-------+---------------+------+---------+---------------------+------+----------+--------------------------------------+
|  1 | SIMPLE      | A     | NULL       | index | NULL          | g    | 42      | NULL                |    4 |   100.00 | Using index                          |
|  1 | SIMPLE      | B     | NULL       | ref   | g             | g    | 42      | aiplatform_face.A.g |    1 |   100.00 | Using where; Not exists; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+---------------------+------+----------+--------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

方法三
使用not in，要求B.g不能有null，因为!= null总是false。

mysql> explain select A.g from A where A.g not in (select B.g from B);
+----+-------------+-------+------------+-------+---------------+------+---------+---------------------+------+----------+--------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref                 | rows | filtered | Extra                                |
+----+-------------+-------+------------+-------+---------------+------+---------+---------------------+------+----------+--------------------------------------+
|  1 | SIMPLE      | A     | NULL       | index | NULL          | g    | 42      | NULL                |    4 |   100.00 | Using index                          |
|  1 | SIMPLE      | B     | NULL       | ref   | g             | g    | 42      | aiplatform_face.A.g |    1 |   100.00 | Using where; Not exists; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+---------------------+------+----------+--------------------------------------+
2 rows in set, 1 warning (0.01 sec)