CODEWITHSUNDEEP
sqlsql-server
Slee
Slee

Reputation: 28268

find values that do not belong in 2 tables using SQL Server 2005

I have a table of ChildProducts that has a column called ItemID that references a column called ItemID in a Products table.

I need to be able to quickly list all of the ItemID's in ChildItem's that do not exist in products.

What is the most efficient way to do this?

Upvotes: 2

Views: 407

Answers (5)

Martin Smith
Martin Smith

Reputation: 453897

You can also use the often forgotten EXCEPT operator to perform this anti semi join.

SELECT ItemID
FROM ChildItems
EXCEPT
SELECT ItemID 
FROM Products

Upvotes: 0

James Johnson
James Johnson

Reputation: 46067

Try something like this:

SELECT  *
FROM    ChildItems c
WHERE   c.ItemID NOT IN(SELECT p.ItemID FROM Products p)

If you're looking to delete the orphaned records, you can do this:

DELETE FROM ChildItems c
WHERE  c.ItemID NOT IN (SELECT p.ItemID FROM Products p)

Here's another way you can find the orphaned records:

SELECT  *
FROM    ChildItems c
        LEFT OUTER JOIN Products p
           ON c.ItemID = p.ItemID
WHERE    p.ItemID IS NULL

EDIT

Assuming that you're looking to eliminate this issue after removing the orphaned records, I would suggest adding a foreign key to the ChildItems table, and using a cascading delete when removing records are deleted from the Products table.

Upvotes: 0

Rob Paller
Rob Paller

Reputation: 7786

NOT EXISTS Clause would get the job done:

    SELECT  *
    FROM    ChildItems c
    WHERE   NOT EXISTS (SELECT 'x' FROM Products p WHERE p.ItemID = C.ItemID)

Sorry - Syntax error made in haste when responding. Thanks @JNK.

Upvotes: 1

KreepN
KreepN

Reputation: 8598

select c.ItemID 
from ChildProducts c left outer join Products p on c.ItemID = p.ItemID 
where p.ItemID IS NULL

EDIT: JNK's answer would be the best bet though, see it below.

Upvotes: 1

JNK
JNK

Reputation: 65217

SELECT ItemID
FROM ChildItems
WHERE ItemID NOT IN (SELECT ItemID FROM Products)

NOT IN or EXISTS will run faster than a JOIN for this since they both short circuit.

Upvotes: 3

Related Questions