Re: WHERE IN query

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 22 Apr 1999 12:39:17 GMT
Message-ID: <372617f8.6289934@192.86.155.100>

A copy of this was sent to Frank van Bortel <f.van.bortel_at_vnl.nl> (if that email address didn't require changing) On Thu, 22 Apr 1999 14:00:57 +0200, you wrote:

>Joerg Leute wrote:
>
>> Hi everybody
>>
>> Isn't it possible to extend a WHERE IN query over 2 columns - first column
>> is number and second is char) like
>>
>> SELECT * FROM TABLE1 WHERE (ID, NAME) IN (SELECT ID_2, NAME_2 FROM TABLE2)
>>
>
>No - how would I (or the rdbms) need to read this? Is this an AND or an OR -
>what about
>NULLS for ID or NAME?
>use the AND, OR and outer joins to resolve all that

Yes:

SQL> select ename from emp
  2 where ( ename, empno ) in ( select ename, empno from emp

  3                               where ename like '%A%' )

  4 /

ENAME

---

ALLEN
WARD
MARTIN
BLAKE
CLARK
ADAMS
JAMES its "where ( set ) in ( select <set> from t)" and set can have 1 or more columns

it compares the tuples like it would in a join. Its an AND (ename=ename AND empno=empno) and NULLs are never equal (nor are they NOT equal) so the comparision fails. for example, in emp -- there is a null mgr so:

SQL> select ename from emp
  2 where ( ename, mgr ) in ( select ename, mgr from emp );

ENAME

---

ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD 13 rows selected.

returns 13, not 14 rows since a null mgr is not equal nor not not equal for one of them....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Apr 22 1999 - 07:39:17 CDT