Forum Discussion
jeffshieldsdev
Solution Sage
Solution SageCOUNT is 0 when nullable column in where clause?
I'm having trouble understanding this--if a column contains any nulls, do I have to use coalesce to perform a string comparison on the column? select count(*)
from my_table
-- 292575
select count(...
There is a NULL-safe equality operator in SparkSQL <=>
This properly handles NULLs and equality and can replace the need for coalesces.
https://spark.apache.org/docs/3.5.2/sql-ref-null-semantics.htmlA solution is not present in this thread, despite someone accepting a reply as a solution.
jeffshieldsdevSolution Sage
Solution SageI recreated my table without null values, and now = and <> work as expected
drop table my_table;
create or replace table my_table
using delta
as
select coalesce(internal_id,'') as internal_id
,coalesce(external_id,'') as external_id
from source1
union all
select coalesce(internal_id,'') as internal_id
,coalesce(external_id,'') as external_id
from source2
union all
select coalesce(internal_id,'') as internal_id
,coalesce(external_id,'') as external_id
from source3;select count(*)
from my_table
where internal_id = external_id;
-- 101330
select count(*)
from my_table
where internal_id <> external_id;
-- 191245Is this how Spark/Delta tables work and I was unaware?