Geek Answers Handbook

Can you use multiple columns for a query not in the query?

I recently noticed that someone posted this as part of a response to a SO request:

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 cannot use multiple columns for "NOT IN" (" where (a, b, c) ", etc.). Is this the correct SQL syntax? What about MySQL?

+5
source share
6 answers

This is an extension of SQL. Oracle, PostgreSQL and MySQL have this. SQL Server 2005 does not have this. I am not sure about others.

+3
source

Googling , , . :

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)
+2

, , Oracle. :

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

:

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 :

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
+1

, , ( char), :

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

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

. , .

0

More articles:


All Articles