Can you use a case statement as part of a join?

By Greg No comments

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:

tbl1ID tbl2ID
2 2
4 4
6 6
8 8
10 10
12 12
14 14
16 16
18 18
20 20

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.

Leave a Reply