Application of SQL Server cross apply and OUTER apply

When you encounter multiple table queries in your daily development, you will first think of INNER join or left OUTER join, and so on, but these two queries sometimes do not meet the requirements. For example, when a left table is associated with multiple records in the right table, I need to control one or more records in the right table to match the left table. It seems that the INNER join or the left OUTER join is not well done. But cross apply and OUTER apply can

There are two tables: Student (student table) and score (score table), the data are as follows

1) Check the last two times of each student's exam results

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

2) Check the last two times of each student's exam results, the students who did not take the exam to fill 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


Summarize

1. Understand cross apply and OUTER apply (personal understanding)

1) Cross apply means "crossover application", querying the left table first, and then each record in the right table matches the current record of the left table. If the match succeeds, the records of the left table and the right table are merged into one record output, and the matching failure discards the records of the left and right tables. (similar to INNER JOIN)

2) OUTER apply means "external application", consistent with cross apply, except that when a match fails, the left and right tables are merged into one record output, but the output field of the right table is null. (similar to left OUTER JOIN)



2. The difference between cross APPLY and INNER JOIN

1) Cross APPLY can query the right table based on the current record of the current left table, but INNER join cannot, INNER join is the entire result set of the right table according to the current record of left table.

2) Both are matched successfully before output.



3. OUTER the difference between APPLY and left OUTER JOIN

1) They are similar to (cross APPLY and INNER JOIN).

2) Only both are matched failures will also output.



4. Usage Scenarios:

1) A product has multiple pictures, but only want to take a recent picture with the product match.



5. Bottom line: the right table can be conditionally matched to the record on the left table, while the value of the condition can come to the left table.

Application of SQL Server cross apply and OUTER apply