It is important to understand how the source and target data are merged into a single input stream and how additional search criteria can be used to correctly filter out unneeded rows. Otherwise,you might specify the additional search criteria in a way that produces incorrect results.
Rows in the source are matched with rows in the target based on the join predicate specified in the ON clause. The result is a combined input stream. One insert, update, or delete operation is performed per input row. Depending on the WHEN clauses specified in the statement, the input row might be any one of the following:
-
A matched pair consisting of one row from the target and one from the source. This is the result of the WHEN MATCHED clause.
-
A row from the source that has no corresponding row in the target. This is the result of the WHEN NOT MATCHED BY TARGET clause.
-
A row from the target that has no corresponding row in the source. This is the result of the WHEN NOT MATCHED BY SOURCE clause.
The combination of WHEN clauses specified in the MERGE statement determines the join type that is implemented by the query processor and affects the resulting input stream. To illustrate, consider the following example source and target tables and data.
USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');
GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO
The following table lists the possible join types and indicates when each type is implemented by the query optimizer. The table also shows the resulting input stream for the example source and target tables when the search criteria for matching the source and target data is Source.EmployeeID = Target.EmployeeID.
|
Join type
|
Implementation
|
Example input stream results
|
|---|
|
INNER JOIN
|
The WHEN MATCHED clause is the only specified WHEN clause.
|
SrcEmpID SrcName TrgEmpID TrgName
-------- ------- -------- -------
NULL NULL NULL NULL
|
|
LEFT OUTER JOIN
|
The WHEN NOT MATCHED BY TARGET clause is specified but the WHEN NOT MATCHED BY SOURCE clause is not specified. The WHEN MATCHED might or might not be specified.
|
SrcEmpID SrcName TrgEmpID TrgName
-------- ------- -------- -------100 Mary NULL NULL
101 Sara NULL NULL
102 Stefano NULL NULL
|
|
RIGHT OUTER JOIN
|
The WHEN MATCHED clause and the WHEN NOT MATCHED BY SOURCE clause are specified, but the WHEN NOT MATCHED BY TARGET clause is not specified.
|
SrcEmpID SrcName TrgEmpID TrgName
-------- ------- -------- -------NULL NULL 103 Bob
NULL NULL 104 Steve
|
|
FULL OUTER JOIN
|
The WHEN NOT MATCHED BY TARGET clause and the WHEN NOT MATCHED BY SOURCE clause are specified. The WHEN MATCHED might or might not be specified.
|
SrcEmpID SrcName TrgEmpID TrgName
-------- ------- -------- -------100 Mary NULL NULL
101 Sara NULL NULL
102 Stefano NULL NULL
NULL NULL 103 Bob
NULL NULL 104 Steve
|
|
ANTI SEMI JOIN
|
The WHEN NOT MATCHED BY SOURCE clause is the only specified WHEN clause.
|
TrgEmpID TrgName
-------- -------
100 Mary
101 Sara
102 Stefano
|
The example input stream results show that the input stream results depend on the combination of WHEN clauses. Now assume that you want to perform the following actions on the target table based on that input stream:
-
Insert rows from the source table when the employee ID does not exist in the target table and the source employee name starts with 'S'.
-
Delete rows in the target table when the target employee name starts with 'S' and the employee ID does not exist in the source table.
To perform these actions, the following WHEN clauses are required:
-
WHEN NOT MATCHED BY TARGET THEN INSERT
-
WHEN NOT MATCHED BY SOURCE THEN DELETE
As described in the previous table, when both of the WHEN NOT MATCHED clauses are specified, the resulting input stream is a full outer join of the source and target tables. Now that the input stream results are known, consider how the insert, update, and delete actions will be applied to the input stream.
As stated earlier, the WHEN clauses specify the actions to take based on the results of the ON clause and any additional search criteria specified in the WHEN clauses. In many cases, the search conditions specified in the ON clause produces the required input stream. However, in the example scenario, the insert and delete actions require additional filtering to restrict the affected rows to those that have an employee name that starts with 'S'. In the following example, the filtering conditions are applied to the WHEN NOT MATCHED BY TARGET and the WHEN NOT MATCHED BY SOURCE. The output from the statement shows that the expected rows from the input stream are corrected, inserted, or deleted.
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
THEN DELETE
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO
Here are the results of the OUTPUT clause.
$action EmployeeID EmployeeName EmployeeID EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 104 Steve NULL NULL
(3 row(s) affected)
Reducing the number of rows in the input stream early in the process by specifying the additional search condition to the ON clause (for example, by specifying ON Source.EmployeeID = Target.EmployeeID AND EmployeeName LIKE 'S%') might seem to improve query performance. However, doing so can cause unexpected and incorrect results. Because the additional search conditions specified in the ON clause are not used for matching the source and target data, they can be misapplied.
The following example demonstrates how incorrect results can occur. The search condition for matching the source and target tables and the additional search condition for filtering rows are both specified in the ON clause. Because the additional search condition is not required to determine source and target matching, the insert and delete actions are applied to all input rows. In effect, the filtering condition EmployeeName LIKE 'S%' is ignored. When the statement is run, the output of the inserted and deleted tables shows that two rows are incorrectly modified: Mary is incorrectly deleted from the target table, and Bob is incorrectly inserted.
-- MERGE statement with join conditions that produce unexpected results.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID AND T.EmployeeName LIKE 'S%'
AND S.EmployeeName LIKE 'S%' )
WHEN NOT MATCHED BY TARGET
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*;
ROLLBACK TRAN;
GO
Here are the results of the OUTPUT clause.
$action EmployeeID EmployeeName EmployeeID EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 100 Mary
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 103 Bob NULL NULL
INSERT 104 Steve NULL NULL
(5 row(s) affected)
Search Condition Guidelines
The search conditions used to match the source and target rows and the additional search conditions used to filter rows from either the source or target must be specified correctly to ensure that correct results are obtained. We recommend following these guidelines:
-
Specify only search conditions in the
ON <merge_search_condition> clause that determine the criteria for matching data in the source and target tables. That is, specify only columns from the target table that are compared to the corresponding columns of the source table.
-
Do not include comparisons to other values such as a constant.
To filter out rows from the source or target tables, use one of the following methods:
-
Specify the search condition for row filtering in the appropriate WHEN clause. For example,
WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
-
Define a view on the source or target that returns the filtered rows and reference the view as the source or target table. If the view is defined on the target table, any actions against it must satisfy the conditions for updating views. For more information about updating data by using a view, see Modifying Data Through a View.
-
Use the WITH <common table expression> clause to filter out rows from the source or target tables. This method is similar to specifying additional search criteria in the ON clause and may produce incorrect results. We recommend that you avoid using this method or test thoroughly before implementing it.