MySql中的minus用法

在DB2和Oracle中，我们经常会用到minus这个函数，但是MySql中是没有minus的，那么要怎么办呢？

下面以一个小栗子来说下：

用例基础

表信息

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_eg_01        |
| t_eg_02        |
+----------------+
2 rows in set (0.00 sec)mysql> desc t_eg_01;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | varchar(5)   | NO   | PRI | NULL    |       |
| name   | varchar(20)  | YES  |     | NULL    |       |
| remark | varchar(255) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> desc t_eg_02;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | varchar(5)   | NO   | PRI | NULL    |       |
| name   | varchar(20)  | YES  |     | NULL    |       |
| remark | varchar(255) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

数据信息

mysql> select * from t_eg_01 \G
*************************** 1. row ***************************id: 1name: 张三
remark: 特长：音乐
*************************** 2. row ***************************id: 2name: 李四
remark: 爱好：游泳
*************************** 3. row ***************************id: 3name: 王五
remark: 富二代
3 rows in set (0.00 sec)mysql> select * from t_eg_02 \G
*************************** 1. row ***************************id: 1name: 张三
remark: 特长：音乐
*************************** 2. row ***************************id: 3name: 王五
remark: 富二代
*************************** 3. row ***************************id: 5name: 光头
remark: 平民
3 rows in set (0.00 sec)

如果用minus会直接报错

mysql> select * from t_eg_01-> minus-> select * from t_eg_02;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from t_eg_02' at line 3

实现方式

通过left join实现minus的效果，如下图所示，应该得到id为2的数据

mysql> select a.* from t_eg_01 a-> left join-> t_eg_02 b-> on a.id=b.id-> where b.id is null;
+----+--------+-----------------+
| id | name   | remark          |
+----+--------+-----------------+
| 2  | 李四    | 爱好：游泳       |
+----+--------+-----------------+
1 row in set (0.00 sec)