开发者

Which of these two sql joins will perform better?

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


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


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