| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: WHERE IN query
A copy of this was sent to bonanos_at_yahoo.com
(if that email address didn't require changing)
On Thu, 22 Apr 1999 12:02:34 GMT, you wrote:
>Hi
>
>> 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
>
>select * from table1
>where id in (select id_2 from table2)
>and name in (select name from table2)
>
>
>you'll execute the second query twice, but it should be cached etc
>
No, the syntax would be:
select * from table1 where ( id, name ) in ( select id_2, name_2 from table2 )
Your query would return a very different answer from above. Consider:
SQL> create table table1 ( id int, name varchar2(20) ); Table created.
SQL> insert into table1 values ( 1, 'x' ); SQL> insert into table1 values ( 2, 'y' ); SQL> insert into table1 values ( 3, 'z' );
SQL> create table table2 ( id2 int, name2 varchar2(20) ); Table created.
SQL> insert into table2 values ( 1, 'y' ); SQL> insert into table2 values ( 2, 'x' ); SQL> insert into table2 values ( 3, 'z' );
SQL> select * from table1
2 where id in ( select id2 from table2 )
3 and name in ( select name2 from table2 )
4 /
ID NAME
---------- ------------------------------
1 x
2 y
3 z
SQL>
SQL> select * from table1 where (id,name) in (select id2,name2 from table2 )
2 /
ID NAME
---------- ------------------------------
3 z
Your query returns all rows in this example, the other one only 1 row -- where the tuples actually match.
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |