PostgreSQL: Unexpected NULL values when using COALESCE with multiple JOINs on large tables
Could someone explain I'm working with an scenario with a query in PostgreSQL 13.3 where I'm trying to use `COALESCE` to handle potential NULL values from multiple LEFT JOINs... The query returns unexpected NULL values instead of the fallback value I expect. Here’s the query I am using: ```sql SELECT a.id, COALESCE(b.value, c.value, 'Default') AS result FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id LEFT JOIN table_c c ON a.id = c.a_id; ``` I’ve verified that for many rows in `table_a`, there should be corresponding entries in `table_b` or `table_c`. However, I'm seeing that some rows return NULL for `result`, which should not happen if both `b.value` and `c.value` are NULL. To debug, I ran the query without `COALESCE` to inspect the raw values: ```sql SELECT a.id, b.value, c.value FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id LEFT JOIN table_c c ON a.id = c.a_id; ``` In the result set, I found rows where both `b.value` and `c.value` are NULL, but there are also rows where one of them has a valid value. It looks like the `COALESCE` function isn’t behaving as I expect. I also tried using a more granular approach by checking each join condition separately, but the scenario continues. I suspect that it might be related to how PostgreSQL evaluates the JOINs, or perhaps there’s something subtle about the data itself that I’m missing. Any insights on why I might be getting these NULL results despite expecting a valid fallback value from `COALESCE`? Additionally, if there are any best practices for structuring such queries to avoid this kind of scenario, I would appreciate those as well. Thanks for taking the time to read this!