PostgreSQL - Unexpected NULL values when using LEFT JOIN with COALESCE on multiple columns
I'm integrating two systems and I'm integrating two systems and Quick question that's been bugging me - I'm working with an scenario with a LEFT JOIN in PostgreSQL where I'm trying to fetch data from two tables and using COALESCE to handle possible NULL values... Specifically, I have two tables: `orders` and `customers`. The question arises when there's no match in the `customers` table for certain `order_id`s from the `orders` table. Instead of the expected default values from COALESCE, the result set returns NULL for some columns. Here's the SQL query I'm running: ```sql SELECT o.order_id, COALESCE(c.customer_name, 'Unknown Customer') AS customer_name, COALESCE(c.customer_email, 'no-email@example.com') AS customer_email FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id; ``` In the `orders` table, I have some entries where `customer_id` does not exist in the `customers` table, which is expected behavior for a LEFT JOIN. However, I observed that for some `order_id`s, the `customer_name` and `customer_email` are returning NULL instead of the fallback values provided in COALESCE. I tried executing the query directly in the PostgreSQL console, and here’s a sample of the output: ``` order_id | customer_name | customer_email ----------+----------------------+------------------------ 1 | John Doe | john.doe@example.com 2 | NULL | NULL 3 | Alice Smith | alice.smith@example.com ``` As you can see, the second row is returning NULL instead of 'Unknown Customer' and 'no-email@example.com'. I checked the data types of `customer_name` and `customer_email`, and they are both of type `VARCHAR` in the `customers` table, which should be compatible with COALESCE. I also verified that there are indeed no matching `customer_id`s for some `orders`, so that is not the scenario. I've run `EXPLAIN` on the query, and it shows that the execution plan looks fine, but I need to pinpoint why COALESCE isn't behaving as expected. Is there something I'm overlooking, or could there be any specific PostgreSQL settings that affect how NULL values are handled in this context? Any insights would be greatly appreciated! Has anyone else encountered this? Has anyone else encountered this? This is part of a larger microservice I'm building. Any examples would be super helpful. This issue appeared after updating to Sql 3.9. This is for a microservice running on Windows 10. Any feedback is welcome!