[thelist] Problem with SQL Intersect query

Joel Lieberman joel_lieberman at yahoo.com
Sun Jan 6 09:32:45 CST 2002


Rudy -

The union stuff isn't a problem, it is the intersect
that is not working.  I probably could get the
intersect to work by recoding as an exclusion list
using a SELECT WHERE... NOT IN ('A',....,'X')  or
combining this with some unions to get the desired
effect, but I hate the idea of doing repeated full
table scans which are obligate when an IN clause is
invoked.  It kills performance and besides, it isn't
"pretty".

The advantage to the union and intersect operators is
that Oracle has tuned the query engine to maximize
performance behind the scenes when we use these calls.

Thanks anyway...I can't crack it either.

Joel
--- rudy <r937 at interlog.com> wrote:
> >So if the table has authors and I ask for all
> records
> >where author is "A" union all records where author
> is
> >"B" etc, I get the correct records.
> >
> >With intersect used as a subsetting condition
> >(exclusion) they cannot be composed with more than
> a
> >single intersection.
> 
> hi joel
> 
> patient:  doc, it hurts when i do that
> doctor: so don't do that
> 
> INTERSECT as a set operator is all fine and good but
> not if you can't get
> it to work, eh?
> 
> i can't tell from your description what's wrong, i'd
> have to see the sql
> 
> 
> of course, there were ways of doing these sorts of
> things before these set
> operators were added to sql, so one option is to
> code it up the old way
> 
> for example,
> 
> >  records where author is "A"
> > union all
> >  records where author is "B"
> 
> could be done with
> 
>     where author in ("A", "B")
> 
> ;o)
> 
> 
> rudy
> 
> 
> -- 
> For unsubscribe and other options, including
> the Tip Harvester and archive of TheList go to:
> http://lists.evolt.org Workers of the Web, evolt ! 


__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/




More information about the thelist mailing list