JOINs against a subquery

I have a pretty complicate query that produces a sales summary, something similar to this:

customer_id sales_amount
=========== ============

           1        99.95
           2       125.50
           3         1.25

The query is complicate because it reads the table where individual sales are stored and it needs to filter out some lines according to variable criteria.

I need to print this data, together with the customer details (full name, city, etc.). So I join this resultset with the customers table:

customer_id full_name city
=========== ========= ======

           1 Joe M.    Paris
           3 Jack X.   London

(Yep, there ain't foreign keys; I'm reading from a third-party database).

My worry is that I must be missing something basic about Oracle. An INNER JOIN works as expected and is almost as fast as the main query:

SELECT sa.customer_id, sa.sales_amount, cu.full_name, cu.city FROM (
        // Complicate inner query, returns only a few rows ) sa
INNER JOIN customers cu ON sa.customer_id=cu.customer_id

customer_id sales_amount full_name city
=========== ============ ========= ======

           1        99.95 Joe M.    Paris
           3         1.25 Jack X.   London

However, a LEFT JOIN takes years to run. The explain plan is completely different and has a cost that's more than ten times larger. So I cannot get this:

customer_id sales_amount full_name city
=========== ============ ========= ======

           1        99.95 Joe M.    Paris
           2       125.50 NULL      NULL
           3         1.25 Jack X.   London

I'd say the outer query would run after the inner query is completed but it doesn't seem to be the case. Am I making a wrong assumption somewhere?

--

• http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
• Mi sitio sobre programaci�n web: http://bits.demogracia.com
• Mi web de humor al ba�o Mar�a: http://www.demogracia.com --