-4

I am trying to join two tables on two different columns and I was wondering if following two techniques are equivalent, if yes which one is better performance wise?

JOIN with OR (Conditional JOIN)

SELECT *
FROM table1
JOIN TABLE2 ON table1.value = table2.HighValue
OR table1.value = table2.LowValue

Using self Join

SELECT *
FROM TABLE1
JOIN table2 t2 ON table1.value = t2.HighValue
JOIN table2 t3 ON table1.value = t3.LowValue
StayOnTarget
  • 11,743
  • 10
  • 52
  • 81
Aura
  • 1,283
  • 2
  • 16
  • 30
  • 6
    These queries do different things, so you should choose the one that does what you want. Sample data and desired results would help. – Gordon Linoff Aug 22 '18 at 14:41

1 Answers1

2

The two queries are not equivalent, the first is equivalent to

SELECT *
FROM table1
JOIN TABLE2 ON table1.value = table2.HighValue

UNION 

SELECT *
FROM table1
JOIN TABLE2 ON table1.value = table2.LowValue
user1443098
  • 6,487
  • 5
  • 38
  • 67
  • Thanks @user1443098. That's helpful. I will try it in my query. – Aura Aug 22 '18 at 14:45
  • 1
    The UNION operator removes duplicated, should there be any. If that is not desired, use UNION ALL. If you don't care or are certain that there are no duplicates, also use UNION ALL for performance reasons. – SQL_M Aug 22 '18 at 14:51