How to find records that exist in one table but not the other when each record has variations

Question

I want to find only records that exist in one table, that don't exist in another table. The part I'm struggling with is that one item can have multiple variations.

Example Table one

ItemNumber     | VendorName
1              | Frito Lay
1              | Joe's Chips
1              | Chips Galore

Example Table two

Item Number    | Vendor Name
1              | Frito Lay
1              | Joe's Chips

I'm looking to return only the records that exist in table one that don't exist in table two. The tables are identical in schema btw.

The record I'm looking to return is 1, Chips Galore

I am using SQL Server 2008.

Soooo, the result should be `1, Chips Galore`? – Lamak Mar 01 '16 at 19:11 — Lamak, Mar 01 '16 at 19:11
In relational algebra, this is known as an anti join. – Bacon Bits Mar 01 '16 at 19:23 — Bacon Bits, Mar 01 '16 at 19:23

score 2 · Answer 1 · answered Mar 01 '16 at 19:14

You can use NOT EXISTS:

SELECT *
FROM dbo.TableOne T1
WHERE NOT EXISTS(SELECT 1 FROM dbo.TableTwo
                 WHERE ItemNumber = T1.ItemNumber 
                 AND VendorName = T1.VendorName);

Another option could be using EXCEPT:

SELECT *
FROM dbo.TableOne
EXCEPT
SELECT *
FROM dbo.TableTwo;

score 1 · Answer 2 · answered Mar 01 '16 at 19:12

So do you want to return Chip's Galore out of the first table?

SELECT * FROM [Table One]
LEFT JOIN [Table Two] ON [Table One].[Item Number] = [Table Two].[Item Number]
AND [Table One].[Vendor Name] = [Table Two].[Vendor Name] 
WHERE [Table Two].[Vendor Name] IS NULL

Or using NOT EXISTS

SELECT *
FROM [Table One] Tab1
WHERE NOT EXISTS (
    SELECT 1 
    FROM [Table Two] Tab2
    WHERE Tab1.[Item Number] = Tab2.[Item Number]
    AND Tab1.[Vendor Name] = Tab2.[Vendor Name] )

score 0 · Accepted Answer · answered Mar 01 '16 at 19:20

SELECT ItemNumber, VendorName
 from Table1
except select ItemNumber, VendorName
 from Table2

This will select everything in the "first" set that is not also found in the "second" set... and it checks every specified column in every row column in doing it. (Duplicates are removed.)

How to find records that exist in one table but not the other when each record has variations

3 Answers3