postgre比较空值的奇怪行为 SQL

Question

我正在比较两个表，得到一个奇怪的结果。我从比较通常为空的两列开始。声明 null = null 总是返回 false。我发现对于这些列工作，制作是空的。结果显示了很多双行，我不明白。

所以基本上这一排data.data_1

column1     column2     column3     column4
apple       tree        NULL        NULL        

还有这一排data.data_2

column1     column2     column3     column4
apple       tree        NULL        NULL        

在以下查询后返回

select
br.column1, br.column2, br.column3, br.column4,  
af.column1, af.column2
from
data.data_1 as br
left join data.data_2 as af on 
(br.column1 = af.column1 and
br.column2 = af.column2 and
br.column3 IS NULL and af.column3 IS NULL and 
br.column4 IS NULL and af.column4 IS NULL)
column1     column2     column3     column4     af.column1     af.column2     
apple       tree        NULL        NULL         apple          tree 
apple       tree        NULL        NULL         apple          tree 
apple       tree        NULL        NULL         apple          tree      
apple       tree        NULL        NULL         apple          tree      
apple       tree        NULL        NULL         apple          tree      
apple       tree        NULL        NULL         apple          tree      
apple       tree        NULL        NULL         apple          tree      
apple       tree        NULL        NULL         apple          tree      
apple       tree        NULL        NULL         apple          tree      
apple       tree        NULL        NULL         apple          tree      
apple       tree        NULL        NULL         apple          tree      
apple       tree        NULL        NULL         apple          tree      
apple       tree        NULL        NULL         apple          tree      
apple       tree        NULL        NULL         apple          tree      
apple       tree        NULL        NULL         apple          tree    

而不是我希望它返回的内容，这将是：

column1     column2     column3     column4     af.column1     af.column2     
apple       tree        NULL        NULL         apple          tree 

我可以围绕它添加一个不同的功能，但我觉得这将是一个不必要的额外操作。

Answer 1

demo： db<>fiddle

SELECT *
FROM br
LEFT JOIN af
ON 
br.col1 = af.col1 
AND br.col2 = af.col2 
AND COALESCE(br.col3, af.col3, br.col4, af.col4) IS NULL

COALESCE() IS NULL确保此函数中的所有元素(所有列)都NULL。

Answer 2

如果要比较两个可为空的列并考虑NULL = NULL，请使用IS NOT DISTINCT FROM：

SELECT CASE WHEN NULL = NULL                    THEN 'Equal' ELSE 'IDK' END       -- IDK
SELECT CASE WHEN NULL IS NOT DISTINCT FROM NULL THEN 'Equal' ELSE 'Not Equal' END -- Equal

只需在原始查询中插入此条件：

select
br.column1, br.column2, br.column3, br.column4,
af.column1, af.column2
from
data.data_1 as br
left join data.data_2 as af on (
br.column1 = af.column1 and
br.column2 = af.column2 and
br.column3 IS NOT DISTINCT FROM af.column3 and
br.column4 IS NOT DISTINCT FROM af.column4
)

Answer 3

left join使查询列出data_1中的所有行，无论data_2中是否有匹配的行。

此外，仅当两个表中NULLcolumn3和column4时，才联接 - 并列出这些NULL值。委婉地说，这有点令人困惑。

如果你写下你想要实现的目标，你的问题会更容易回答。