NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server
三种方式
NOT IN
SELECT l.id, l.value
FROM [dbo].t_left l
WHERE l.value NOT IN
(
SELECT value
FROM [dbo].t_right r
)
NOT EXISTS
SELECT l.id, l.value
FROM [20090915_anti].t_left l
WHERE NOT EXISTS
(
SELECT NULL
FROM [20090915_anti].t_right r
WHERE r.value = l.value
)
|
LEFT JOIN / IS NULL
SELECT l.id, l.value
FROM [20090915_anti].t_left l
LEFT JOIN
[20090915_anti].t_right r
ON r.value = l.value
WHERE r.value IS NULL
|
以下是我实际操作的实例
insert into [HTPMPlusPlus].[dbo].[BssEPC_Business]([Status] ,[Type] ,[GroupID] ,[Code] ,[ShortName] ,[Name] ) (SELECT [Status] ,[Type] ,[GroupID] ,[Code] ,[ShortName] ,[Name] FROM [EasyCPM].[dbo].[BSS_Business] l WHERE NOT EXISTS (SELECT r.Code FROM [HTPMPlusPlus].[dbo].[BssEPC_Business] r WHERE r.Code = l.Code ) and l.Code is not null)
insert into ADB.[dbo].A(a,b,c) (select a,b,c from BDB.[dbo].B)
大致逻辑 就是 insert into 目标表 （列名，列名） （select 列名，列名 from 源数据表，WHERE NOT EXISTS（去除重复数据））
