Oracle - Inner query takes time-CodePudding

I have two queries:

select * from PRE_DETAIL_REPORT a where item = (select item from apple_skus);
select * from PRE_DETAIL_REPORT a where item IN ('100299122');

the table: APPLE_SKUS only has one item: 100299122

When I run the first query, it takes 2 minutes to execute When I run the second query, it takes 3 seconds to execute

What can be the reason?

CodePudding user response：

you can rewrite it in this way:

select a.* from PRE_DETAIL_REPORT a 
join apple_skus t on t.item = a.item;

CodePudding user response：

Its the way a sql query syntax works

You have manual values for selection in your 2nd query but in the first case you have subquery specified so again a

FROM CLAUSE, N THEN SELECT so, Querying a table will take more time than a hardcode value even when theres a single record

You could try EXISTS as it uses correlated subquery which would be much faster

    Select * from table  t1 where exists (select  1 from table 
    where 
     Item =t1.item)