Survey banner
Switching to Dataiku - a new area to help users who are transitioning from other tools and diving into Dataiku! CHECK IT OUT

Left join coalesce returns null values when there value

ShrimpMania
Level 1
Left join coalesce returns null values when there value

 I have a table1 that specialty code B doesn't have company name. Since I needed to make the dataset to have all date(2020-01, 02,03 and 04), I had to make a table that has all the months and a column that all rows have 0(zero_col). I used cartesian join for that. After that, I tried to left join and select coalesce(value, zero_col)  where specialty_code, product and company type are the same but the highlighted rows returns 0 even though I expected the highlighted row will return its values 1100 and 800 but it returns 0.

 

 Shouldn't it return the value since both company name are empty and it recognise as it's the same?

 

table1:

specialty codeproductcompanytypedatevalue
Achocolatem&m'sgrocery2020-01-01T00:00:00.000Z3300
Bpasta grocery2020-01-01T00:00:00.000Z1100
Bpasta grocery2020-03-01T00:00:00.000Z800
Elaptophpelectric2020-01-01T00:00:00.000Z8800
Elaptophpelectric2020-02-01T00:00:00.000Z11000
Elaptophpelectric2020-03-01T00:00:00.000Z8800
Elaptophpelectric2020-04-01T00:00:00.000Z7000

 

 

Output

specialty codeproductcompanytypedatevalue
Achocolatem&m'sgrocery2020-01-01T00:00:00.000Z3300
Achocolatem&m'sgrocery2020-02-01T00:00:00.000Z0
Achocolatem&m'sgrocery2020-03-01T00:00:00.000Z0
Achocolatem&m'sgrocery2020-04-01T00:00:00.000Z0
Bpasta grocery2020-01-01T00:00:00.000Z0
Bpasta grocery2020-02-01T00:00:00.000Z0
Bpasta grocery2020-03-01T00:00:00.000Z0
Bpasta grocery2020-04-01T00:00:00.000Z0
Elaptophpelectric2020-01-01T00:00:00.000Z8800
Elaptophpelectric2020-02-01T00:00:00.000Z11000
Elaptophpelectric2020-03-01T00:00:00.000Z8800
Elaptophpelectric2020-04-01T00:00:00.000Z7000

 

0 Kudos
4 Replies
Turribeach

Where are the datasets stored (which technology/vendor) and which engine are you selecting to run this recipe? (bottom left corner).

0 Kudos
ShrimpMania
Level 1
Author

@Turribeach SQL code recipes with snowflake.

0 Kudos
ShrimpMania
Level 1
Author

@Turribeach   I found a post says null != null. thank you for trying to help me out

0 Kudos

Yes this is a common misconception. Null is nothing and therefore can't be compared with nothing else, including Null itself hence if Null can't be = Null then it has to be Null != Null. More importantly this also applies when you use the NOT IN() which if the nested select returns any Nulls caused if to return nothing.

0 Kudos

Labels

?
Labels (1)
A banner prompting to get Dataiku