SQL Server uses CROSS APPLY and OUTER APPLY to implement connection queries

This article brings you relevant knowledge about SQL server. It mainly introduces the method of SQL Server using CROSS APPLY and OUTER APPLY to implement connection queries. The article introduces it in detail through sample code. , let’s take a look at it, I hope it will be helpful to everyone.

Recommended study: "SQL Tutorial"

Overview

CROSS APPLY and OUTER APPLY can do:
When one record in the left table is associated with multiple records in the right table, I need to control whether one or more records in the right table match the left table.

There are two tables: Student (student table) and Score (score table). The data are as follows:

1. CROSS APPLY

ROSS APPLY means "cross-application". When querying, the left table is first queried, and then each record in the right table is matched with the current record in the left table. If the match is successful, the records of the left table and the right table will be merged into one record and output; if the match fails, the records of the left table and the right table will be discarded. (Similar to INNER JOIN)

Query the last two test scores of each student.

SELECT T1.StudentNo, T1.Name, T2.ExamScore, T2.ExamDate FROM Student AS T1
CROSS APPLY
(
    SELECT TOP 2 * FROM Score AS T
    WHERE T1.StudentNo = T.StudentNo
    ORDER BY T.ExamDate DESC
) AS T2

Result:

##2. OUTER APPLY

OUTER APPLY means "external application", which is consistent with the principle of CROSS APPLY , but when the matching fails, the left table and the right table will also be merged into one record for output, but the output field of the right table will be null. (Similar to LEFT OUTER JOIN)

Query the last two test scores of each student, and fill in the scores of students who did not take the test with null

SELECT T1.StudentNo, T1.Name, T2.ExamScore, T2.ExamDate FROM Student AS T1
OUTER APPLY
(
    SELECT TOP 2 * FROM Score AS T
    WHERE T1.StudentNo = T.StudentNo
    ORDER BY T.ExamDate DESC
) AS T2

Result:

3. Application scenarios

1. Combined with table-valued functions:

There is a table like this:

It is a very simple table with only one field num. I want to convert the int numbers in this field into binary octal and hexadecimal values. There are ready-made table-valued functions for base conversion.

SELECT * FROM #T a CROSS APPLY [dbo].[F_TConversion](a.num)

To summarize: If the query result set needs to use a table-valued function to process the value of a field, please use CROSS APPLY~

2. Usage of top subquery:

There is a student table with three fields: name, subject, and score, as follows:

I want to see The name, subject, and score of the first place in Chinese, the top two in mathematics, and the top three in English can be achieved using cross apply as follows:

SELECT b.* FROM (
    select Subject='Chiness',num=1 union all
    select 'Math',2 union all
    select 'English',3) a 
cross apply 
    (select top(a.num) * from Students where Subject=a.Subject ) b

Recommended study: "

SQL tutorial》

The above is the detailed content of SQL Server uses CROSS APPLY and OUTER APPLY to implement connection queries. For more information, please follow other related articles on the PHP Chinese website!