SQL inner vs left join question

Question

I have a very badly performing sql query. I tracked it down to the INNER JOIN performed on the table. Changing this to LEFT join significantly increases performance (from 6 min to 20 sec) - now i know the 2 are not equiv, but... here is what i am asking

SELECT * 
  FROM SomeTable ST
  JOIN BigTable BT ON BT.SomeID = ST.SomeID 
                  AND BT.Something = ST.Something 
                  AND BT.AnotherValue = '123'

Since the join has additional criteria (and something=something) -- is changing this to a left join producing the same results - but MUCH faster?

The results returned are the same using LEFT/INNER with left being significantly faster...

Answer 1

It looks like doing the inner join the other way around would give a better performance...

SELECT
    *
FROM
    BigTable AS BT
INNER JOIN
    SomeTable AS ST
ON
    BT.AnotherValue = '123'
AND
    BT.SomeID = ST.SomeID 
AND
    BT.Something = ST.Something

or with subquery

SELECT
    *
FROM
    (SELECT * FROM BigTable WHERE AnotherValue = '123') AS BT
INNER JOIN
    SomeTable AS ST
AND
    BT.SomeID = ST.SomeID 
AND
    BT.Something = ST.Something

Also, make sure the BigTable.AnotherValue is properly indexed.