Developing Matt

My Technical Journal

What Itzik Taught me Part 2: CROSS APPLY LIBERALLY

leave a comment »

Excuse my mumbling fumblings while I attempt to grasp the CROSS APPLY operator, as explained to me by Itzik Ben-Gan.  I’m on page 181ish of his book T-SQL Fundamentals. 

If you are a Mormon Fundamentalist, CROSS APPLY will help you tally up the men in your camp with their top three wives.  Because, you see, CROSS APPLY will help you, in one shot, to produce a report of all the men and their first three wives.  (note: mormon fundamentalists believe in polygamy; mormons don’t)

Cross apply enables you to apply the relationship for the table on the right for each row of the table on the left.  One example would be to query the top three purchases from each customer.  There isn’t really any way to do this with a simple join.  To do it without CROSS APPLY you would create a cursor and a temp table and insert the top three sales into your temp table for each customer and query it in the end.  But if you use CROSS APPLY it looks something like this

select cust.custid, cust.contactname, totalprice from
Sales.Customers as Cust
cross apply
(
   select top 3 O.custid,  totalprice = D.qty * D.unitprice   
   from Sales.Orders O
   join Sales.OrderDetails D on O.orderid=D.orderid
   where O.custid = Cust.custid
   order by   D.qty * D.unitprice   desc
)as a
order by cust.custid

Note that the right table (the cross apply one) is joined to the left one in bold.  This signifies the relationship between these two tables and tells the engine that for every customer in the left query show me the top three sales in the right query.

For the Mormon Fundamentalists in the group your query would go as such:
   select  manname, subservant.womanname
   from   fundamentalistmormon a
   CROSS APPLY 
      (select top 3 femalename from fundamentalistmormon b where b.husband = a.personid) as subservant

This would get you all the men with their top 3 wives.  Feel free to use that query liberally and not credit me back for it. 

But there is a problem

WHAT IF your camp includes a fundamentalist mormon boy who doesn’t like girls?  What if whenever they came around the boy ran to the mormon fundamentalist hills?   How would you include this young lad’s name in the list? 

EASY AS A MORMON FUNDAMENTALIST CASSEROLE

You can use the OUTER APPLY to apply the boy’s name from the outer hills.  This will cause the query engine to execute the query twice, once to catch the ones with wives, twice to catch the boy still running.

   select  manname, womanname
   from   fundamentalistmormon a
   OUTER APPLY (select top 3 femalename from fundamentalistmormon b where b.husband = a.personid) as c

Fundamentalist Mormon Caserole Made by 1 of 1 Spouse

Advertisements

Written by matt

June 4, 2010 at 2:41 pm

Posted in T-Sql

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: