Prev: Assign permissions t user defined functions
Next: Join Syntax multiple on
From: tshad on 29 Mar 2010 16:05
If you have the following:

USE AdventureWorks;
GO
SELECT C.ContactID,
C.FirstName,
C.LastName,
SP.SalesPersonID,
SP.CommissionPct,
SP.SalesYTD,
SP.SalesLastYear,
SP.Bonus,
ST.TerritoryID,
ST.Name,
ST.[Group],
ST.SalesYTD
FROM Person.Contact C
INNER JOIN Sales.SalesPerson SP
ON C.ContactID = SP.SalesPersonID
LEFT OUTER JOIN Sales.SalesTerritory ST
ON ST.TerritoryID = SP.TerritoryID
ORDER BY ST.TerritoryID, C.LastName
GoIs the left table for the LEFT OUTER JOIN considered the Sales.SalesPerson
table or is it the table that results from the: Person.Contact C
INNER JOIN Sales.SalesPerson SP?
In other words to I get all the rows from the Sales.SalesPerson table or all
the matched rows from the above joined Person.Contact and Sales.SalesPerson
table?I seem to remember someone saying that the joins are the results of
all the previous joins, not just the table to the left of the JOIN
statement.Thanks,Tom


From: Plamen Ratchev on 29 Mar 2010 16:59
When you have outer join, the preceding virtual table is the table considered in the join. Virtual table is the result
set from the prior joins. In your case, the result from the inner join will be the virtual result table that will be the
preserved table in the outer join. That means only rows that satisfy the inner join predicates will be considered the
virtual table for the outer join.

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 29 Mar 2010 17:40

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:qbednTrjT_IDiSzWnZ2dnUVZ_o-dnZ2d(a)speakeasy.net...
> When you have outer join, the preceding virtual table is the table
> considered in the join. Virtual table is the result set from the prior
> joins. In your case, the result from the inner join will be the virtual
> result table that will be the preserved table in the outer join. That
> means only rows that satisfy the inner join predicates will be considered
> the virtual table for the outer join.
>

So then the order of the tables would important.

If you had an inner join/inner join/left outer join/inner join:
1) 1st inner join would join 2 tables ( could be the result of 2 views)
2) 2nd inner join would join the results (virtual table) from the first join
with another table
3) left outer join would join the results of #2 (the 2 inner joins) with
another table. Since this is a left outer join, we would get all the rows
from the 1st 2 inner joins.
4) 3rd inner join would join the results of #3 with another table.

Thanks,

Tom
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 29 Mar 2010 18:56
Yes, the order for the joins matters when you have outer joins. In general the steps you describe is the logical order
to process the joins. In practice the query engine may do it differently, but the final results should be the same as if
the logical processing order is followed.

Itzik Ben-Gan has a very good poster of the logical processing available for download here:
http://www.solidq.com/insidetsql/books/insidetsql2008/

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 29 Mar 2010 19:59

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:qbednTTjT_KRrSzWnZ2dnUVZ_o-hnZ2d(a)speakeasy.net...
> Yes, the order for the joins matters when you have outer joins. In general
> the steps you describe is the logical order to process the joins. In
> practice the query engine may do it differently, but the final results
> should be the same as if the logical processing order is followed.
>

Sounds good.

> Itzik Ben-Gan has a very good poster of the logical processing available
> for download here:
> http://www.solidq.com/insidetsql/books/insidetsql2008/
>

Couldn't find the poster, but I think I will get the book, however.

Thanks,

Tom

> --
> Plamen Ratchev
> http://www.SQLStudio.com


 |  Next  |  Last
Pages: 1 2
Prev: Assign permissions t user defined functions
Next: Join Syntax multiple on