locked
Cross Apply in SqlServer RRS feed

  • Frage

  • HI,

    What is the use of cross apply?

    Freitag, 2. Dezember 2011 09:37

Antworten

  • 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
    • Als Antwort vorgeschlagen Shkumar Freitag, 2. Dezember 2011 09:53
    • Als Antwort markiert Kalman Toth Mittwoch, 7. Dezember 2011 21:06
    Freitag, 2. Dezember 2011 09:48
  • 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)
    

     

    • Als Antwort markiert Kalman Toth Mittwoch, 7. Dezember 2011 21:06
    Freitag, 2. Dezember 2011 13:53
  • 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
    • Als Antwort markiert Kalman Toth Mittwoch, 7. Dezember 2011 21:07
    Freitag, 2. Dezember 2011 14:51

Alle Antworten

  • 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.
    Freitag, 2. Dezember 2011 09:40
  • 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
    Freitag, 2. Dezember 2011 09:48
  • 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
    • Als Antwort vorgeschlagen Shkumar Freitag, 2. Dezember 2011 09:53
    • Als Antwort markiert Kalman Toth Mittwoch, 7. Dezember 2011 21:06
    Freitag, 2. Dezember 2011 09:48
  • 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)
    

     

    • Als Antwort markiert Kalman Toth Mittwoch, 7. Dezember 2011 21:06
    Freitag, 2. Dezember 2011 13:53
  • 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
    • Als Antwort markiert Kalman Toth Mittwoch, 7. Dezember 2011 21:07
    Freitag, 2. Dezember 2011 14:51
    • Bearbeitet Kalman Toth Freitag, 10. November 2017 13:23
    Mittwoch, 7. Dezember 2011 21:09