SQL on the two forms of apply crossapply and outerapply

Source: Internet
Author: User
SQL has two forms of apply: crossapply and outerapplyapply. first look at the Syntax: left_table_expression {cross | outer} applyright_table_expression. Then let's take a look at the two steps involved in the apply operation: a1: right table expression (right _

SQL has two forms of cross apply and outer apply: cross apply and outer apply. Check the syntax first: left_table_expression {cross | outer} apply right_table_expression let's take a look at the two steps involved in the apply operation: A1: Put the right table expression (right _

SQL cross apply and outer apply

Apply has two forms: cross apply and outer apply.

First look at the Syntax:

   {Cross | outer} apply

Let's take a look at the two steps involved in the apply operation:

  • A1: Right table expression ( ) Apply to the left table ( ) Input rows;
  • A2: Add external rows;
  • Using apply is like calculating the left input first, and then calculating the right input for each row in the left input. (This sentence is very important and may not be understood, but remember it first. A detailed description will be provided later)

    Finally, the difference between cross apply and outer apply is described based on the above two steps:

    Cross apply and outer apply always contain step A1. Only outer apply contains step A2. If cross apply applies to the Left row and returns an empty product when the expression in the right table is applied, this row is not returned. Outer apply returns the modified row, and the attribute of the right table expression of the modified row is null.

    We can see that the above explanation or steps may still be confusing, and yunyun. The following is an example:

    , City -- city ):

    . (5) COLLATE Chinese_PRC_CI_AS not null, (10) COLLATE Chinese_PRC_CI_AS not null ,())

    Insert data to table 1:

    ,);,);,);,);

    Query the inserted data:

    Dbo. MERs

    Result

    Create another table 2 ([dbo]. [Orders] field Description: orderid -- order id, customerid -- consumer id ):

    . (, (5) COLLATE Chinese_PRC_CI_AS NULL ,())

    Insert data to table 2:

    ); Insert into dbo. Orders values (7, null );

    Query the inserted data:

    Dbo. orders

    Result

    Example: Question: Get the latest two orders for each consumer:

    Use cross apply

    SQL:

    Dbo. Customers as C cross apply (dbo. Orders as Owhere C. customerid = O. customeridorder by orderid desc) as CA

    Result

    Process Analysis:

    It first obtains the left table [dbo. customers data, and then put the data one by one into the right table, respectively, to obtain the result set, finally, the result set is integrated into the final returned result set (T1 data enters the T2 one by one like a for loop, and then returns a set, and finally integrates all the sets one piece is the final result ), website space. Finally, let's take a look at the words mentioned above (using apply is like calculating the left input first, and then calculating the right input for each row in the left input.

    Experiment: Try outer apply to see the result:

    SQL statement:

    Dbo. Customers as C outer apply (dbo. Orders as Owhere C. customerid = O. customeridorder by orderid desc) as CA

    Result

    Result Analysis:

    We found that outer apply has a row more than cross. Based on the difference described above (cross apply and outer apply always include step A1, virtual host, and only outer apply contains step A2, if cross apply returns an empty product when the expression in the right table is applied to the Left row, this row is not returned. Outer apply returns the modified row, and the attribute of the right table expression of the modified row is null.

    , Hong Kong VM

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.