SQL server keyword description, SQL server

SQL server keyword description (text), SQL server

The following describes the cross apply and outer apply keywords in sqlserver as follows:

1. cross apply and OUTER APPLY

The MSDN explanation is as follows (my personal understanding is not clear ):

You can use the APPLY operator to call the table value function for each row returned by the External table expression that implements the query operation. The table value function is used as the right input, and the External table expression is used as the left input.

Evaluate the right input to obtain the calculation result of each row in the left input. The generated rows are combined for final output.

The list of columns generated by the APPLY operator is the column set in the left input, followed by the list of columns returned by the right input.

APPLY has two forms:

Cross apply and outer apply.

Cross apply only returns the rows in the result set generated by the table Value Function in the External table.

Outer apply returns both the row that generates the result set and the row that does not generate the result set. The value in the column generated by the table value function is NULL.

The explanations collected on the Internet are as follows (I personally feel better at understanding ):

In SQL Server database operations, an APPLY table operator is added in Versions later than 2005. The new APPLY table operator applies the right table expression to each row in the left table expression.

Unlike the JOIN operation, it can calculate any table expression first. APPLY must calculate the left expression logically. The logic order of input calculation allows the right expression to be associated with the left table expression.

APPLY can be in either of the following ways:, One isOUTER APPLY, One isCROSS APPLYThe difference is that the OUTER is specified, which means that the result set will contain rows in the left table expression that makes the right table expression empty, while CROSS is specified, the opposite is true, the result set does not contain rows in the left table expression that empty the right table expression.

Note: To use APPLY, the database compatibility level must be 90.

The following is an example:

For example, a Category table contains the following content:

 

There is also a category detail table (CategoryDetail) with the following content:

Let's take a look.OUTER APPLYQuery results:

 SELECT * FROM dbo.Category a   OUTER APPLY ( SELECT *      FROM  dbo.CategoryDetail b      WHERE  b.CategoryId = a.Id      ) AS c ;

It can be seen that outer apply also associates the information in the right table after detecting the information in the left table. Of course, when the information in the right table is NULL, outer apply is also displayed in the result set.

Next, let's take a look at the cross apply query results:

 SELECT * FROM dbo.Category a   CROSS APPLY ( SELECT *      FROM  dbo.CategoryDetail b      WHERE  b.CategoryId = a.Id      ) AS c ;

According to the comparison between the figure and the above, there are only two results returned, and the Tiger information in the Category table is not included because there is no corresponding details in the CategoryDetail table.

According to the above information, outer apply is equivalent to the Union set in mathematics, while cross apply is equivalent to the intersection in mathematics. The introduction of intersection and union is as follows:

And set to all the red parts, that is, all of A and B:

The intersection is the red part, that is, the intersection of A and B:

2. outer apply and LEFT JOIN

The left join keyword returns all rows from the LEFT table (Category), even if no matching row exists in the right table (CategoryDetail.

Note: In some databases, left join is called left outer join.

Let's take a look at the query results of left join (or an example in 1. cross apply and outer apply ):

 SELECT * FROM dbo.Category a   LEFT JOIN dbo.CategoryDetail b ON b.CategoryId = a.Id ;

The left join keyword returns all rows from the LEFT table (Category), even if no matching row exists in the right table (CategoryDetail. The effect is the same as that of outer apply.

The main differences between outer apply and left join are:

A left join keyword can only JOIN one table. It cannot solve a complex SELECT statement or function method.

An outer apply keyword can contain an independent complex SELECT statement or other function methods.

Performance differences between outer apply and left join:

This article summarizes the Performance Comparison Between left join and outer apply, and shows that left join is faster than outer apply. We recommend that you do not use outer apply if you can use left join.

Note:

The result of the Category table and CategoryDetail table and the script for inserting data are attached:

Create table [dbo]. [CategoryDetail] ([Id] [int] IDENTITY (,) not null, [CategoryId] [int] NULL, [Cry] [varchar] () NULL, CONSTRAINT [PK_CategoryDetail] primary key clustered ([Id] ASC) WITH (PAD_INDEX = OFF, expiration = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ON [PRIMARY] go set ANSI_PADDING off go set IDENTITY_INSERT [dbo]. [CategoryDetail] on insert [dbo]. [CategoryDetail] ([Id], [CategoryId], [Cry]) VALUES (, N 'meak') INSERT [dbo]. [CategoryDetail] ([Id], [CategoryId], [Cry]) VALUES (, N 'wang') SET IDENTITY_INSERT [dbo]. [CategoryDetail] OFF/****** Object: Table [dbo]. [Category] Script Date: //: ******/SET ANSI_NULLS on go set QUOTED_IDENTIFIER on go set ANSI_PADDING on go create table [dbo]. [Category] ([Id] [int] IDENTITY (,) not null, [Name] [varchar] () NULL, CONSTRAINT [PK_Category] primary key clustered ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] go set ANSI_PADDING off go set IDENTITY_INSERT [dbo]. [Category] on insert [dbo]. [Category] ([Id], [Name]) VALUES (, N 'cat') INSERT [dbo]. [Category] ([Id], [Name]) VALUES (, N 'Dog') INSERT [dbo]. [Category] ([Id], [Name]) VALUES (, N 'tiger ') SET IDENTITY_INSERT [dbo]. [Category] OFF

The above content is all shown in this article. I hope you will like it.