Join statement performance in SQL

Question

I'm working on SQL Server 2008-R2 and I need to use multiple tables join. In that case, which query has better time performance, when using the WHERE statement or using INNER JOIN with ON statement ? both statements using the AND operator for multi conditions.

I would like to know if there is also some relevant query tuning for that issue.

Sample code for the options above:

1)

SELECT *

FROM T1,T2,T3....

WHERE T1.ID = T2.ID AND

      T1.ID = T3.ID AND

      ....

2)

SELECT *

FROM T1 

     INNER JOIN T2

        ON T1.ID = T2.ID

     INNER JOIN T3

        ON T1.ID = T3.ID

     INNER JOIN .....

Tx.

possible duplicate of [SQL INNER JOIN question](http://stackoverflow.com/questions/3412785/sql-inner-join-question) — Lukas Eder, Apr 11 '11 at 08:02

score 4 · Accepted Answer · edited May 23 '17 at 11:55

4

Quick answer: No, there is no difference in performance.

This is a rather commonly asked question and is explained well here: INNER JOIN ON vs WHERE clause

The query optimization engine in SQL server will automatically convert both of these queries into the same approach for retrieving results.

The biggest difference in performance for your query would be the order of your joins and how much data was filtered with each new join. The most selective join statements (which filter out the most results) should come first (as much as possible depending on the tables you're joining).

edited May 23 '17 at 11:55

Community

1
1

answered Apr 11 '11 at 08:04

Mani Gandham

7,688
1
51
60

the join order is only relevant in combination with the FORCE ORDER query hint – Magnus Apr 11 '11 at 08:37

score 1 · Answer 2 · answered Apr 11 '11 at 08:05

There should be no performance difference between the two, so it would be good to aim for consistency with other queries on the same database.

In cases of doubt, use the "Show actual execution plan" of SQL Server Management Studio to show the relative performance of two queries. Query -> Include Actual Execution Plan

gbn · Answer 3 · 2011-04-11T09:07:10.867

There is no difference in performance.

However, the first style is ANSI-89 and will get your legs broken in some shops. Including mine. The second style is ANSI-92 and is much clearer.

Examples:

Which is the JOIN, which is the filter?

FROM T1,T2,T3....
WHERE T1.ID = T2.ID AND
     T1.foo = 'bar' AND T2.fish = 42 AND
     T1.ID = T3.ID

FROM T1 
   INNER JOIN T2 ON T1.ID = T2.ID
   INNER JOIN T3 ON T1.ID = T3.ID
WHERE
   T1.foo = 'bar' AND T2.fish = 42

If you have OUTER JOINs (=*, *=) then the 2nd style will work as advertised. The first most likely won't and is also deprecated in SQL Server 2005+

The ANSI-92 style is harder to bollix too. With the older style you can easily end up with a Cartesian product (cross join) if you miss a condition. You'll get a syntax error with ANSI-92.

score 0 · Answer 4 · answered Apr 11 '11 at 08:05

Assuming both statements are syntactically identical, you should see absolutely no difference in performance - the query optimizer takes the SQL statement and turns it into a query plan, and the original form of the SQL is sort of irrelevant.

99 times out of 100, the query optimizer gets it completely and utterly right - it used to be possible to confuse the optimizer, but the modern versions of SQL Server are nearly always spot on.

In general, I'd recommend writing your query to be as easy to understand as possible, measure performance if you think you have a problem, and only optimize if you really have to.

Join statement performance in SQL

4 Answers4