Re: WHERE IN query

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 26 Apr 1999 11:55:01 GMT
Message-ID: <372653d2.3839360@192.86.155.100>

A copy of this was sent to "Joerg Leute" <leute_at_itdesign.de> (if that email address didn't require changing) On Fri, 23 Apr 1999 09:01:51 +0200, you wrote:

>Hi Timothy
>
>That was it! Since I have Version 7.3.2.2.1 the standard form did not work,
>but yours did.
>

What didn't work? the "( x, y ) in ( select a, b from t )" syntax works in all v7.x/8.x versions of Oracle on all platforms. Consider:

SQL> select * from emp
  2 where ( empno, ename ) in ( select empno, ename from emp where mgr is null)   3 /

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------

---

      7839 king PRESIDENT 17-NOV-81 5000 10

SQL> select * from v$version;

BANNER

---

Oracle7 Server Release 7.1.6.2.0 - Production Release PL/SQL Release 2.1.6.2.0 - Production
CORE Version 2.3.7.1.0 - Production (LSF Alpha) TNS for SVR4: Version 2.1.6.0.0 - Production NLSRTL Version 2.3.6.0.0 - Production

The concatenate trick will disallow use of indexes and perform (in general) slower then "( x, y ) in ( select a, b from t )"

>Thanks
>
>Joerg
>
>>
>>As all earlier persons were addressing the fact that it seems you want both
>>fields to match may I suggest this instead.
>>
>>Select * from Table1 where ID||Name in (select ID2||Name2 from Table2)
>>
>>This may or may not be faster, but from my side of the table it
>>is easier to read and almost self-documenting.
>>
>>Timothy
>
>
 

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 Mon Apr 26 1999 - 06:55:01 CDT