根据公共密钥查找两个表之间的差异（Microsoft SQL Server）

debugcn 发表于 Dev

用户名

我有两个共享一个公用键的表和一些不同的列。公用密钥为CampaignID。其中一张表的张数CampaignID比另一张表略多，我想找出这两个表之间的区别。当前，我先使用LEFT OUTER JOIN并CTE合并这两个表，然后通过检查在较早步骤中派生NULL的CTE结果集中的列来计算CampaignID列之间的差异。例如，

WITH CTE_Results
     AS (SELECT t1.CampaignID AS cd_CampaignID,
                t2.CampaignID AS cod_CampaignID,
                t1.NAME,
                t2.Vendor
         FROM   CampaignDetails AS t1
                LEFT OUTER JOIN CampaignOnlineDetails AS t2
                             ON t1.CampaignID = t2.CampaignID)

-- Now that I have CTE result, I'll use another SELECT to find the difference
SELECT cd_CampaignID, cod_CampaignID
FROM CTE_Results
WHERE cod_CampaignID is NULL

但这对我来说似乎效率低下。有没有更有效/更快的方法来比较Microsoft SQL Server中两个表之间的特定列中的差异？谢谢您的回答！

注意：我是Microsoft SQL Server和SQL的新手。

麦基文

如果一个表比另一个表多，那么结果如何：

SELECT C1.* FROM CAMPAIGN_WITH_MORE_DATA AS C1
WHERE NOT EXISTS(SELECT * FROM CAMPAIGN_WITH_LESS_DATA AS C2
                                WHERE C2.CAMPAIGN_ID = C1.CAMPAIGN_ID)

如果任何一个可能没有一个广告系列，则UNION此操作：

SELECT C1.Name AS [Col1],
  C1.CAMPAIGN_ID,
  'More Campaigns' AS [Source]
FROM CAMPAIGN_WITH_MORE_DATA AS C1
WHERE NOT EXISTS(SELECT * FROM CAMPAIGN_WITH_LESS_DATA AS C2
                 WHERE C2.CAMPAIGN_ID = C1.CAMPAIGN_ID)
UNION ALL
SELECT C2.Vendor AS [Col1],
  C2.CAMPAIGN_ID,
  'Less Campaigns' AS [Source]
FROM CAMPAIGN_WITH_LESS_DATA AS C2
WHERE NOT EXISTS(SELECT * FROM CAMPAIGN_WITH_MORE_DATA AS C1
                 WHERE C1.CAMPAIGN_ID = C2.CAMPAIGN_ID)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。