Re: Multiple Conditions for an Outer Join?

"Seth Ladd" <seth_at_brivo.net> wrote in message news:228c5cc7.0205021035.6c384126_at_posting.google.com...
> Hello,
>
> I've been searching around for this answer (or work around) but not
> finding much because I'm not sure how to phrase the question. I'm
> hoping someone can point me to some tips or hints.
>
> I'm currently running Oracle 8.1.7 on Solaris. I want to write a
> query like this:
>
> SELECT <stuff> FROM table1, table2
> WHERE table1.id = table2.t1_id (+)
> AND (table2.x (+) = 12345 OR table2.x (+) = 12346)
>
> But I'm not allowed to use OR with outer joins. Is there a tip or
> workaround for this problem? I could use a UNION but that seems
> pretty inefficient to me.

Hi Seth,

Here's an idea that just came up ... what I'd do though, if I were you is EXPLAIN PLAN the following versus a UNION ALL -- note, I'd use the UNION ALL rather than the UNION to avoid the building of a temp table to DISTINCT:

SELECT <stuff> FROM table1, table2 t2, table2 t3 WHERE table1.id = t2.t1_id (+)
AND t2.x (+) = 12345
AND table1.id = t3.t1_id (+)
AND t3.x (+) = 12346

Also, on the above, you're going to need an NVL() on t2/t3 values.

--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo_at_hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts