SQL "IN" statement for multiple columns

distinct, sql

Solution

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.

Problem

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?

Original source