Internal and internal SQL issue

Question

I have a very poorly executed sql query. I tracked it down to the INNER JOIN executed on the table. Changing this to a LEFT connection significantly increases performance (from 6 minutes to 20 seconds) - now I know that 2 is not equiv, but ... that's what I ask

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

Since the connection has additional criteria (and something = something), this changes it to the left connection, producing the same results - but MUCH faster?

The returned results are the same using LEFT / INNER, and the left speed is much faster ...

Answer 1

It seems that the inner join, on the contrary, will give 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 a 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 BigTable.AnotherValue properly indexed.

Answer 2

As long as you get the same results from both joins, it’s important to understand that the left join is different from the inner join.

The left join will accept all rows from the left table, even if there is no match in the right table.

SQL LEFT JOIN vs SQL INNER JOIN

This way, your data is simply produced the same, given two different associations.

 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 is not an unindexed character string.