Mysql -选择指定表中不存在的条目

Question

我有三张表：

mysql> describe messages;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| msgID  | varchar(36)  | YES  |     | NULL    |       |
| time   | mediumtext   | YES  |     | NULL    |       |
| sender | varchar(10)  | YES  |     | NULL    |       |
| hash   | varchar(44)  | YES  |     | NULL    |       |
| msg    | varchar(400) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

mysql> describe user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| msgID | varchar(36) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> describe user2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| msgID | varchar(36) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

我想从'messages‘表中选择所有条目，其中msgID不在'user1’或'user2‘表中。

我试过这个：

mysql> select * from messages where msgID not in 
(select msgID from user1) 
union 
(select msgID from user2);

但它导致了以下错误：

ERROR 1222 (21000): The used SELECT statements have a different number 
of columns

我将*替换为'msgID'，但结果不正确。

我该怎么办？

顺便说一句，“user”表的数量可以超过2个，因此解决方案应该足够灵活。

Answer 1

尝试此操作(仅更改了分组)：

mysql> select * from messages where msgID not in (
    select msgID from user1
    union 
    select msgID from user2
)

您的原始查询被解释为：

select * from messages where msgID not in (select msgID from user1) 

union 

(select msgID from user2);

而且由于messages的列比您从users2中选择的列多，所以它不知道如何“联合”。