Forum Discussion
Solution SageCOUNT 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.
Yes, this is expected behavior. In SQL, NULL comparisons like = or <> evaluate to NULL (treated as FALSE), which is why your queries didn't behave as expected earlier. Using COALESCE to replace NULL with '' ensures comparisons work correctly. Alternatively, you can use IS NULL/IS NOT NULL or the NULL-safe equality operator (<=>) in Spark for precise handling without modifying data."
- jeffshieldsdev
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