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

I have a table of开发者_JAVA技巧 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?

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.

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.

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.

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.

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

SELECT ItemID
FROM ChildItems
EXCEPT
SELECT ItemID 
FROM Products