Different Join Values Depending On The Value Of Another Column

I have 2 tables j and c. Both tables have columns port and sec. For j.port = ABC, I want to join the 1st 6 characters of c.sec with the 1st 6 characters of j.sec. For other j.port

Solution 1:

Performance wise breaking this into two may be beneficial. The complex join condition will force nested loops otherwise.

SELECT c.port,
       j.port,
       c.sec,
       j.sec
FROM   j
       JOIN c
         ONLEFT(c.sec, 6) =LEFT(j.sec, 6)
WHERE  j.port ='ABC'UNIONALLSELECT c.port,
       j.port,
       c.sec,
       j.sec
FROM   j
       JOIN c
         ON c.sec = j.sec
WHERE  j.port ISNULLOR j.port <>'ABC'

Or in this specific case you could also do

SELECT c.port,
       j.port,
       c.sec,
       j.sec
FROM   j
       JOIN c
         ONLEFT(c.sec, 6) =LEFT(j.sec, 6)
         and (j.port ='ABC'OR c.sec = j.sec)

This allows the main join to be a simple equi join that can use any of the join algorithms with a residual predicate on the result.

For the following example data both of these took about 700ms on my machine whereas I killed the three competing answers after 30 seconds each as none of them completed in that time.

createtable c(port varchar(10), sec varchar(10)  index ix clustered )  
createtable j(port varchar(10), sec varchar(10))  

INSERTINTO c 
SELECT TOP 1000000LEFT(NEWID(),10) , LEFT(NEWID(),10)
FROM sys.all_objects o1, sys.all_objects o2

INSERTINTO j 
SELECT TOP 1000000LEFT(NEWID(),10) , LEFT(NEWID(),10)
FROM sys.all_objects o1, sys.all_objects o2

Solution 2:

You could use:

select c.port,j.port,c.sec,j.sec 
from j 
join c
  on  (CASEWHEN j.port ='ABC'andSUBSTRING(c.sec,1,6) =SUBSTRING(j.sec,1,6) then1WHEN c.sec = j.sec THEN1END) =1

The same as:

select c.port,j.port,c.sec,j.sec 
from j 
join c
  on (j.port ='ABC'andSUBSTRING(c.sec,1,6) =SUBSTRING(j.sec,1,6))
  or (c.SEC = j.sec AND (j.port <>'ABC'or j.port ISNULL))