Shulou(Shulou.com)05/31 Report--

How to use the Apply keyword in SQL Server, aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

1. Concept introduction

The APPLy keyword is a system keyword that began to be available in the SQLServer version.

The function of APPLY is similar to that of connection, and the APPLY operation is divided into two parts: left and right.

Right expression of APPLY: each line of the left expression is evaluated with the right expression, that is, the right expression needs to be evaluated according to the value provided by the left expression to get the relevant results, and then returned to the client.

The right expression of APPLY: is a subquery or table-valued function.

2. The difference between APPLY operator and join query

APPLY: the data acquisition of the left expression of the advanced row, and then the correlation acquisition of the value of the right expression according to the record of the left expression.

Join: first perform Cartesian product operation on the left and right tables.

3. The syntax format of the APPLY operator

CROSS APPLY: inner join. Two tables are directly joined without any association conditions. The result is that the Cartesian set of these two tables is equivalent to CROSS JOIN.

The difference between CROSS Apply and CROSS JOIN: Cross Apply can use the value of the field of the previous associated table in the associated table subquery, but CROSS JOIN cannot.

For example:

SELECT * FROM T1 a CROSS APPLY T2 b WHERE a.id=b.id-equivalent to inner join SELECT * FROM T1 a CROSS JOIN T2 b WHERE a.id=b.id-error report

OUTER APPLY: the left outer join, the table expression on the right returns an empty set, and the CROSS APPLY operator does not return the corresponding left row, that is, OUTER APPLY is equivalent to LEFT JOIN on the derived table.

SELECT * FROM T1 an OUTER APPLY (select * from T2 where id=a.id) b-equivalent to left join. 4. Cross Apply uses 4.1 for use with table-valued functions

You can use CROSS APPLY if the query result set needs to use a table-valued function to process the value of a field.

4.2 usage of topic subquery

-- query the name of the first place in Chinese, the top two in mathematics and the top five in English, subjects and scores. The implementation method with cross apply is as follows

SELECT b. * FROM (select Subject='Chiness',num=1 union all select 'Math',3 union all select' English',5) a cross apply (select top (a.num) * from Students where Subject=a.Subject) b on how to use the Apply keyword in SQL Server is shared here. I hope the above content can be of some help to everyone. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.