Which of these values are NOT in the table?

Question

I have a list of values, and want to know which ones don't already exist in the table. (This is on sql server 2000)

Consider the following:

CREATE TABLE myTable ( foo VARCHAR(10) )
GO

INSERT INTO myTable
        ( foo
        )
        SELECT 'aaa'
        UNION ALL SELECT 'bbb'
        UNION ALL SELECT 'ccc'
GO

SELECT foo
    FROM myTable
    WHERE foo IN ( 'aaa', 'cat', 'bbb', 'dog' ) 
-- returns 'aaa' and 'bbb'

I need to write a query that returns 'cat' and 'dog'.

SELECT foo
    FROM myTable
    WHERE foo ????? ( 'aaa', 'cat', 'bbb', 'dog' ) 
-- returns 'cat' and 'dog'

Is there a simple way to do this in a query?

Due to the way the app interacts with the database, I'd rather not, say, create a temp table

Answer 1

EXISTS is the thing (NOT EXISTS, in fact)

select foo from myTable
WHERE NOT EXISTS (select 1 from myTable where foo in('cat', 'dog'));

Not the cleanest way, neither the cleanest. Soehow, it could work.

Answer 2

You could construct a table with union all and join on that, filtering on the rows that are not matched by the join:

SELECT     myList.foo
FROM       (
               SELECT 'aaa' as foo
               UNION ALL SELECT 'cat'
               UNION ALL SELECT 'bbb'
               UNION ALL SELECT 'dog'
           ) myList
LEFT JOIN  myTable
ON         myTable.foo = myList.foo
WHERE      myTable.foo is null