{"id":2077,"date":"2015-09-07T00:00:00","date_gmt":"2015-08-24T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/how-to-avoid-conditional-joins-in-t-sql\/"},"modified":"2026-03-18T14:16:24","modified_gmt":"2026-03-18T14:16:24","slug":"how-to-avoid-conditional-joins-in-t-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/performance-sql-server\/how-to-avoid-conditional-joins-in-t-sql\/","title":{"rendered":"SQL Conditional JOIN: Why It’s Slow & What to Use Instead"},"content":{"rendered":"
\n

A conditional JOIN – where you use CASE in the ON clause to choose which column to join on based on a condition – is a T-SQL anti-pattern that causes significant performance problems. The query optimizer cannot use index seeks efficiently when the join predicate is wrapped in a CASE expression, typically resulting in full table scans. Better alternatives include using LEFT OUTER JOIN to all possible tables with CASE in the SELECT (simplest), UNION ALL of separate targeted queries (best performance), or CROSS APPLY with a subquery (most flexible). This article benchmarks all three approaches against the conditional JOIN pattern using one million rows.<\/p>\n

Introduction<\/h2>\n

Just because certain SQL JOINs are possible doesn’t mean that they are a good option.\u00a0 The ‘conditional JOIN’ is one of these.<\/p>\n

So what is a conditional join? This is where one key in a table is used\u00a0 to join either with one table or others, depending on some criterion. It sounds extraordinary, but it can happen, particularly in highly complex, sometimes auto-generated queries. It usually represents an attempt to use a polymorphic association between tables, and is a SQL Code Smell<\/a>.<\/p>\n

The term \u00a0‘Conditional JOIN’ is somewhat ambiguous, in that people refer to different problems when they use it.\u00a0 It seems as if programmers wish to either JOIN a table to more than one other table or to JOIN a column in a table to different columns in a second table, choosing the JOIN column based on a condition.<\/p>\n

Since T-SQL \u00a0has no syntax that would allow for putting a table name into a CASE statement, the first definition of the conditional JOIN really has no means to resolve other than to simply JOIN all the tables (likely as LEFT OUTER JOINs) and use CASE statements to pull the specific data item(s) required from the secondary tables as appropriate.<\/p>\n

Recently I was writing a query that could be resolved using the second case.\u00a0 While I thought about several other ways to do it, from a coding standpoint using a CASE statement in the ON clause of the JOIN resulted in the simplest syntax to make the query work.\u00a0 However, was syntax that looked simple the most efficient way of doing it?<\/p>\n

Let’s face it, sometimes in programming you don’t have the luxury of standing back and redesigning the system to avoid the requirement, but it is always best if we can optimize the performance of the SQL that meets the requirement.<\/p>\n

Sample Data<\/h2>\n

We’ll start with a simple example to illustrate a conditional join.\u00a0 This code creates two tables, with one million rows of test data in the secondary table that we’ll be doing the conditional JOIN to.<\/p>\n

CREATE TABLE dbo.SampleLeftTable\n(\n\u00a0\u00a0\u00a0 ID\u00a0\u00a0\u00a0\u00a0\u00a0 INT IDENTITY PRIMARY KEY\n\u00a0\u00a0\u00a0 ,num\u00a0\u00a0\u00a0 INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL\n);\n\u00a0\nINSERT INTO dbo.SampleLeftTable (num)\nVALUES(21),(44),(53),(78);\n\u00a0\nCREATE TABLE dbo.ConditionalJoinExample\n(\n\u00a0\u00a0\u00a0 ID\u00a0\u00a0\u00a0\u00a0\u00a0 INT IDENTITY PRIMARY KEY\n\u00a0\u00a0\u00a0 ,N1\u00a0\u00a0\u00a0\u00a0 INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL\n\u00a0\u00a0\u00a0 ,N2\u00a0\u00a0\u00a0\u00a0 INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL\n\u00a0\u00a0\u00a0 ,N3\u00a0\u00a0\u00a0\u00a0 INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL\n\u00a0\u00a0\u00a0 ,N4\u00a0\u00a0\u00a0\u00a0 INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL\n);\n\u00a0\nWITH Tally(n) AS \n(\n\u00a0\u00a0\u00a0 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))\n\u00a0\u00a0\u00a0 FROM(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- 10 rows\n\u00a0\u00a0\u00a0 CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x10 rows\n\u00a0\u00a0\u00a0 CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x10 rows\n\u00a0\u00a0\u00a0 CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n) -- x10 rows\n\u00a0\u00a0\u00a0 CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n) -- x10 rows\n\u00a0\u00a0\u00a0 CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f(n) -- x10 rows\n)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- = 1M rows\nINSERT INTO dbo.ConditionalJoinExample\n(\n\u00a0\u00a0\u00a0 N1, N2, N3, N4\n)\n-- Populate each column with a random number in the range {1,100}\nSELECT N1\u00a0\u00a0 = 1+ABS(CHECKSUM(NEWID()))%100\n\u00a0\u00a0\u00a0 ,N2\u00a0\u00a0\u00a0\u00a0 = 1+ABS(CHECKSUM(NEWID()))%100\n\u00a0\u00a0\u00a0 ,N3\u00a0\u00a0\u00a0\u00a0 = 1+ABS(CHECKSUM(NEWID()))%100\n\u00a0\u00a0\u00a0 ,N4\u00a0\u00a0\u00a0\u00a0 = 1+ABS(CHECKSUM(NEWID()))%100\nFROM Tally;\n\u00a0\n\u00a0\nGO\n--DROP TABLE dbo.SampleLeftTable;\n--DROP TABLE dbo.ConditionalJoinExample;\nHere are the results.\nSELECT *\nFROM dbo.SampleLeftTable;\n\u00a0\nID\u00a0 num\n1\u00a0\u00a0 21\n2\u00a0\u00a0 44\n3\u00a0\u00a0 53\n4\u00a0\u00a0 78\n\u00a0\nSELECT TOP 10 *\nFROM dbo.ConditionalJoinExample;\n\u00a0\nID\u00a0 N1\u00a0\u00a0 N2\u00a0\u00a0 N3\u00a0\u00a0 N4\n1\u00a0\u00a0 19\u00a0\u00a0 30\u00a0\u00a0 1\u00a0\u00a0\u00a0 66\n2\u00a0\u00a0 61\u00a0\u00a0 92\u00a0\u00a0 69\u00a0\u00a0 51\n3\u00a0\u00a0 9\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0 20\u00a0\u00a0 74\n4\u00a0\u00a0 81\u00a0\u00a0 78\u00a0\u00a0 76\u00a0\u00a0 79\n5\u00a0\u00a0 62\u00a0\u00a0 100\u00a0 37\u00a0\u00a0 75\n6\u00a0\u00a0 59\u00a0\u00a0 83\u00a0\u00a0 58\u00a0\u00a0 43\n7\u00a0\u00a0 89\u00a0\u00a0 70\u00a0\u00a0 76\u00a0\u00a0 30\n8\u00a0\u00a0 40\u00a0\u00a0 11\u00a0\u00a0 85\u00a0\u00a0 91\n9\u00a0\u00a0 97\u00a0\u00a0 16\u00a0\u00a0 67\u00a0\u00a0 84\n10\u00a0 22\u00a0\u00a0 50\u00a0\u00a0 74\u00a0\u00a0 30\n<\/pre>\n
You can see that the four data columns (N1, N2, N3 and N4) contain random numbers in the range 1 to 100.\u00a0 We’ve selected the TOP 10 rows just to illustrate, and if you’re following along you’ll get different but similar results in the second results set.\u00a0 The commented-out DROPs are provided to clean up your sandbox later if you want to run these examples on your server.\u00a0 Note that we’ll be running them using SQL 2012, but SQL 2008 or SQL 2005 can also be used.<\/p>\n
Scenario 1: Conditional JOIN Based on Data in the Left Table<\/h2>\n
Like this article? Check out these below:
Identifying and solving index scan problems<\/a>
SQL Server deadlocks by example<\/a>
Computed column performance in SQL Server<\/a>
Using subqueries in a T-SQL SELECT statement<\/a>

