Forum Discussion
COUNT is 0 when nullable column in where clause?
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.
How can both of these statements return 0 rows?
select count(*) from my_table where internal_id = external_id; -- 0 select count(*) from my_table where internal_id <> external_id; -- 0
This behavior occurs because SQL comparisons involving NULL (= or <>) evaluate to NULL, which is treated as FALSE. If all rows in your table have NULL in either internal_id or external_id, neither condition (= or <>) will match, resulting in 0 rows for both queries.
You can confirm this by checking for NULL values using:
select count(*)
from my_table
where internal_id IS NULL
or external_id IS NULL
To include NULL values in your logic, you can either use COALESCE to replace NULL with a default value or use the NULL-safe equality operator (<=>) for comparisons.
Hope this helps ?
Regards,
Prasana