better sql join

A bit out of topic but here i can find the right person who explain me...

I have 3 tables: User(ID,Name),Customer(ID,Name),Order(CustomerID,UserID,Qty)

I want to know the difference (i think in performance because are both right) between:

SELECT U.Name, C.Name

FROM User AS U, Customer AS C, Order as O

WHERE C.ID=O.CustomerID AND U.ID=O.UserID

AND U.ID=5

AND C.ID=7

SELECT U.Name, C.Name

FROM User AS U, Customer AS C, Order as O

WHERE C.ID=O.CustomerID AND U.ID=O.UserID

AND O.UserID=5

AND O.CustomerID=7

Thx

Hi Manuel

As far as I can see you second query has mutually exclusive criteria

AND O.ID=5

AND O.ID=7

Did you mean AND ( O.ID=5 OR O.ID=7) ?

And as an additional suggestion, the quesry would be easier to read if it was written with use of INNER JOINs instead of conneting tables on thhe "WHERE" clause. Its behaviour is more consistent.

NB.

|||

ok for innerjoin..

I did an error:

I mean O.CustomerID =5

O.UserID =7

|||Manuel, 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.

Vincent|||

Thx for reply.

Expect Microsoft? Bah!

And if i'm not using SQLServer?

|||? Hi Manuel, Did you try both? Have you checked the execution plans to see if they are any different? I just created a small test (with a similar pattern, but on only two tables) and in my case both execution plans where exactly the same. If you observed different execution plan, I'd like to see some SQL to reproduce it here! -- Hugo Kornelis, SQL Server MVP <manuel0081@.discussions.microsoft.com> schreef in bericht news:5278193f-98a0-47a3-8978-416cf71318d5_WBRev3_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com A bit out of topic but here i can find the right person who explain me... I have 3 tables: User(ID,Name),Customer(ID,Name),Order(CustomerID,UserID,Qty) I want to know the difference (i think in performance because are both right) between: SELECT U.Name, C.Name FROM User AS U, Customer AS C, Order as O WHERE C.ID=O.CustomerID AND U.ID=O.UserID AND U.ID=5 AND C.ID=7 SELECT U.Name, C.Name FROM User AS U, Customer AS C, Order as O WHERE C.ID=O.CustomerID AND U.ID=O.UserID AND O.UserID=5 AND O.CustomerID=7 Thx