左连接不显示空值

I need to find the items that exist in table A but not in table B. Now that would be really simple in MySQL doing a join like this

我需要找到表A中存在的项，而不是表b中存在的项

select * from A 
left join B on A.key=B.key 
where B.key is null

However for some reason this is not working in MSSQL. I have created the query without the where clause to see all the results and I only see matches, not null values. Do you have any idea why this is not working?

然而，由于某些原因，这在MSSQL中不起作用。我创建的查询没有where子句来查看所有结果，我只看到匹配项，没有null值。你知道这怎么不行吗?

I know I can alternatively use "when not exists" but I want to know the reason as to why with a join is not working.

我知道我也可以使用“当不存在时”，但是我想知道为什么使用join不能工作。

I am adding the code for your review

我正在为您的评论添加代码

select Absences.CustomerID, b.* 
from (
    select * from openquery(JUAN_INTERFACE,'select cmp_wwn from Planet_Customers where   i_outcome =4')) b 
left join Absences on Absences.CustomerID = b.cmp_wwn 
where Absences.Type = 3223

1 个解决方案

#1

Your where clause is filtering out null values:

where子句过滤空值:

where Absences.Type = 3223

You are left-joining from the openquery subquery to Absences; and then filtering only rows that have a specific (non-null) value in an Absences column.

从openquery子查询向左连接到缺勤;然后只过滤在缺席列中具有特定(非空)值的行。

Did you mean to join the other way around, from Absenses to openquery?

你的意思是从Absenses到openquery反过来加入的吗?

#1