The fifth day of SQL Study -- two forms of SQL apply: Cross apply and outer apply

Source: Internet
Author: User
SQL cross apply and outer apply

Apply has two forms: Cross apply and outer apply.

First look at the Syntax:

  <Left_table_expression> {cross | outer} apply <right_table_expression>

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

  1. A1: Apply the right table expression (<right_table_expression>) to the input row in the left table (<left_table_expression>;
  2. 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:

Create Table 1 first ([DBO]. [MERs] field Description: customerid -- consumer ID, city -- City ):

 

CREATE TABLE [dbo].[Customers](    [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NOT NULL,    [city] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,PRIMARY KEY CLUSTERED (    [customerid] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

 

Insert data to table 1:

insert into dbo.Customers values('FISSA','Madrid');insert into dbo.Customers values('FRNDO','Madrid');insert into dbo.Customers values('KRLOS','Madrid');insert into dbo.Customers values('MRPHS','Zion');

Query the inserted data:

select * from dbo.Customers

Result

Create another table 2 ([DBO]. [orders] field Description: orderid -- order ID, customerid -- consumer ID ):

CREATE TABLE [dbo].[Orders](    [orderid] [int] NOT NULL,    [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NULL,PRIMARY KEY CLUSTERED (    [orderid] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

Insert data to table 2:

insert into dbo.Orders values(1,'FRNDO');insert into dbo.Orders values(2,'FRNDO');insert into dbo.Orders values(3,'KRLOS');insert into dbo.Orders values(4,'KRLOS');insert into dbo.Orders values(5,'KRLOS');insert into dbo.Orders values(6,'MRPHS');insert into dbo.Orders values(7,null);

Query the inserted data:

select * from dbo.orders

Result

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

Use cross apply

SQL:

select *from dbo.Customers as C cross apply    (select top 2 *     from dbo.Orders as O     where C.customerid=O.customerid     order 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 ), 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.) Is it clear.

Experiment: Try outer apply to see the result:

SQL statement:

select *from dbo.Customers as C outer apply    (select top 2 *     from dbo.Orders as O     where C.customerid=O.customerid     order by orderid desc) as CA

Result

Result Analysis:

We can see that outer apply has a row more than cross (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..

 

 

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.