Hi, Sergey.
Thank you for your time.
Here is an example from BOL.

SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1 = tab4.c1
ON tab2.c3 = tab4.c3

Here you see one not nested and two nested joins. If the oprimizer makes most of the decisions then why come up with a consept of a nested join at all?
If, in a nested join (the above example), the joins are processed from the bottom up, then first Tab4 will be joined ON Tab3.C1=Tab4.C1. Next Tab3 will be joined ON Tab2.C3=Tab4.C3, how come the table is joined on condition which doesn't involve the table? Is it for giggles?
I've never plunged into detailed discussion on a complex topic of SELECT so maybe i'm missing something, but i'm still convinced that there should be some basic rules of the SELECT result building.

All the best.
Kamil


>Kamil,
>
>There're three separate issues here:
>#1 How to build a query that produces desired result
>#2 What execution plan query optimizer would use to process the query
>#3 How optimize the query
>
>#1. It doesn't matter if JOIN or WHERE will execute first, the result will be the same. Here're two queries that, I belive, are equivalent of your query

SELECT Pc1.*, Place1.User_Na, Place1.Dep_Pk,
>		Dep.Na AS Dep_Na
>	FROM Pc1
>	JOIN Place1 ON Pc1.Place1_Pk=Place1.pk
>	JOIN Dep ON Dep.pk=Place1.Dep_Pk
>	WHERE Place1.pk IN (SELECT Place1_Pk FROM Place1_Po1))
>	ORDER BY User_Na
>
>SELECT Pc1.*, Place1.User_Na, Place1.Dep_Pk,
>		Dep.Na AS Dep_Na
>	FROM Pc1
>	JOIN Place1 ON Pc1.Place1_Pk=Place1.pk
>	JOIN Dep ON Dep.pk=Place1.Dep_Pk
>	JOIN Place1_Po1 ON Place1_Po1.Place1_Pk = Place1.pk
>	ORDER BY User_Na
>

>
>#2. I covered in my previous message. Most likely the two queries from #1 will produce the same excution plan in SQL Server. Anyway, the execution plan selected doesn't affect the query result only performance.
>
>#3. SQL Server query optimizer does pretty good job in designing most effective execution plan in most cases. It's not recomended to specify optimization hints w/o good reason because most likely the execution plan will be less effective.
>