👀 Views: 140 💬 Answers: 1 📅 Created: 2025-08-24

sql-server null joins sql

I'm working on a project and hit a roadblock. I've been banging my head against this for hours. I'm collaborating on a project where I'm having trouble with I'm stuck on something that should probably be simple... I'm working with an scenario when using parameterized queries in SQL Server 2017 where null values are involved in joins. I have two tables, `Customers` and `Orders`. I want to retrieve the orders for a specific customer, but if the customer has no orders, I still want to get their details with a null for the orders. However, when I use a left join with a parameterized query, I don't get the expected results. Here’s the code I’m using: ```sql DECLARE @CustomerId INT; SET @CustomerId = NULL; -- This is where I want to be able to pass in NULL to get all customers without orders SELECT c.CustomerId, c.CustomerName, o.OrderId FROM Customers c LEFT JOIN Orders o ON c.CustomerId = o.CustomerId WHERE (@CustomerId IS NULL OR c.CustomerId = @CustomerId); ``` When I run this, I get an empty result set if `@CustomerId` is set to NULL, which isn't what I expect. I've tried changing the `WHERE` clause to account for NULL, like this: ```sql WHERE c.CustomerId IS NOT NULL AND (@CustomerId IS NULL OR c.CustomerId = @CustomerId); ``` But this still doesn't return the expected results. If I set `@CustomerId` to a specific ID, it works perfectly, but I really need the ability to filter by NULL and get all customers. Is there a way to achieve this with parameterized queries? Am I missing something, or is there a better approach to handle this scenario? Any help would be appreciated! I'm working on a API that needs to handle this. Has anyone else encountered this? This is happening in both development and production on macOS. Any examples would be super helpful. What's the correct way to implement this? What's the correct way to implement this? Could this be a known issue?