Optional filter on a left join column

I'm having issues wrapping my head around how to write a procedure that includes an optional filter on a left join.

I have two tables, Issues, and Customer_Location. Not all issues have been linked to a customer location. So I am fine with this as a starting point:

SELECT
  I.Issue_Number,
  C.Customer_Location_Code

FROM Issues I
LEFT JOIN Customer_Location C
  ON C.Customer_Location_Key = I.Customer_Location_Key


Issue_Number | Customer_Location_Code
1            | Chicago
2            | NULL
3            | Chicago
4            | New Yor开发者_开发问答k      

And this works, it gives me all the issues. But I want to add an optional parameter for the customer location code that if left null would return all 4 issues, but if set to say 1 for Chicago, only issue 1 and 3 would return.

I've tried this

DECLARE @customer_location_key INT
SET @customer_location_key = 1

SELECT
  I.Issue_Number,
  C.Customer_Location_Code

FROM Issues I
LEFT JOIN Customer_Location C
  ON C.Customer_Location_Key = I.Customer_Location_Key
 AND C.Customer_Location_Key = @customer_location_key

But I get the following results

Issue_Number | Customer_Location_Code
1            | Chicago
2            | NULL
3            | Chicago
4            | NULL

For some reason I seem to be having a brain fart right now and just can't seem to get my head around what SHOULD be something rather simple

---

Adding a where clause similar to below should do it.

DECLARE @customer_location_key INT
SET @customer_location_key = 1

SELECT
  I.Issue_Number,
  C.Customer_Location_Code

FROM Issues I
LEFT JOIN Customer_Location C
  ON C.Customer_Location_Key = I.Customer_Location_Key
where (@customer_location_key is null or C.Customer_Location_Key = @customer_location_key)

---

Use the where clause instead

DECLARE @customer_location_key INT
SET @customer_location_key = 1

SELECT
  I.Issue_Number,
  C.Customer_Location_Code

FROM Issues I
LEFT JOIN Customer_Location C
  ON C.Customer_Location_Key = I.Customer_Location_Key
WHERE
 (@customer_location_key is null OR C.Customer_Location_Key = @customer_location_key)

---

The reason that your query does not work as you expect is that first the 2 ON conditions are examined and then, because of the LEFT JOIN, all rows of table Issue that haven't been matched are added as well (with NULLs in the columns of table Customer_Location_Code).

DECLARE @customer_location_key INT
SET @customer_location_key = 1

SELECT
  I.Issue_Number,
  C.Customer_Location_Code

FROM Issues I
LEFT JOIN Customer_Location C
  ON C.Customer_Location_Key = I.Customer_Location_Key
WHERE ( @customer_location_key IS NULL )
   OR ( C.Customer_Location_Key = @customer_location_key )