Re: DBA FIGHT

In terms of performance a direct table join will produce the best performance, a correlated sub query with an EXISTS clause will produce better results than a query using the IN function.

It may not always be possibility to get the results you want with a direct table join if one of the columns being matched is not a unique index ie returns more than one row you will get a cartesian join. Assuming you only want to return one row per matched item then you'll have to use a correlated sub query:

select *
from table1 t1
where exists (

   select 'X'
   from table2 t2
   where t2.a = t1.a);

assuming column a is a primary key in table1 and a foreign key in table2.

If you do have to do a correlated sub query try using the EXISTS clause.

btw: Your queries are still wrong! (your where clause refers to the same table alias and column name on both sides of the operator).

btw2: Be good to your dBA! Care and feeding of a dBA is a skill well worth developing.

-GP

On Tue, 24 Jun 2003 15:17:26 +0200, "denaro" <denaro_at_freemail.it> wrote:

>I am sorry but the hot make me crazy.
>
>the right samples are:
>
>select *
>from table1 t1
>where t1.a in
> (
>select t2.a
>from fable2 t2
>where t1.b = t1.b
>);
>
>select *
>from table1 t1, table2 t2
>where t1.a in t2.a
>and t1.b = t1.b
>
>
>
>"andrewst" <member14183_at_dbforums.com> ha scritto nel messaggio
>news:3036987.1056453423_at_dbforums.com...
>>
>> Originally posted by Denaro
>> > These queries are equivalents?
>> > Same results?
>> > Same performance?
>> >
>> > select *
>> > from table1 t1
>> > where t1.a in
>> > (
>> > select t2.a
>> > from fable2 t2
>> > where t1.b = t1.a
>> > )
>> >
>> >
>> > select *
>> > from table1 t1
>> > table2 t2
>> > where t1.a in t2.a
>> > and t1.b = t1.a
>> They are equivalent in that both are invalid queries, and neither makes
>> much sense. Presumably "fable1" should be "table1" in the first query,
>> and you are missing a comma in the second.
>>
>> Correct those errors and they give different results:
>>
>> SQL> select *
>> 2 from table1 t1
>> 3 where t1.a in
>> 4 (
>> 5 select t2.a
>> 6 from fable2 t2
>> 7 where t1.b = t1.a
>> 8 );
>>
>> A B
>> ---------- ----------
>> 2 2
>> 4 4
>> 6 6
>> 8 8
>> 10 10
>>
>> SQL> select *
>> 2 from table1 t1
>> 3 table2 t2
>> 4 where t1.a in t2.a
>> 5 and t1.b = t1.a;
>>
>> A B A
>> ---------- ---------- ----------
>> 2 2 2
>> 2 2 2
>> 4 4 4
>> 4 4 4
>> 6 6 6
>> 6 6 6
>> 8 8 8
>> 8 8 8
>> 10 10 10
>> 10 10 10
>>
>> I suggest you get the queries correct and doing the same thing before
>> you try to measure performance.
>>
>> --
>> Posted via http://dbforums.com
>
Received on Tue Jun 24 2003 - 15:26:53 CDT