Re: Is there another way of looking at this query ?

"Randy" <gastcom_at_sympatico.ca> wrote in message news:1152110222.424910.66900_at_l70g2000cwa.googlegroups.com...
> Excuse me if this question has been asked before. I want to find all
> records that satisfy the first query AND I want all the rows that
> satisfy the 2nd query ... that haven't already been included in the 1st
> query.
>
> SELECT A.1, A.2
> FROM A
>
> UNION
>
> SELECT B.1, B.2
> FROM B
> WHERE B.1 NOT IN ( SELECT A.1 FROM A )
>
> Is there a way of NOT re-doing the original query.
>

I don't think there's anything really
efficient - but depending on indexes,
nulls, and data patterns, using subquery factoring may add an edge:

with subq1 as (

    SELECT A.c1, A.c2
    FROM A
)
select c1, c2
from subq1
union
select c1, c2
from b
where c1 not in (selet c1 from subq1)

9i onwards.
If Oracle materializes the subquery (which is the intention) then it will write the result to the temporary tablespace.

Since (as others have noted) UNION eliminates duplicates, you may want to include a DISTINCT in the subquery definition so that you eliminate duplicates as early as possible. But,again, the benefit (or not) depends on the data patterns.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html