MySQL MySQL选择在其他表中没有匹配列的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10968767/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL select rows that do not have matching column in other table
提问by xendi
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.
到目前为止，我似乎无法弄清楚这一点。我试图连接两个表，只选择表 A 中在表 B 中没有匹配列的行。例如，假设我们有一个用户表和一个发送表。
userstable has the following columns: id, usernamesenttable has the following columns: id, username
users表具有以下列：id, usernamesent表具有以下列：id, username
I want to select all rows from userswhere usernamedoes not exist in senttable. So, if tomis in usersand in senthe will not be selected. If he is in usersbut not in senthe will be selected. I tried this but it didn't work at all:
我想选择的所有行users，其中username在不存在的sent表。所以，如果tom是 inusers和 insent他将不会被选中。如果他在users但不在，sent他将被选中。我试过这个，但它根本不起作用：
SELECT pooltest.name,senttest.sentname
FROM pooltest,senttest
WHERE pooltest.name != senttest.sentname
采纳答案by Haroon
Try this SQL:
试试这个 SQL：
SELECT users.username
FROM users
LEFT JOIN sent ON sent.username = users.username
WHERE sent.username IS NULL;
The better way in my opinion would be:
我认为更好的方法是：
SELECT users.username
FROM users
LEFT JOIN sent ON sent.id = users.id
WHERE sent.id IS NULL;
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.
由于两个 id 字段都将被索引（我原以为是主键），因此此查询将比我建议的第一个查询得到更好的优化。
However you may find my first suggestion better for you, it depends on what your requirements are for your application.
但是，您可能会发现我的第一个建议更适合您，这取决于您对应用程序的要求。
回答by Lieven Keersmaekers
Typically, you would use NOT EXISTSfor this type of query
通常，您将NOT EXISTS用于此类查询
SELECT p.Name
FROM pooltest p
WHERE NOT EXISTS (SELECT s.Name
FROM senttest s
WHERE s.Name = p.Name)
An alternative would be to use a LEFT OUTER JOINand check for NULL
另一种方法是使用 aLEFT OUTER JOIN并检查NULL
SELECT p.Name
FROM pooltest p
LEFT OUTER JOIN senttest s ON s.Name = p.Name
WHERE s.Name IS NULL
Note that the implicit join syntax you are using is considered obsolete and should be replaced with an explicit join.
请注意，您使用的隐式连接语法已过时，应替换为显式连接。
回答by Upendra Singh
May be this one can help you ....
也许这个可以帮助你......
I had also the same problem but Solved using this this query
我也有同样的问题，但使用这个查询解决了
INSERT INTO tbl1 (id,name) SELECT id,name from tbl2 where (name) not in(select name from tbl1);
hope this one will solve your problem
希望这个能解决你的问题
