CODEWITHSUNDEEP
sqlsql-servert-sql
Eric
Eric

Reputation: 8088

Any alternatives to key word 'or' in an sql statement?

I probably didn't word the question right buy I have a query that is taking a substantial amount of time because of 'or'.

select stuff 
from table1 T1
left join table2 T2 on T2.field1 = T1.field or T2.field2 = T1.field
where some condition

I have to check both fields to join. Is there a better way to do this?

Upvotes: 3

Views: 98

Answers (3)

AsifQadri
AsifQadri

Reputation: 2389

If you follow the rule either T2.field1 = T1.field true or T2.field2 = T1.field. when output depends on atleast one is true.

**select stuff 
from table1 T1, table2 T2 
where some condition AND (T2.field1 = T1.field or T2.field2 = T1.field)**

Upvotes: 2

Alin P.
Alin P.

Reputation: 44376

You could use T1.field IN (T2.field1, T2.field2) but I doubt it will improve your query performance.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332751

There's two options I can think of based on what's provided:

Use IN instead

   select stuff 
     from table1 T1
left join table2 T2 on T1.field IN (T2.field1, T2.field2)
    where some condition

Using a UNION

Mind that I used UNION ALL--it's faster than UNION, but UNION removes duplicates so alter to suit your needs:

   select stuff 
     from table1 T1
left join table2 T2 on T1.field = T2.field1
    where some condition
UNION ALL
   select stuff 
     from table1 T1
left join table2 T2 on T1.field = T2.field2
    where some condition

Upvotes: 3

Related Questions