SQL "IN" statement for multiple columns

Question

I would like to filter Name,X combinations for which is never X=Y Let's assume the following table:

*Name*     *X*      *Y*
   A        2        1
   A        2        2   <--- fulfills requirement for Name=A, X=2
   A       10        1
   A       10        2
   B        3        1
   B        3        3   <--- fulfills requirement for Name=B, X=3
   B        1        1   <--- fulfills requirement for Name=B, X=1
   B        1        3

So I would like to return the combination Name=A, X=10 for which X=Y is never true. This was my approach (which is syntactically incorrect)

SELECT * 
FROM TABLE 
WHERE NAME
     , X NOT IN (SELECT DISTINCT NAME
                       , X 
                 FROM TABLE 
                 WHERE X=Y)

My problem is the where statement which cannot handle multiple columns. Does anyone know how to do this?

Answer 1

Just put the columns into parentheses

SELECT * 
FROM TABLE 
WHERE (NAME, X) NOT IN (SELECT NAME, X 
                        FROM TABLE WHERE X=Y);

The above is ANSI standard SQL but not all DBMS support this syntax though.

A distinct is not necessary for a sub-query for IN or NOT IN.

However NOT EXISTS with a co-related sub-query is very often faster that an NOT IN condition.