9

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?

froadie
  • 79,995
  • 75
  • 166
  • 235

6 Answers6

7

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
  • 1,532
  • 9
  • 12
5

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
  • 330,807
  • 53
  • 334
  • 373
3

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
  • 129,880
  • 21
  • 220
  • 259
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 )
James Curran
  • 101,701
  • 37
  • 181
  • 258
  • This has a bug in it. a='ab' b='cd' c='ef' will match with a='abcd' b='e' c='f'... – David Oneill Feb 12 '10 at 15:01
  • 1
    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 Feb 12 '10 at 15:02
  • 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 Feb 12 '10 at 15:03
  • Yeah, yeah, yeah... I was going to mention that, but since it was just a hack, I went for the quick solution. – James Curran Feb 12 '10 at 15:12
  • You can add the separator that don't exist in those fields, between those fields. I suggest CHAR(0), and be careful for null values : SELECT DISTINCT a, b, c FROM t1 WHERE a+CHAR(0)+b+CHAR(0)+c NOT IN ( SELECT DISTINCT a+CHAR(0)+b+CHAR(0)+c FROM t2 ) – AliReza Nov 24 '21 at 23:28
0

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
  • 31,137
  • 42
  • 147
  • 238
  • 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 Feb 12 '10 at 15:04
  • @froadie Pardon my, misunderstanding. Other people have answered your question correctly I see. – C. Ross Feb 12 '10 at 15:13
0

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
  • 1,580
  • 4
  • 25
  • 33