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

已知有两张表，找出在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)