在不存在的地方选择 *

2021-11-20 00:00:00 mysql not-exists

我认为我正在走这条正确的道路......请容忍我，因为我的 SQL 不是最好的

I think I'm going down the right path with this one... Please bear with me as my SQL isn't the greatest

我正在尝试查询数据库以从一个表中选择某些单元格在另一个表中不存在的所有内容.这么多没有多大意义，但我希望这段代码会

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)

所以基本上我有一张表格，上面有员工及其详细信息的列表.然后是另一个包含一些其他详细信息的表，包括他们的名字.eotm_dyn 表中没有 name 的地方，这意味着他们没有条目，我想看看他们到底是谁，或者换句话说，看看到底缺少什么.

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.

上面的查询没有返回任何内容，但我知道缺少 20 个左右的名字，所以我显然没有做对.

The above query returns nothing, but I know there are 20ish names missing so I've obviously not gotten it right.

有人可以帮忙吗?

推荐答案

您没有在查询中加入表格.

You didn't join the table in your query.

除非eotm_dyn 中根本没有记录，否则您的原始查询将始终不返回任何内容，在这种情况下，它将返回所有内容.

Your original query will always return nothing unless there are no records at all in eotm_dyn, in which case it will return everything.

假设这些表应该在 employeeID 上连接，请使用以下内容:

Assuming these tables should be joined on employeeID, use the following: 

SELECT  *
FROM    employees e
WHERE   NOT EXISTS
        (
        SELECT  null 
        FROM    eotm_dyn d
        WHERE   d.employeeID = e.id
        )

您可以使用 LEFT JOIN 关键字连接这些表并过滤掉 NULL，但这可能不如使用 NOT EXISTS.

You can join these tables with a LEFT JOIN keyword and filter out the NULL's, but this will likely be less efficient than using NOT EXISTS.

相关文章