Can you use multiple columns for a not in query?

Question

I recently saw someone post this as part of an answer to an SO query question:

SELECT DISTINCT a, b, c 
FROM t1 
WHERE (a,b,c) NOT IN 
   ( SELECT DISTINCT a,b,c FROM t2 )

I'm a bit confused, as I always thought that you can't use multiple columns for "NOT IN" ("where(a,b,c)", etc.). Is this correct SQL syntax? And how about MySQL?

Answer 1

Not that I'm aware of, but if thy're character type (or can be converted to char types), you can fake it:

SELECT DISTINCT a, b, c  
FROM t1  
WHERE a+b+c NOT IN  
   ( SELECT DISTINCT a+b+c FROM t2 ) 

Answer 2

It's a SQL extension. Oracle, PostgreSQL and MySQL have it. SQL Server 2005 does not have it. I'm not sure about others.

Answer 3

Try this

SELECT DISTINCT a, b, c  
FROM t1,
(SELECT DISTINCT a,b,c FROM t2) as tt
WHERE t1.a NOT IN tt.a
AND t1.b NOT IN tt.b
AND t1.c NOT IN tt.c

Note: This has not been tested, it hasn't even been proven correct.

Answer 4

It certainly does work in Oracle. Quick contrived example:

SQL> select ename, job, deptno from emp
  2  where (ename, deptno) in
  3  ( select ename, deptno from emp
  4    where job = 'MANAGER'
  5  );

ENAME      JOB           DEPTNO
---------- --------- ----------
JONES      MANAGER           20
CLARK      MANAGER           10
PARAG      MANAGER           30

This also works:

SQL> select ename, job, deptno from emp
  2  where (ename, deptno) in (('JONES',20),('CLARK',10));

ENAME      JOB           DEPTNO
---------- --------- ----------
JONES      MANAGER           20
CLARK      MANAGER           10

NOT IN too:

SQL> select ename, job, deptno from emp
  2  where (ename, deptno) not in
  3  ( select ename, deptno from emp
  4    where job = 'MANAGER'
  5  );

ENAME      JOB           DEPTNO
---------- --------- ----------
SMITH      CLEANER           99
SCOTT      ANALYST           20
KING       PRESIDENT         10
FORD       ANALYST           20
MILLER     CLERK             10

Answer 5

Googling it suggests that it will work on some databases but not others. You can use this instead:

SELECT DISTINCT a, b, c 
FROM t1 
WHERE NOT EXISTS
   (SELECT 1 FROM t2 
    WHERE t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c)

Answer 6

Others have already answered the question, but as a performance suggestion, if you're dealing with data of any significant size always use the EXISTS statement rather than IN. It will be faster in almost every case.

http://decipherinfosys.wordpress.com/2007/01/21/32/