CODEWITHSUNDEEP
sqlsql-server
user8528493
user8528493

Reputation: 1

I am trying to streamline or speed up the following query:

create table table_3 as     
select *
from table_1 
   join table_2 on tbl.1.C1 = tbl.2.C1
or tbl.1.C1 = tbl.2.C2 
or tbl.1.C1 = tbl.2.C3
or tbl.1.C1 = tbl.2.C4
or tbl.1.C1 = tbl.2.C5
or tbl.1.C1 = tbl.2.C6

There is a total of 10 columns in table 2 that I need to check. Basically, I want to check table 2 for the table 1 value if table 2 column1 is Null or non-match look in table 2 column2 if null or non-match look in table 2 column3....

Upvotes: 0

Views: 94

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

or in a join statement can kill performance. It is unclear what you really want to do, but the following might product a better optimization plan:

select *
from table_2 t2 join
     table_1 t1
     on t1.C1 = t2.C1
union all
select *
from table_2 t2 join
     table_1 t1
     on t1.c1 = t2.c2 and t1.c1 not in (t2.c1)
union all
select *
from table_2 t2 join
     table_1 t1
     on t1.c1 = t2.c3 and t1.c1 not in (t2.c1, t2.c2)
. . .

Upvotes: 0

xQbert
xQbert

Reputation: 35323

perhaps normalize/unpivot the data then join?

select *
from table_1  t1
join (Select c1 as cNorm, differentID_# from table_2 UNION ALL 
      Select c2, differentID_# from table_2 UNION ALL 
      Select c3, differentID_# from table_2 UNION ALL 
      Select c4, differentID_# from table_2 UNION ALL
      Select c5, differentID_# from table_2 UNION ALL
      Select c6, differentID_# from table_2 UNION ALL
      Select c7, differentID_# from table_2 UNION ALL
      Select c8, differentID_# from table_2 UNION ALL
      Select c9, differentID_# from table_2 UNION ALL
      Select c10, differentID_# from table_2) t2
 on t1.c1 = t2.cNorm

This will match on any values and doesn't progress from c1-c10.

Upvotes: 0

Maurício Pontalti Neri
Maurício Pontalti Neri

Reputation: 1080

I do not know if I understand your query, by you can try it:

CREATE TABLE table_3 AS 
  SELECT COALESCE(B.c1, C.c2, D.c3, E.c4, F.c5, G.c6) 
  FROM   table_1 A 
         LEFT JOIN table_2 B 
                ON A.c1 = B.c1 
         LEFT JOIN table_2 C 
                ON A.c1 = C.c2 
         LEFT JOIN table_2 D 
                ON A.c1 = D.c3 
         LEFT JOIN table_2 E 
                ON A.c1 = E.c4 
         LEFT JOIN table_2 F 
                ON A.c1 = F.c5 
         LEFT JOIN table_2 G 
                ON A.c1 = G.c6

In this code first try find the value of table_2.c1, if it is null try the value of table_2.c2.....

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33571

I am guessing here as to what you are trying to do because your post is not clear. I think you want to join when ANY of a list of columns in table2 match the value in table1. This is kind of a big sign that something is pretty off in your design but the query is easy enough. Something like this.

select *
from Table1 t1
join Table2 t2 on t1.C1 in (t2.C1, t2.C2, t2.C3, t2.C4, t2.C5)

Upvotes: 1

Related Questions