Now suppose we have a business requirement that states we want to perform a JOIN from the left table (4 rows) to the secondary table based on the range that the value in the left table falls into.\u00a0 Such a JOIN may look like this.<\/p>\n
SELECT a.ID, num, b.ID, N1, N2, N3, N4\nFROM dbo.SampleLeftTable a\nJOIN dbo.ConditionalJoinExample b\nON num = CASE\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN num BETWEEN 1 AND 25 THEN b.N1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN num BETWEEN 26 AND 50 THEN b.N2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN num BETWEEN 51 AND 75 THEN b.N3\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE b.N4\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END;\n<\/pre>\n
And it displays results that look like this (only the first five rows are shown, out of about 40,000 returned).<\/p>\n
ID\u00a0\u00a0 num\u00a0\u00a0 ID\u00a0\u00a0 N1\u00a0\u00a0 N2\u00a0\u00a0 N3\u00a0\u00a0 N4\n3\u00a0\u00a0\u00a0 53\u00a0\u00a0\u00a0 23\u00a0\u00a0 52\u00a0\u00a0 70\u00a0\u00a0 53\u00a0\u00a0 4\n3\u00a0\u00a0\u00a0 53\u00a0\u00a0\u00a0 72\u00a0\u00a0 48\u00a0\u00a0 49\u00a0\u00a0 53\u00a0\u00a0 3\n3\u00a0\u00a0\u00a0 53\u00a0\u00a0\u00a0 227\u00a0 89\u00a0\u00a0 81\u00a0\u00a0 53\u00a0\u00a0 1\n3\u00a0\u00a0\u00a0 53\u00a0\u00a0\u00a0 269\u00a0 19\u00a0\u00a0 28\u00a0\u00a0 53\u00a0\u00a0 77\n3\u00a0\u00a0\u00a0 53\u00a0\u00a0\u00a0 393\u00a0 83\u00a0\u00a0 72\u00a0\u00a0 53\u00a0\u00a0 78\n<\/pre>\n
You can see that because the value of Num of the row in the left table is 53, it is matching on the third N column (N3) in the table JOINed to.<\/p>\n
I was interested in TIME and IO STATISTICS so I ran that query with them on and we got these results.<\/p>\n
(40104 row(s) affected)\n\u00a0\nTable 'SampleLeftTable'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'ConditionalJoinExample'. Scan count 4, logical reads 14400, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 11676892, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\n\u00a0\n\u00a0SQL Server Execution Times:\n\u00a0\u00a0 CPU time = 20015 ms,\u00a0 elapsed time = 5617 ms.\n<\/pre>\n
While the query only took about five and a half seconds to run, it looked suspiciously expensive in terms of CPU.\u00a0 The query plan for it was this.<\/p>\n
\"2270-clip_image002.jpg\"<\/a><\/p>\n
First off, we should not be surprised to see a Parallelism operator in the plans, because from our timing results we see that CPU time used is way in excess of the elapsed time for the query.<\/p>\n
We also see that both tables are using a Clustered Index Scan operator, but when we look a little deeper into what is going on with the right table (by expanding the details of the Table Spool and Clustered Index Scan operators), we see that they’re trying to operate on four million (actual) rows of data, when there are only one million rows of data in the table!<\/p>\n
Our first thought of course is that perhaps we can build an INDEX to speed this bad boy up a bit.\u00a0 So let’s try that.<\/p>\n
CREATEINDEX ix5ON dbo.ConditionalJoinExample(N1,N2,N3,N4);<\/pre>\n
When we run that same query again, we get these timing results.<\/p>\n
SQL Server parse and compile time: \n\u00a0\u00a0 CPU time = 0 ms, elapsed time = 2 ms.\n\u00a0\n(40104 row(s) affected)\n\u00a0\nTable 'SampleLeftTable'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'ConditionalJoinExample'. Scan count 4, logical reads 12928, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 11676892, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\n\u00a0\n\u00a0SQL Server Execution Times:\n\u00a0\u00a0 CPU time = 20279 ms,\u00a0 elapsed time = 5949 ms.\n<\/pre>\n
And they are not very impressive, being of approximately the same order of magnitude as the first.\u00a0 As to the query plan:<\/p>\n
\"2270-clip_image004.jpg\"<\/a><\/p>\n
We see that while the INDEX we created is being used, it didn’t seem to help much.\u00a0 If you repeat this query and hover your cursor over the Index Scan (NonClustered) and Table Spool operators in the graphical query plan, you will find that the same four million actual rows were being used in the query.<\/p>\n
It is definitely time to rewrite this query and make it run faster by finding an alternative to the conditional join.<\/p>\n
Instead of our conditional JOIN, we can partition the big table into four parts, doing successive JOINs on each of the partitions, and then recombining the parts like this (using UNION ALL):<\/p>\n
-- Drop the annoyingly unhelpful INDEX\nDROP INDEX ix5 ON dbo.ConditionalJoinExample;\n\u00a0\nSELECT a.ID, num, b.ID, N1, N2, N3, N4\nFROM dbo.SampleLeftTable a\nJOIN dbo.ConditionalJoinExample b\nON num = b.N1\nWHERE num BETWEEN 1 AND 25\nUNION ALL\nSELECT a.ID, num, b.ID, N1, N2, N3, N4\nFROM dbo.SampleLeftTable a\nJOIN dbo.ConditionalJoinExample b\nON num = b.N2\nWHERE num BETWEEN 26 AND 50\nUNION ALL\nSELECT a.ID, num, b.ID, N1, N2, N3, N4\nFROM dbo.SampleLeftTable a\nJOIN dbo.ConditionalJoinExample b\nON num = b.N3\nWHERE num BETWEEN 51 AND 75\nUNION ALL\nSELECT a.ID, num, b.ID, N1, N2, N3, N4\nFROM dbo.SampleLeftTable a\nJOIN dbo.ConditionalJoinExample b\nON num = b.N4\nWHERE num BETWEEN 76 AND 100;\n<\/pre>\n
While that appears to be a much more complex query (it certainly takes a lot longer to write even using copy and paste), we are pretty impressed by the timing results:<\/p>\n
(40104 row(s) affected)\n\u00a0\nTable 'SampleLeftTable'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'ConditionalJoinExample'. Scan count 20, logical reads 14600, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\n\u00a0\n\u00a0SQL Server Execution Times:\n\u00a0\u00a0 CPU time = 249 ms,\u00a0 elapsed time = 389 ms.\n<\/pre>\n
While this query is now running in less than half a second (with CPU time down by two orders of magnitude), it does produce a much more (seemingly) complex query plan.<\/p>\n
\"2270-clip_image006.jpg\"<\/a><\/p>\n
Even though SQL is doing four Clustered Index Scans of the big table, the results are overwhelmingly in favor of this query over the conditional JOIN.<\/p>\n
SQL 2012 grumbled somewhat that an INDEX was missing that could improve the query a bit, so let’s go ahead and create the INDEX that it recommends and try again.<\/p>\n
-- Add the recommended INDEX\nCREATE NONCLUSTERED INDEX ix1 ON dbo.ConditionalJoinExample (N4) INCLUDE (ID, N1, N2, N3);\n<\/pre>\n
Now we get these timing results:<\/p>\n
(40104 row(s) affected)\n\u00a0\nTable 'SampleLeftTable'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'ConditionalJoinExample'. Scan count 16, logical reads 9807, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\n\u00a0\n\u00a0SQL Server Execution Times:\n\u00a0\u00a0 CPU time = 265 ms,\u00a0 elapsed time = 398 ms.\n<\/pre>\n
It looks to me like that recommended INDEX didn’t help one bit!\u00a0 And I did in fact check the query plan and found that the optimizer used the recommended INDEX.<\/p>\n
From my perspective I’d say that the rewrite worked sufficiently that I wouldn’t hesitate to use it in production without the recommended INDEX, thus saving the space that building that INDEX would entail, not to mention the overhead having that INDEX would impose on INSERT, UPDATE and DELETE statements.<\/p>\n
I should also point out that in our first (conditional JOIN) attempt, we were working with columns (N1, …, N4) that were all of the same data type.\u00a0 If in your case, you are not, there are likely additional performance detractors in getting them to the same data type, that you should watch for and consider.<\/p>\n
Let’s drop that INDEX before we proceed with our next scenario.<\/p>\n
DROP INDEXix1 ONdbo.ConditionalJoinExample;<\/pre>\n
Scenario 2: Conditional JOIN Based on an External Parameter<\/h2>\n
Sometimes you may have a SQL problem that makes you believe that you need to drive the conditional JOIN key column by a switch of some sort, perhaps one that is passed in as a parameter in a stored procedure.\u00a0 Let’s look at a simplified example that does not employ a stored procedure:<\/p>\n
DECLARE @Switch INT = 1;\n\u00a0\nSELECT a.ID, num, b.ID, N1, N2, N3, N4\nFROM dbo.SampleLeftTable a\nJOIN dbo.ConditionalJoinExample b\nON num = CASE @Switch\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 1 THEN b.N1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 2 THEN b.N2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 3 THEN b.N3\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE b.N4\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END;\n<\/pre>\n
Here the @Switch local variable controls the column we’ll JOIN on.\u00a0 Timing results look suspiciously similar to the first case we explored.<\/p>\n
(40156 row(s) affected)\n\u00a0\nTable 'SampleLeftTable'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'ConditionalJoinExample'. Scan count 4, logical reads 14400, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 11676892, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\n\u00a0\n\u00a0SQL Server Execution Times:\n\u00a0\u00a0 CPU time = 19297 ms,\u00a0 elapsed time = 5284 ms.\n<\/pre>\n
Indeed, the query plan, which we won’t show here, looks remarkably similar to the first query plan we showed, right down to the four million actual row counts in the Clustered Index Scan and Table Spool operators.<\/p>\n
Using the identical refactoring approach:<\/p>\n
DECLARE @Switch INT = 1;\n\u00a0\nSELECT a.ID, num, b.ID, N1, N2, N3, N4\nFROM dbo.SampleLeftTable a\nJOIN dbo.ConditionalJoinExample b\nON num = b.N1\nWHERE @Switch = 1\nUNION ALL\nSELECT a.ID, num, b.ID, N1, N2, N3, N4\nFROM dbo.SampleLeftTable a\nJOIN dbo.ConditionalJoinExample b\nON num = b.N2\nWHERE @Switch = 2\nUNION ALL\nSELECT a.ID, num, b.ID, N1, N2, N3, N4\nFROM dbo.SampleLeftTable a\nJOIN dbo.ConditionalJoinExample b\nON num = b.N3\nWHERE @Switch = 3\nUNION ALL\nSELECT a.ID, num, b.ID, N1, N2, N3, N4\nFROM dbo.SampleLeftTable a\nJOIN dbo.ConditionalJoinExample b\nON num = b.N4\nWHERE @Switch NOT IN (1,2,3);\n<\/pre>\n
We end up with a much more complicated looking query (with a similar, much more complicated query plan), that runs lickety-split!<\/p>\n
(40156 row(s) affected)\n\u00a0\nTable 'SampleLeftTable'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'ConditionalJoinExample'. Scan count 5, logical reads 3650, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\n\u00a0\n\u00a0SQL Server Execution Times:\n\u00a0\u00a0 CPU time = 108 ms,\u00a0 elapsed time = 324 ms.\n<\/pre>\n
Now, if you were to be passing the @Switch variable into a stored procedure, it is quite possible that you could run into parameter sniffing issues in either or both of these cases.\u00a0 But we won’t explore the details here (nor how to resolve them), as they’ve been covered pretty thoroughly by  SQL MVP Erland Sommarskog<\/a> in his excellent blog<\/a> on this subject “Slow in the Application, Fast in SSMS?<\/a>“<\/p>\n
Conclusions<\/h2>\n
You can never be certain that code that looks elegant on-screen will be executed quickly. The query that uses a conditional JOIN may seem to solve the problem, but it is worth \u00a0exploring alternatives to make sure you don’t run into performance issues when your data grows large.<\/p>\n
Some other lessons learned from the two scenarios we explored in this article are:<\/p>\n