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 code | product | company | type | date | value |
A | chocolate | m&m's | grocery | 2020-01-01T00:00:00.000Z | 3300 |
B | pasta | grocery | 2020-01-01T00:00:00.000Z | 1100 | |
B | pasta | grocery | 2020-03-01T00:00:00.000Z | 800 | |
E | laptop | hp | electric | 2020-01-01T00:00:00.000Z | 8800 |
E | laptop | hp | electric | 2020-02-01T00:00:00.000Z | 11000 |
E | laptop | hp | electric | 2020-03-01T00:00:00.000Z | 8800 |
E | laptop | hp | electric | 2020-04-01T00:00:00.000Z | 7000 |
Output
specialty code | product | company | type | date | value |
A | chocolate | m&m's | grocery | 2020-01-01T00:00:00.000Z | 3300 |
A | chocolate | m&m's | grocery | 2020-02-01T00:00:00.000Z | 0 |
A | chocolate | m&m's | grocery | 2020-03-01T00:00:00.000Z | 0 |
A | chocolate | m&m's | grocery | 2020-04-01T00:00:00.000Z | 0 |
B | pasta | grocery | 2020-01-01T00:00:00.000Z | 0 | |
B | pasta | grocery | 2020-02-01T00:00:00.000Z | 0 | |
B | pasta | grocery | 2020-03-01T00:00:00.000Z | 0 | |
B | pasta | grocery | 2020-04-01T00:00:00.000Z | 0 | |
E | laptop | hp | electric | 2020-01-01T00:00:00.000Z | 8800 |
E | laptop | hp | electric | 2020-02-01T00:00:00.000Z | 11000 |
E | laptop | hp | electric | 2020-03-01T00:00:00.000Z | 8800 |
E | laptop | hp | electric | 2020-04-01T00:00:00.000Z | 7000 |
Answers
-
Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,975 Neuron
Where are the datasets stored (which technology/vendor) and which engine are you selecting to run this recipe? (bottom left corner).
-
@Turribeach
SQL code recipes with snowflake. -
@Turribeach
I found a post says null != null. thank you for trying to help me out -
Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,975 Neuron
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.