Left Outer Join

[jarshad]

Hi,

Can you pls. write a sample code for Left Outer Join in Oracle.

Thanx,

Arshad

[kmesser]

Oracle doesn't use the terminology "Left Outer Join". An outer join is identified by a (+) sign on the column which will/could be deficient in rows.

for example:

select arow from tablewithextrarows x, tablewithmissingrows m where x.id = m.id(+)

This will return all rows in tablewithextrarows, along with any rows in tablewithmissingrows which have the same id as those rows from tablewithextrarows.

[shawish_sababa]

Hi jarshad

remmember, when you us outer join you get a full table scan, no index will help you to get better preformance

[chrisrlong]

Sorry, shawish_sababa, but that is not at all true. There are many ways for the optimizer to resolve an outer join. Look at it this way

T1.Col1 T2.Col1
1 1
2 2
3 4
5 5
... ...

Now, we have a query:

SELECT
---T1.Col1,
---T2.Col1
FROM
---T1,
---T2
WHERE
---T1.Col1 < 10 AND
---T2.Col1(+)= T1.Col1

Now, the optimizer will look at this and first, decide to use an index on T1, to get to the small number of rows that are less than 10, agreed?

The optimizer will then have the following result set in hand:

T1.Col1
1
2
3
5

Now, it has to outer-join this to T2. It rolls through each of the rows *in hand*, and does an indexed search into T2 to see if it exists. If it finds a row in T2, it pulls that row and adds the T2 fields to the result set. If it does not find the row, it will add null fields to the result set.

The end result is, of course:

T1.Col1 T2.Col1
1 1
2 2
3 NULL
5 5

The point is that an outer join means a *lot* logically, and *will* affect the plans for your statement, but it certainly does *not* mean a table scan.

Sorry, just thought that needed to be cleared up. I've seen that mistaken notion a few times on the boards lately.

- Chris

[shawish_sababa]

Hi chrisrlong

you are more then right! I my answer i should have stated that if you only use outerjoin then you end up with a full scan! i should have put thatpart in my posting.

sorry, and thank you for your reply