Re: WHERE IN query

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 27 Apr 1999 11:47:51 GMT
Message-ID: <3725a31a.3119335@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 Mon, 26 Apr 1999 23:54:23 +0200, you wrote:

>Hi Thomas
>
>Thanks for your hint, but your query refers on the same table. Mine selects
>from a different table with different field-lenghts. Actually none of all
>queries on this thread did what i wanted. I guess the problem will be solved
>with my new Oracle 7.3.4 version.
>What i found out until now:
>the query
>select * from table1 where (id, name) in (select id2,name2 from table2)
>did not work because the "where in" statement could not compare the name
>column since it's varchar2.
>the query
>select * from table1 where (id, soundex(name)) in (select id2,soundex(name2)
>from table2)
>worked because the soundex function converted the name to numbers
>
>Joerg
>

Well, they you were comparing apples to oranges (varchars to chars) i presume. You can also (in all versions, don't need a special 7.3.4 on) do the following:

SQL> desc t1

 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)

SQL> desc t2
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    CHAR(10)

so, t1 and t2 have different datatypes for the ename column. A simple:

SQL> select empno, ename
  2 from t1
  3 where ( empno, ename ) in ( select empno, ename from t2 )   4 /

no rows selected

returns no data since 'KING' is not the same as 'KING '

We can either turn 'KING ' into 'KING' via trim:

SQL> select empno, ename
  2 from t1
  3 where ( empno, ename ) in ( select empno, rtrim(ename) from t2 )   4 /

     EMPNO ENAME
---------- ----------

      7369 SMITH
      7499 ALLEN
      7521 WARD

...
14 rows selected.

or we can turn 'KING' into 'KING ' with rpad:

SQL> select empno, ename
  2 from t1
  3 where ( empno, rpad(ename,10) ) in ( select empno, ename from t2 )   4 /

     EMPNO ENAME
---------- ----------

      7369 SMITH
      7499 ALLEN
      7521 WARD

...
14 rows selected.

to make the comparision succeed as well. You don't need to use soundex() which would preclude all indexes (using rtrim() would allow an index on t1 to still be used) and must be evaulated 2 times AND might lead to the wrong answer (soundex() is not a 1-1 function, many strings evaluate to the same soundex)..  

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 Tue Apr 27 1999 - 06:47:51 CDT