CROSS APPLY and OUTER APPLY clause in SQL-SERVER 2005

CROSS APPLY and OUTER APPLY clause in SQL-SERVER 2005

I will be introducing you with magical feature of Microsoft SQL-Server 2005, called CROSS APPLY and OUTER APPLY. If you want to get top 2 or 3 maximum inventory quantity of each product or all product of AdventureWorks database then it is bit difficult in SQL-Server 2000 as it doesn’t support CROSS APPLY or OUTER APPLY. You can use UDF or sub query for CROSS APPY or OUTER APPLY.

BTW, you can consider CROSS APPLY clause as INNER APPLY also as it will use outer (main) query as an input of subquery or function and will return the result set. In CROSS APPLY we will be getting full set of left side query (main query) and its corresponding value from right side query or function, if it is not available in right side query or function, it will return NULL.

 

First let us see use of CROSS APPLY:

We are going to create one function which will return top row from production.productinventory table of AdventureWorks database based on supplied productID and row number.

CREATE FUNCTION dbo.fn_GetMax_ProductItem(@Productid AS int, @rownum AS INT)

  RETURNS TABLE

AS

RETURN

  SELECT TOP(@rownum) *

  FROM Production.ProductInventory

  WHERE ProductID = @Productid

  ORDER BY Quantity DESC

GO

You can run above UDF and test it with following query.

select * from dbo.fn_GetMax_ProductItem(1,2)

It will return top two row of productID 1.

Note: I strongly recommend not using “*” in query in live production environment due to performance issue, this is ok in this example.

As function is ready, we will start using that function in our CROSS APPLY example.

SELECT 

      p.ProductID,

      p.Name,

      p.ProductNumber,

      pmi.Quantity,

      pmi.locationID

FROM

      Production.Product AS p

CROSS APPLY

      dbo.fn_GetMax_ProductItem(p.productid, 2) AS pmi

WHERE

      p.ProductID in (1,2,3,531,706)

ORDER BY

      p.productid ASC

As soon as you run above query, you will get 8 rows. Two rows for each productID (1,2,3,531). You will not get any row for ProductID 706 as it is not available in Production.Product table.  This proves that CROSS APPLY clause works like INNER APPLY.

Now let us tweak above query a bit with OUTER APPLY instead of CROSS APPLY.

SELECT 

      p.ProductID,

      p.Name,

      p.ProductNumber,

      pmi.Quantity,

      pmi.locationID

FROM

      Production.Product AS p

OUTER APPLY

      dbo.fn_GetMax_ProductItem(p.productid, 2) AS pmi

WHERE

      p.ProductID in (1,2,3,531,706)

ORDER BY

      p.productid ASC

This time you will get records for 706 productID as well but it will come with NULL in Quantity and LocationID.

 

Reference: Ritesh Shah

Advertisements

8 Responses to “CROSS APPLY and OUTER APPLY clause in SQL-SERVER 2005”

  1. Patsy Says:

    Hi Ritesh I was wondering whether you could help me on the following (Sorry for all the dodgy column names): I want the below to return 10 of the latest dates for each category but instead it returns around 28-35 for each category despite me having put in Top 10.Any help or suggestions would be great. Thank you.

    Select
    PA.Melon,
    PA.Orange,
    PA.Kiwi,
    S.Apple,
    DD.Pear,
    CA.Banana

    From dbo.Fruit As S
    Inner Join dbo.Bowl As DD
    On S.Pear = DD.Pear
    Inner Join dbo.Tree as PA
    On S.Orange = PA.Orange

    CROSS APPLY

    (Select Top (10)*
    From dbo.Ripe As P
    Where S.Starfish = P.Starfish
    And S.Starfish_ID In (76,77,78,79)
    And S.Date < getdate()
    Order By S.Date Desc) As CA

    Order By
    CA.Banana,
    S.Date Desc

    • riteshshah Says:

      I found some problem in subquery but have to be sure with your data, can you pass out your insert and create script so that I can fix it.

  2. Anonymous Says:

    Not to be picky, but did you mean OUTER APPLY, not CROSS APPLY?

    i.e.

    “In OUTER APPLY we will be getting full set of left side query (main query) “…

    vs.

    “In CROSS APPLY we will be getting full set of left side query (main query) “…

  3. Nilson Says:

    Hi,

    I think that
    And S.Starfish_ID In (76,77,78,79)
    And S.Date < getdate()
    may be out of subquery .

    Nilson

  4. Darshna Says:

    hi I much happy to join in your prograsive work

  5. Ravi Kuchimanchi Says:

    The article is very good and easy to understand. Thq. very much Ritesh for the valuable article. It will help in future querying.

  6. Sanish Joseph Says:

    Thanks bro.. something I was able to understand !!!

  7. Sharepoint Foundation Says:

    Sharepoint Foundation…

    […]CROSS APPLY and OUTER APPLY clause in SQL-SERVER 2005 « Ritesh’s Blog for SQL-SERVER & .NET[…]…


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: