1

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
Dale K
  • 25,246
  • 15
  • 42
  • 71
faujong
  • 949
  • 4
  • 24
  • 40

2 Answers2

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
         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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
0

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))
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Neither of these can use merge or hash join which means the only alternative for the optimiser is nested loops with an unsargable predicate on the inner table – Martin Smith Apr 24 '19 at 19:56
  • That is true. SUBSTRING is not sarg-able at all and using `OR expansion` will help at least with second part as you did. – Lukasz Szozda Apr 24 '19 at 19:58
  • Thank you all for your help. One more question: Is it possible to do the query if j.port = 'ABC', JOIN ON c.sec = j.sec, and if it can't find anything then JOIN ON LEFT(c.sec, 6) = LEFT(j.sec, 6) ? – faujong Apr 24 '19 at 20:37
  • @faujong yes, thats possible. you should ask a new question about that. – Martin Smith Apr 24 '19 at 20:40
  • Hi, I have posted a new question https://stackoverflow.com/questions/55838361/query-on-1-column-but-if-it-doesnt-find-anything-query-on-first-6-characters. Thank you for your help – faujong Apr 24 '19 at 20:51