Saturday, November 17, 2012 4:21 PM
Saturday, November 17, 2012 4:31 PM
If the all the tables have necessary indexes and relationship defined, there would not be any difference.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Saturday, November 17, 2012 4:36 PMModerator
This depends on a many things, but it is likely that they will not perform the same. Something that you didn't mention was whether or not you want records to be repeated. If records should not be repeated, then I would change this query completely; hang on and I will get an example.
My guess would be that you filter based on the Orders table for best peformance, but there are many issues going on. Hang on and I will continue this response in a few minutes.
Some of the issues:
- Whether or not the Order table has an index that includes (1) UserId and (2) CustomerId
- Whether or not the Customer table has an index based on (1) ID and possibly (2) Name
- Whether or not the User table has an index based on (1) ID and possibly (2) name
- The percentage of records in the User table that have ID = 5
- The percentage of records in the Customer table that have ID = 7
- The percentage of records in the Order table that have UserId = 5 and CustomerId = 7
- Whether or not records should be repeated
If records should not be repeated, I suggest something like this:
select u.Name, c.Name from ( select top 1 userId, customerId from order where userId = 5 and customerId = 7 ) o join user u on u.id = o.userId join customer c on c.id = o.customerId
You need to elaborate a bit on exactly what you are trying to do.
After thinking about this briefly, I think Lateesh is probably correct about it not making much difference provided all the indexes are present.
- Edited by Kent WaldropMicrosoft Community Contributor, Moderator Saturday, November 17, 2012 5:04 PM
Saturday, November 17, 2012 6:50 PM
please read the below article hope you will get your answer:
Difference between On clause and Where clause when used with left join in SQL Server
Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/
Sunday, November 18, 2012 4:56 AMModerator
I suggest to use JOIN instead of WHERE to join tables and use where to restrict information. So, I would re-write your query this way:
SELECT U.Name, C.Name FROM dbo.Customer AS C INNER JOIN dbo.[Order] AS O ON C.Id = O.CustomerID INNER JOIN dbo.User AS U ON O.UserID = U.ID WHERE U.ID=5 AND C.ID=7
For every expert, there is an equal and opposite expert. - Becker's Law
Sunday, November 18, 2012 8:12 AMModerator
in the case of:
select * from A, B where A.col1 = B.col1
if A is smaller and has a good index, I think SQL Server will chose A as the inner table. Because of this we should put our restrictive where clause on B, like this:
select * from A, B where A.col1 = B.col1 and B.col2 = 5
That will have a better performance than putting the restrictive clause on A like this:
select * from A, B where A.col1 = B.col1 and A.col2 = 5
As a general rule, I normally put the restrictive where clause on the foreign key (FK)table, not on the primary key (PK) table. This is because I think SQL Server will choose the PK table as the inner table. Somebody from Microsoft SQL Server development team would be able to explain more about SQL Server's nested loop join plan strategy.
To understand what is inner table and outer table, I would recommend you to read page 90 of Peter Gulutzan's book, SQL Performance Tuning. Basically, the table size affect number of page read from disk. If the table is small, there is more chance to put all the pages in the cache.
TechNet Community Support
- Proposed As Answer by Kalman TothMicrosoft Community Contributor, Moderator Sunday, November 18, 2012 9:30 AM
- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Saturday, November 24, 2012 12:21 PM