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...

This is bizarre, since in most cases INNER JOIN performs better. An OUTER JOIN must return rows even where there isn't a match. Are you sure there's not more to this than the join type? — Yuck
– Yuck, Commented Jun 30, 2011 at 0:42
YUP, im confused as ever - the logical reads drops by millions (many millions) — user822150
– user822150, Commented Jun 30, 2011 at 0:42
Have you checked the execution plan to see if anything else is happening behind the scenes? — George Johnston
– George Johnston, Commented Jun 30, 2011 at 0:44

shinkou · Accepted Answer · 2011-06-30 00:55:27Z

2

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.

answered Jun 30, 2011 at 0:55

shinkou

5,1431 gold badge24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

N. Warfield · Accepted Answer · 2011-06-30 00:57:06Z

0

While you're getting the same results from both joins its important to understand that a left join is different from an inner join.

A left join will take all rows from the left table even if there are no matches in the right table.

SQL LEFT JOIN vs. SQL INNER JOIN

So your data just happens to be produced in a manner that is identical given the two different joins.

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

How about this:

SELECT
   *
FROM
   SomeTable
      INNER JOIN
   BigTable
      ON SomeTable.PreferedPrimaryKey = BigTable.PreferAForeignKey
      AND SomeTable.SomethingThatIsIndexedAndPreferableNumeric = BigTable.SomethingThatIsIndexedAndPreferableNumeric 
WHERE
   BigTable.AnotherValue = '123'

Check your indexes and make sure your criteria for the second part of the join isn't a non-indexed character string.

answered Jun 30, 2011 at 0:57

N. Warfield

3462 silver badges14 bronze badges

4 Comments

user194076 Over a year ago

You don't use a w3schools as a trusted source. Never. That's what they told me.

JeffO Over a year ago

I don't know where you heard this about w3schools, but sql joins have been around for decades and implemented in several databases. They probably got this right.

ypercubeᵀᴹ Over a year ago

INNER JOIN is different to LEFT JOIN but they don't always give different results. If every row of the left side table has a match in the right side table, then they are equivalent.

N. Warfield Over a year ago

I agree, that's what I stated.

Collectives™ on Stack Overflow

SQL inner vs left join question

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related