MySQL 选择 * 不存在的地方
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/915643/
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
SELECT * WHERE NOT EXISTS
提问by Ciaran
I think I'm going down the right path with this one... Please bear with me as my SQL isn't the greatest
我想我正在走这条正确的道路......请容忍我，因为我的 SQL 不是最好的
I'm trying to query a database to select everything from one table where certain cells don't exist in another. That much doesn't make a lot of sense but I'm hoping this piece of code will
我正在尝试查询数据库以从一个表中选择某些单元格在另一个表中不存在的所有内容。这么多没有多大意义，但我希望这段代码会
SELECT * from employees WHERE NOT EXISTS (SELECT name FROM eotm_dyn)
So basically I have one table with a list of employees and their details. Then another table with some other details, including their name. Where there name is not in the eotm_dyn table, meaning there is no entry for them, I would like to see exactly who they are, or in other words, see what exactly is missing.
所以基本上我有一张桌子，上面有员工名单和他们的详细信息。然后是另一个包含一些其他详细信息的表，包括他们的名字。在 eotm_dyn 表中没有 name 的地方，这意味着他们没有条目，我想确切地知道他们是谁，或者换句话说，看看到底缺少什么。
The above query returns nothing, but I know there are 20ish names missing so I've obviously not gotten it right.
上面的查询什么都不返回，但我知道缺少 20 个名字，所以我显然没有做对。
Can anyone help?
任何人都可以帮忙吗？
回答by Quassnoi
You didn't join the table in your query.
您没有在查询中加入该表。
Your original query will always return nothing unless there are no records at all in eotm_dyn, in which case it will return everything.
除非在 中根本没有记录，否则您的原始查询将始终不返回任何内容eotm_dyn，在这种情况下，它将返回所有内容。
Assuming these tables should be joined on employeeID, use the following:
假设这些表应该在 上连接employeeID，请使用以下内容：
SELECT *
FROM employees e
WHERE NOT EXISTS
(
SELECT null
FROM eotm_dyn d
WHERE d.employeeID = e.id
)
You can join these tables with a LEFT JOINkeyword and filter out the NULL's, but this will likely be less efficient than using NOT EXISTS.
您可以使用LEFT JOIN关键字连接这些表并过滤掉NULL's，但这可能比使用NOT EXISTS.
回答by Robin Day
SELECT * FROM employees WHERE name NOT IN (SELECT name FROM eotm_dyn)
OR
或者
SELECT * FROM employees WHERE NOT EXISTS (SELECT * FROM eotm_dyn WHERE eotm_dyn.name = employees.name)
OR
或者
SELECT * FROM employees LEFT OUTER JOIN eotm_dyn ON eotm_dyn.name = employees.name WHERE eotm_dyn IS NULL
回答by Mike Tunnicliffe
You can do a LEFT JOIN and assert the joined column is NULL.
您可以执行 LEFT JOIN 并断言连接的列为 NULL。
Example:
例子：
SELECT * FROM employees a LEFT JOIN eotm_dyn b on (a.joinfield=b.joinfield) WHERE b.name IS NULL
回答by Cade Roux
SELECT * from employees
WHERE NOT EXISTS (SELECT name FROM eotm_dyn)
Never returns any records unless eotm_dynis empty. You need to some kind of criteria on SELECT name FROM eotm_dynlike
除非eotm_dyn为空，否则永远不会返回任何记录。你需要某种标准对SELECT name FROM eotm_dyn样
SELECT * from employees
WHERE NOT EXISTS (
SELECT name FROM eotm_dyn WHERE eotm_dyn.employeeid = employees.employeeid
)
assuming that the two tables are linked by a foreign key relationship. At this point you could use a variety of other options including a LEFT JOIN. The optimizer will typically handle them the same in most cases, however.
假设两个表通过外键关系链接。此时，您可以使用各种其他选项，包括 LEFT JOIN。然而，在大多数情况下，优化器通常会以相同的方式处理它们。
回答by Andre Miller
You can also have a look at this related question. That user reported that using a join provided better performance than using a sub query.
你也可以看看这个相关的问题。该用户报告说，使用联接比使用子查询提供了更好的性能。
