Re: sub-select in SQL

[CRPence <CRPbottle@xxxxxxxxx>]

  Proof by counter-example can easily show otherwise.  One should not 
be so sure to discount a possible implementation for the IN predicate 
derived from a SELECT.  Again, one should ask the optimizer what plan 
was built for the given query, to best know the answer to the question 
by the OP.

Regards, Chuck

On 3/2/11 10:50 AM, Schutte, Michael D wrote:
> The OP original query is NON correlated. There's nothing for the two
> files to join on. In that example, the sub query would get executed
> once. However, if there was a.fld = b.fld in the sub query then it
> would/could/should execute a join, thus repeating the subquery over
> and over again for each row from table a.
>
> CRPence on Wednesday, March 02, 2011 1:32 PM wrote:
> > On 3/2/11 10:02 AM, hockchai Lim wrote:
> > > For the SQL below, does any one know if the sub-select in the
> > > where statement gets execute only once or it gets execute for
> > > each row in BL1025INP file?
> >
> > The answer is in the domain of the optimizer, not of a reviewer of
> > the SQL. Even if known what the optimizer chooses today, does not
> > imply what the optimizer will choose tomorrow. However very
> > probably the subselect is not performed, not even as a
> > parameterized query, for every row in a full table scan. Most
> > likely that query is implemented as a join on INSEQ# with either an
> > existing index on that column, a temporary index on that column, or
> > a full table scan if the cardinality of the matches can be inferred
> > or cardinality of the distinct values is low according to available
> > statistics.
> >
> > > update
> > >      BL1025INP a
> > >    set
> > >      a.INERRFLG = 'Y'
> > >    where
> > >      a.inseq# in (
> > >       select inseq#
> > >        from BL1025INP b
> > >        where
> > >          b.inerrFlg = 'Y'
> > >        group by
> > >          INSEQ#)
> >         /* perhaps adding to the above WHERE clause: */
> >        and a.inerrflg<>'Y' /* no reason to change those.? */
> >
> > So... Ask what the optimizer decided, EXPLAIN the statement
> > [query] in iNav. Even the deprecated "optimizer debug messages" are
> > likely to explain the query sufficiently to infer the subquery is
> > not run  repeatedly.