Re: WHERE IN query

Response is inline

In article <371F6435.75942E7_at_lucent.com>,   Patrick Suppes <psuppes_at_lucent.com> wrote:
> Anyone,
>
> What are the advantages/disadvantages of the syntax
>
> select * from table1
> where (id1, name1) in
> (select id2, name2 from table2)
>
> compared to the syntax
>
> select table1.* from table1, table2
> where id1 = id2
> and name = name2

This (the second) created a "better" explain plan on a q&d query I through together joining a table to itself. However, there are cases where a simple join is not adequate for the task at hand. IE; a table that uses a timestamp to define the effective date/time of something like and address would require that the latest date, that is not in the future define the row to be returned.

This would require a group by clause such as:   select * from table1

     where (id1, name1, eff_date) in
          (select id1, name1, max(eff_date) from table1
           where eff_date <= TRUNC(SYSDATE)
           group by id1, name1)

Yes this can be written with an inline view and a join from it. It is a little more complex that the original message.

Your Milage May Vary, To Each His/Her Own, etc.

James

>
> Patrick Suppes
>
> 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)
> >
> > Thanks for your help
> >
> > Joerg
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 22 1999 - 18:24:58 CDT