none
Cross Apply in SqlServer

Answers

  • The APPLY operator is similar to the JOIN operator, but the difference is that the right-hand side operator of APPLY can reference columns from the left-hand side. Here are two very quick examples:

    SELECT a.col, b.resultcol
    FROM     dbo.tbl a
    CROSS    APPLY dbo.mytblfunc(a.somecol) AS b

    That is, you call a table-valued function where the parameters comes from the table.

    SELECT C.CustomerName, O.*
    FROM     Customers C
    OUTER    APPLY (SELECT TOP 1 *
                                FROM     Orders O
                                WHERE    C.CustomerID = O.CustomerID
                                ORDER    BY O.OrderDate DESC, O.OrderID DESC) AS O

    In this example we list all customers and the most recent order.

    As you in the examples there are both CROSS APPLY and OUTER APPLY. The difference between these two is what happens when the right-hand side returns no rows. With OUTER APPLY, the row from the left-hand side is retained, showing NULLs in all columns from the right-hand side. With CROSS APPLY, the row on the left-hand side is removed from the result set.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Shkumar Friday, December 02, 2011 9:53 AM
    • Marked as answer by Kalman TothModerator Wednesday, December 07, 2011 9:06 PM
    Friday, December 02, 2011 9:48 AM
  • Another use of the CROSS APPLY operator is to create aliases that can be re-used by the query.  For example:

    select
      100*a/line_Total as a_Percent,
      100*b/line_Total as b_Percent,
      100*c/line_Total as c_Percent
    From a_Table
    cross apply
    ( select nullif(a+b+c, 0)
    ) x(Line_Total)
    

     

    Friday, December 02, 2011 1:53 PM
  • APPLY operator was a topic in SQL Tuesday #17

    Here is Brad Schulz take on this topic

    T-SQL Tuesday #017: APPLY: It Slices! It Dices! It Does It All!


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, December 02, 2011 2:51 PM

All replies

  • Very simple example how it works:

    T-SQL:

    DECLARE @TableCA1 TABLE
    (ID VARCHAR(5))
    
    INSERT INTO @TableCA1
    SELECT 'A' UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'C' UNION ALL
    SELECT 'D'
    
    DECLARE @TableCA2 TABLE
    (VAL VARCHAR(5))
    
    INSERT INTO @TableCA2
    SELECT 'X'
    
    SELECT CA1.ID,CA2.VAL FROM @TableCA1 CA1
    CROSS APPLY
    @TableCA2 CA2

    Shatrughna.
    Friday, December 02, 2011 9:40 AM
  • Hi,

    Cross Join in SQL:The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

    for mor details wit Cross Join Query check this link

    http://www.w3resource.com/sql/joins/cross-join.php


    PS.Shakeer Hussain
    Friday, December 02, 2011 9:48 AM
  • The APPLY operator is similar to the JOIN operator, but the difference is that the right-hand side operator of APPLY can reference columns from the left-hand side. Here are two very quick examples:

    SELECT a.col, b.resultcol
    FROM     dbo.tbl a
    CROSS    APPLY dbo.mytblfunc(a.somecol) AS b

    That is, you call a table-valued function where the parameters comes from the table.

    SELECT C.CustomerName, O.*
    FROM     Customers C
    OUTER    APPLY (SELECT TOP 1 *
                                FROM     Orders O
                                WHERE    C.CustomerID = O.CustomerID
                                ORDER    BY O.OrderDate DESC, O.OrderID DESC) AS O

    In this example we list all customers and the most recent order.

    As you in the examples there are both CROSS APPLY and OUTER APPLY. The difference between these two is what happens when the right-hand side returns no rows. With OUTER APPLY, the row from the left-hand side is retained, showing NULLs in all columns from the right-hand side. With CROSS APPLY, the row on the left-hand side is removed from the result set.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Shkumar Friday, December 02, 2011 9:53 AM
    • Marked as answer by Kalman TothModerator Wednesday, December 07, 2011 9:06 PM
    Friday, December 02, 2011 9:48 AM
  • Another use of the CROSS APPLY operator is to create aliases that can be re-used by the query.  For example:

    select
      100*a/line_Total as a_Percent,
      100*b/line_Total as b_Percent,
      100*c/line_Total as c_Percent
    From a_Table
    cross apply
    ( select nullif(a+b+c, 0)
    ) x(Line_Total)
    

     

    Friday, December 02, 2011 1:53 PM
  • APPLY operator was a topic in SQL Tuesday #17

    Here is Brad Schulz take on this topic

    T-SQL Tuesday #017: APPLY: It Slices! It Dices! It Does It All!


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, December 02, 2011 2:51 PM
  • Wednesday, December 07, 2011 9:09 PM