Re: DBA FIGHT

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