MySQL选择在其他表中没有匹配列的行
目前我似乎无法弄清楚这一点.我试图连接两个表，只选择表 A 中在表 B 中没有匹配列的行.例如，假设我们有一个用户表和一个发送表.
I can't seem to figure this out so far. I am trying to join two tables and only select the rows in table A that do not have a matching column in table B. For example, lets assume we have a users table and a sent table.
users 表具有以下列:id, usernamesent 表有以下列:id, username
users table has the following columns: id, username
sent table has the following columns: id, username
我想从 users 中选择所有行，其中 username 在 sent 表中不存在.因此，如果 tom 在 users 和 sent 中，他将不会被选中.如果他在 users 但不在 sent 中，他将被选中.我试过了，但它根本不起作用:
I want to select all rows from users where username does not exist in sent table. So, if tom is in users and in sent he will not be selected. If he is in users but not in sent he will be selected. I tried this but it didn't work at all:
SELECT pooltest.name,senttest.sentname
FROM pooltest,senttest
WHERE pooltest.name != senttest.sentname
推荐答案
试试这个 SQL:
SELECT users.username
FROM users
LEFT JOIN sent ON sent.username = users.username
WHERE sent.username IS NULL;
我认为更好的方法是:
SELECT users.username
FROM users
LEFT JOIN sent ON sent.id = users.id
WHERE sent.id IS NULL;
作为两个 id 字段，都将被索引(我原以为是主键)，因此此查询将比我建议的第一个查询得到更好的优化.
As both the id fields, would be indexed (primary key I would have thought) so this query would be better optimised than the first one I suggested.
然而，您可能会发现我的第一个建议更适合您，这取决于您对应用程序的要求.
However you may find my first suggestion better for you, it depends on what your requirements are for your application.
相关文章