CODEWITHSUNDEEP
sql-server
user1330974
user1330974

Reputation: 2616

Finding the difference between two tables based on a common key (Microsoft SQL Server)

I have two tables that share a common key and a few different columns. The common key is CampaignID. One of the tables has slightly more CampaignID's than the other one and I would like to find out the difference between those two tables. Currently, I am using LEFT OUTER JOIN and CTE to merge these two tables first, then by inspecting NULL columns in the CTE result set derived in the earlier step to count the difference in CampaignID column. For instance,

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

But this seems inefficient to me. Is there a more efficient/faster way to compare the difference in a specific column between two tables in Microsoft SQL Server? Thank you for your answers!

NOTE: I'm new to Microsoft SQL Server and SQL in general.

Upvotes: 1

Views: 84

Answers (2)

Gareth Lyons
Gareth Lyons

Reputation: 1972

Not exists is likely more efficient than a left join. This'll work too:

SELECT CampaignID FROM CampaignDetails
EXCEPT
SELECT CampaignID FROM CampaignOnlineDetails

Just reverse order to check the other way round.

Upvotes: 1

T McKeown
T McKeown

Reputation: 12847

If one table has more than the other then how bout:

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)

If either may have one campaign or not then UNION this:

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)

Upvotes: 2

Related Questions