My first post!

CROSS APPLY
Examples are based on AdventureWorks DB. You can find that from MSDN and install that with your express version of sql server 2008. I will post links about how to setup that.

I like the "Cross Apply" command in MS SQL. It gives you access to left side of the join and you can perform operations for each row from left side.

Normally cross join operator performs cartesion product if you don't specify where statement in your query.

USE AdventureWorks;
GO
Select p.SalesPerson, st.Name as Territory
from Sales.SalesPerson p
cross join Sales.SalesTerritory st

This query will produce total of nxm rows. You don't have access to table p inside table st.

You can do much better with Cross Apply.

1- This will calculate avg qty in orders and total number of orders for each product.

select pt.ProductID, pt.Name, pt.StandardCost, A.num_orders, A.avg_order
from Production.Product pt
cross apply(
select count(distinct sd.SalesOrderID) as num_orders, avg(sd.OrderQty) as avg_order
from Sales.SalesOrderDetail as sd
where sd.ProductID = pt.ProductID

) as A
order by num_orders desc;

2-Latest 3 orders per customer

Select c.CustomerID, A.SalesOrderID, A.orderdate

from Sales.Customer as c
cross apply(
   select top(3) O.SalesOrderID, O.SalesPersonID, O.OrderDate,O.DueDate
   from sales.SalesOrderHeader as O
   where O.CustomerID = c.CustomerID
   order by orderdate desc, SalesOrderID desc
) as A;

3-Other examples......