SQL statements-MERGE into, cross/outer apply usage understanding

MERGE into syntax:

MERGE into table_name ALIAS1
USING (table|view|sub_query) alias2
On (Join condition)
When matched then
UPDATE table_name
SET col1 = Col_val1,
col2 = Col2_val
When isn't matched then
INSERT (column_list) VALUES (column_values);

Understand:

Select the data in the ALIAS2, each of which is compared to the ALIAS1 on (join condition), if the match, the Update operation (update), if not match, insert operation (insert).
So, strictly speaking, "in a merge statement that has both INSERT and update syntax, the total number of insert/update records is the number of records ALIAS2 in the using statement. "

Cross/outer Apply Syntax:

< left table > {cross|outer} Apply < Right table >

Such as:

C Cross apply    (select top 2 * from     Orders as O     c.customerid=o.customerid     ORDER BY OrderID desc) as CA

Understand:

It is the first to draw < left table > "Customers" Data, and then will < the left table > each record, the corresponding condition query to get < right table > N records, then merge to get n rows of records, and finally return all the data rows.

The difference between cross and outer: if there is no data for the right table for a row of the left table, then there will be no record in the cross apply result, and outer apply results in a record with NULL on the right table.

(The actual process database is also optimized)

SQL statements-MERGE into, cross/outer apply usage understanding