Left join coalesce returns null values when there value

ShrimpMania

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

Turribeach

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

ShrimpMania

@Turribeach
SQL code recipes with snowflake.

ShrimpMania

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

Turribeach

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.