The Apply operator in SQL Server 2008 uses the method _mssql2008

The Apply operator can implement a combined result of two query results, also known as a cross collection. For example two data combinations (a,b), (A,b), their cross collection for (Aa,ab,aa,ab).

Apply is divided into cross apply and outer apply two kinds of use. The specific analysis is as follows:

First, create two table studentlist and Scoreinfo. The scripting language is as follows:

Copy Code code as follows:

CREATE TABLE Studentlist (
ID int Identity (1,1) NOT NULL,
Name nvarchar is not NULL,
Sex bit NOT NULL,
Birthday date NOT NULL,
Class nvarchar (2) NOT NULL,
Grade nvarchar (2) NOT NULL,
RegDate date NOT NULL,
Primary key (ID));

CREATE TABLE Scoreinfo (
ID int Identity (1,1) NOT null primary key,
StudentID int NOT NULL,
ClassID int NOT NULL,
Score int NOT NULL,
TestDate date NOT NULL,
RegDate date not NULL);

Where the StudentID in Scoreinfo is the foreign key of the ID in studentlist

Insert data, script as follows

Copy Code code as follows:

Insert into Studentlist (Name, Sex, Birthday, Class, Grade, RegDate) VALUES (' John ', 1, ' 1988-05-28 ', 1, 8, ' 2010-05-05 ');

Insert into Studentlist (Name, Sex, Birthday, Class, Grade, RegDate) VALUES (' Dick ', 1, ' 1985-09-13 ', 4, 4, ' 2010-05-05 ');

Insert into Studentlist (Name, Sex, Birthday, Class, Grade, RegDate) VALUES (' Wang Li ', 0, ' 1987-11-05 ', 1, 7, ' 2010-05-05 ');

Insert into Scoreinfo (StudentID, ClassID, Score, TestDate, RegDate) VALUES (1, 1, 98, ' 2010-04-15 ', ' 2010-05-01 ');

Insert into Scoreinfo (StudentID, ClassID, Score, TestDate, RegDate) VALUES (1, 2, 92, ' 2010-04-15 ', ' 2010-05-01 ');

Insert into Scoreinfo (StudentID, ClassID, Score, TestDate, RegDate) VALUES (1, 3, 86, ' 2010-04-15 ', ' 2010-05-01 ');

Insert into Scoreinfo (StudentID, ClassID, Score, TestDate, RegDate) VALUES (2, 1, 95, ' 2010-04-15 ', ' 2010-05-01 ');

Insert into Scoreinfo (StudentID, ClassID, Score, TestDate, RegDate) VALUES (2, 2, 94, ' 2010-04-15 ', ' 2010-05-01 ');

Insert into Scoreinfo (StudentID, ClassID, Score, TestDate, RegDate) VALUES (2, 3, 91, ' 2010-04-15 ', ' 2010-05-01 ');

Insert into Scoreinfo (StudentID, ClassID, Score, TestDate, RegDate) VALUES (3, 1, 90, ' 2010-04-15 ', ' 2010-05-01 ');

Insert into Scoreinfo (StudentID, ClassID, Score, TestDate, RegDate) VALUES (3, 2, 88, ' 2010-04-15 ', ' 2010-05-01 ');

Insert into Scoreinfo (StudentID, ClassID, Score, TestDate, RegDate) VALUES (3, 3, 90, ' 2010-04-15 ', ' 2010-05-01 ');

The two-table structure was established and the data was successfully inserted. For easy explanation, insert another record in the Studentlist table

Copy Code code as follows:

Insert into Studentlist (Name, Sex, Birthday, Class, Grade, RegDate)
Values (' Li Ming ', 1, ' 1989-05-04 ', 2, 7, ' 2010-05-05 ');

Enter the following statement

Copy Code code as follows:

SELECT * FROM Studentlist a
Cross Apply
(select ClassID, Score from Scoreinfo where studentid=a.id) b;

The results are as follows

And then enter the following statement

SELECT * FROM Studentlist a
Outer Apply
(select ClassID, Score from Scoreinfo where studentid=a.id) b;

The results are as follows

You can see the difference between cross apply and outer apply

Cross apply to cross-match the two select query results on both sides of the statement to show all the results. The Cross apply query ensures that only a collection of valid information is listed when querying the intersection of two subset data.

The outerapply query lists all the combinations of two subsets, regardless of whether the data crosses or not, displaying all the data to be paired.