CODEWITHSUNDEEP
sqlpostgresqlnull
Zuenie
Zuenie

Reputation: 973

Strange behaviour SQL on comparing null values

I am comparing two tabels and am getting a strange result. I started with comparing two columns that are often null. Stating null = null always returns false. I found out that making is IS NULL for those columns works. The result shows a lot of double rows, which I dont understand.

So basically this row of data.data_1 

column1     column2     column3     column4
apple       tree        NULL        NULL

and this row of data.data_2 

column1     column2     column3     column4
apple       tree        NULL        NULL

returns after the following query

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

In stead of what I want it to return which would be: 

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

I could add a distinct function around it but I have the feeling that this would be an extra operation that should not be necessary.

Upvotes: 0

Views: 121

Answers (3)

S-Man
S-Man

Reputation: 23686

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

The COALESCE() IS NULL ensures that all elements in this function (all columns) are NULL.

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272156

If you want to compare two nullable columns and want to consider NULL = NULL then use 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

Just plug this condition in your original query:

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
    )

Upvotes: 1

Titus
Titus

Reputation: 512

The left join makes the query list all rows from data_1, no matter if there is a matching row in data_2 or not.

Also, you join only when column3 and column4 are NULL in both tables - and list those NULL values. That´s a bit confusing, mildly put.

Your question would be much easier to answer if You would write what You want to achieve.

Upvotes: 0

Related Questions