I had a question yesterday about whether or not you can use a case statement as part of a join condition. I was pretty sure the answer was yes, but we can always just run a simple test to prove it. It’s a pretty simple test to setup, I’m going to create 2 tables, populate them with data and then try joining with a case statement.
Setup the tables:
--Setup the tables create table tbl1 (ID INT, DoJoin BIT) create table tbl2 (ID INT, tbl1ID INT)
Pretty simple tables. The first one has an ID field which we’ll just populate with an incrementing INT, the DoJoin we’ll set to 1 when we want the join to happen and to 0 when we don’t want it to happen. The second table just has it’s own ID and an ID to join back to tbl1 – you could make it a foreign key.
Populate the data:
DECLARE @counter INT = 0 WHILE @Counter < 20 BEGIN SET @counter = @counter + 1 INSERT INTO tbl1 VALUES (@counter, CASE WHEN @counter % 2 = 0 THEN 1 ELSE 0 END) INSERT INTO tbl2 VALUES (@counter, @counter) END
There’s probably better ways, but this is good enough for our test. As you can see, the DoJoin field in tbl1 is set to true when ID is an even number, and false when it’s odd.
So now we have our data, we just need to write the select statement:
SELECT tbl1.ID as tbl1ID, tbl2.ID as tbl2ID FROM tbl1 INNER JOIN tbl2 ON tbl2.tbl1ID = (CASE WHEN tbl1.DoJoin = 1 THEN tbl1.ID ELSE -1 END)
And when we run it all we get these results:
As you can see, the inner join excluded all of the odd rows from returning.
So you can use a case statement in a join clause, but you probably want to ask the question why would you want to use one? In the above trivial example it would be easier and clearer to write the query as:
SELECT tbl1.ID as tbl1ID, tbl2.ID as tbl2ID FROM tbl1 INNER JOIN tbl2 ON tbl2.tbl1ID = tbl1.ID AND tbl1.DoJoin = 1
But maybe there are times when a case statement makes more sense, and if that’s the case then the answer is yes you can use them.