SQL Server: advanced patterns when using COALESCE with NULL values in joins
I'm stuck on something that should probably be simple. I'm working with SQL Server 2019 and I've encountered some unexpected behavior while trying to perform a LEFT JOIN with COALESCE on nullable columns. My goal is to return results from two tables, but I want to replace NULL values from one of the tables with a default value. However, it seems that the COALESCE function is not behaving as I expected. Here’s the query I’m using: ```sql SELECT a.id, COALESCE(b.value, 'Default') AS value FROM TableA a LEFT JOIN TableB b ON a.id = b.a_id; ``` In this case, `TableB` has a nullable column `value`, and I want to ensure that if it’s NULL, I get ‘Default’ instead. However, when `TableB` has no matching rows, I’m getting NULL for the `value` column instead of ‘Default’. To troubleshoot, I tried running the following: ```sql SELECT a.id, b.value FROM TableA a LEFT JOIN TableB b ON a.id = b.a_id; ``` This query correctly returns NULL for `b.value` when there are no matches, but as soon as I introduce the COALESCE in the first query, it doesn’t seem to change the output when there are no matches. I've also checked the data types of the columns involved, and both `TableA.id` and `TableB.a_id` are of type INT, which should not cause any issues. I feel like I might be missing something fundamental about the COALESCE function in this context or how SQL Server handles NULLs in joins. Can anyone shed some light on why COALESCE isn’t providing the expected output in this scenario? I'm working on a CLI tool that needs to handle this. For reference, this is a production CLI tool. Is there a better approach?