2

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' ?

andrewm
  • 2,552
  • 5
  • 33
  • 63
  • 2
    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
  • 1
    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) –  Sep 02 '11 at 09:57

2 Answers2

1

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

Rok Kralj
  • 46,826
  • 10
  • 71
  • 80
1

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

Shantanu Gupta
  • 20,688
  • 54
  • 182
  • 286