Which of these two sql joins will perform better?

Question

I've always been curious of which method of SQL joining I should be using. The following two queries perform exactly the same function, which of the two is better?

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
INNER JOIN Orders o
ON p.P_Id = o.P_Id

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p, Orders o
WHERE p.P_Id = o.P_Id

In summary, does using the words INNER JOIN actually perform better than 'WHERE x = y' ?

No they will have the same execution plan in any sensible RDBMS — Martin Smith, Sep 02 '11 at 09:56
possible duplicate of [Is a JOIN faster than a WHERE?](http://stackoverflow.com/questions/1129923/is-a-join-faster-than-a-where) — Martin Smith, Sep 02 '11 at 09:57
You can easily find that out yourself by running them and looking at the execution plan (I'm actually tempted to downvote the question because of that) — a_horse_with_no_name, Sep 02 '11 at 09:57

score 1 · Answer 1 · answered Sep 02 '11 at 09:56

1

It doens't matter. The good DBMS optimises it to the same code, essentially making them the same.

answered Sep 02 '11 at 09:56

Rok Kralj

43,948
10
67
80

Shantanu Gupta · Accepted Answer · 2011-09-02T10:08:22.827

Case 1 is the ANSI standard version that is used in sql server while Case 2 was the syntax that was used earlier and is depreciated. Go through this

SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p INNER JOIN Orders o ON p.P_Id = o.P_Id  

SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p, Orders o WHERE p.P_Id = o.P_Id

Hence performance of both the query will be same

Please note there if your query had been little different. i.e.

SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p LEFT JOIN Orders o ON p.P_Id = o.P_Id AND p.Id = 1

Vs

 SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p LEFT JOIN Orders o ON p.P_Id = o.P_Id WHERE p.Id = 1

In this case query will work completely different. Output of both the queries will be different.

For understanding this...See this

Which of these two sql joins will perform better?

2 Answers2