views:

59

answers:

6

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?

A: 

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 ) 
James Curran
This has a bug in it. a='ab' b='cd' c='ef' will match with a='abcd' b='e' c='f'...
David Oneill
You'd need to be careful to make sure you don't get collisions based on column content. I'd put in separators so that columns aren't run together accidentally resulting in false positives: 'a' + 'bb' + 'c" == 'ab' + 'b' + 'c'
tvanfosson
Won't work for certain strings though... a+b could be "abc" for a="ab" and b="c", or a="a" and b="bc".
Corey
Yeah, yeah, yeah... I was going to mention that, but since it was just a hack, I went for the quick solution.
James Curran
+2  A: 

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

Vinko Vrsalovic
Oracle has it too.
Tony Andrews
A: 

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.

C. Ross
My question isn't how to do it - I know of a couple of ways. I just wanted to know if the syntax was correct, as someone posted it and no one objected.
froadie
@froadie Pardon my, misunderstanding. Other people have answered your question correctly I see.
C. Ross
+1  A: 

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
Tony Andrews
He is asking about 'NOT IN'. Not just 'IN'
David Oneill
@David Oneill - can't you assume that if one works, so does the other?
froadie
Added NOT IN example just to be sure!
Tony Andrews
... so maybe that downvote can be removed? ;-)
Tony Andrews
@froadie, no, you can't assume that. Or at least, I wouldn't.
David Oneill
@tony: got rid of down vote :)
David Oneill
A: 

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)
Corey
A: 

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/

jasonk