SQL Server keyword detailed encyclopedia (text) _mssql

The following cross apply and outer apply keywords in SQL Server are presented as follows:

1.CROSS Apply and outer apply

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

Use the APPLY operator to call a table-valued function for each row returned by an external table expression that implements a query operation. The table-valued function is the right input, and the outer table expression is left-input.

The resulting row is combined as the final output by evaluating the right input for each row in the left input.

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

There are two forms of APPLY:

CROSS apply and OUTER apply.

CROSS APPLY returns only the rows in the external table that generate the result set through table-valued functions.

OUTER APPLY returns both the row that generated the result set and the row that does not generate the result set, where the value in the column generated by the table-valued function is NULL.

The explanations for online collection are as follows (individuals feel good understanding):

In SQL Server database operations, a new application table operator is added to the version above 2005. The new apply table operator applies the right table expression to each row in the left table expression.

It does not compute which table expression can be calculated first, and apply must first logically evaluate the left expression. This logical order of calculation input allows the right expression to be associated with the left table expression.

apply has two forms , one is OUTER apply, one is CROSS apply, the difference is that specifying OUTER means that the result set will contain rows in the left table expression that makes the right table expression empty. Instead, the result set does not contain rows in the left table expression that makes the right table expression empty. Cross

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

Here's an example:

For example, a category table (Category) reads as follows:

There is also a breakdown of the categories (Categorydetail) as follows:

Here's a look at the results of OUTER APPLY :

 SELECT * from
 dbo. Category a
   OUTER APPLY (SELECT *
      from  dbo. Categorydetail b
      WHERE  B.categoryid = a.id
      ) as C;

From the above figure can be seen in the left table OUTER apply the information in the right table is also associated with, of course, when the right table information is empty (null), OUTER apply will also appear in the result set.

Next we look at the results of the cross apply query:

 SELECT * from
 dbo. Category a
   CROSS APPLY (SELECT *
      from  dbo. Categorydetail b
      WHERE  B.categoryid = a.id
      ) as C;

According to this figure and the above comparison, it can be seen that this return result is only two, the Category table of tiger information is not brought out, because there is no corresponding detail in the Categorydetail table.

As can be obtained from the above information, OUTER apply is equivalent to the sum of mathematics, and cross apply is equivalent to the intersection of mathematics, the introduction of the intersection and the set is as follows:

and set to all the red parts in the following illustration, which are all of A and B:

The intersection is the red part of the following figure, which is the part where A and b intersect:

2.OUTER APPLY and left JOIN

The left JOIN keyword returns all rows from the left-hand table (Category), even if there are no matching rows in the right table (Categorydetail).

Note: In some databases, the left join is called the left OUTER join.

Let's look at the query results for the left JOIN (or 1). CROSS apply and OUTER apply in the example):

 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-hand table (Category), even if there are no matching rows in the right table (Categorydetail). The effect is the same as outer APPLY.

The main differences between OUTER APPLY and left JOIN are:

A LEFT JOIN keyword can join only one table, not solve a complex SELECT statement, or a function method.

A outer APPLY keyword can contain a separate, complex SELECT statement, or other function methods.

Difference between OUTER APPLY and left JOIN performance:

The summary of the performance comparison of left JOIN and OUTER apply shows that the left join is faster than the OUTER apply performance. Therefore, it is recommended to use left join as far as possible not to use outer apply.

Note:

With Category table and Categorydetail table results and insert data script:

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, Statistics_norecompute = off, Ignore_dup_key = off, Allow_ro W_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 ' Meow ') 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 ' Go Set ansi_padding ' Go CREATE TABLE [db O]. [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 is all shown in this article, I hope you like it.