CODEWITHSUNDEEP
sqlsql-serverjoininner-join
faujong
faujong

Reputation: 1127

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.ports, I want to join c.sec = j.sec

How can I do that ?

select c.port,j.port,c.sec,j.sec from j, c
where  c.SEC = 
   CASE WHEN j.port = 'ABC' then SUBSTRING(c.sec,1,6) = SUBSTRING(j.sec,1,6)  
   --> something like this
   else j.sec

Upvotes: 1

Views: 122

Answers (2)

Martin Smith
Martin Smith

Reputation: 453328

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
         ON LEFT(c.sec, 6) = LEFT(j.sec, 6)
WHERE  j.port = 'ABC'
UNION ALL
SELECT c.port,
       j.port,
       c.sec,
       j.sec
FROM   j
       JOIN c
         ON c.sec = j.sec
WHERE  j.port IS NULL
        OR 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
         ON LEFT(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.

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

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

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

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

You could use:

select c.port,j.port,c.sec,j.sec 
from j 
join c
  on  (CASE WHEN j.port = 'ABC' and SUBSTRING(c.sec,1,6) = SUBSTRING(j.sec,1,6) then 1
           WHEN c.sec = j.sec THEN 1
      END) = 1

The same as:

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

Upvotes: 0

Related Questions