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”，但结果不正确。

我应该做什么？

顺便说一句，“用户”表的数量可以超过 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 中选择的列多，因此它不知道如何“联合”