results of left outer join and =(+) in oracle are different

[rneel]

I have a query which performs the following

Select a2 from table_a left outer join table_b on table_a.a1=table_b.b1 and table_b.b1=999999

The result of set of this is different from

select a2 from table_a,table_b where table_a.a1=table_b.b1 and table_b.b1=999999
 

I am using oracle 9i... Why is it different...

Not too sure...

regards
rneel

[chandu031]

Hi,

Your first query will return all the rows of table_a with a 'NULL' in places where the join condition fails. While the second query will first filter out those rows which dont satisfy the filter condition and return only the matched rows. This can be considered like an inner join.

[rneel]

Sorry

I had missed the outer join in my second query

it is

select a2 from table_a,table_b where table_a.a1=table_b.b1 (+) and table_b.b1=999999

and the result set of this is different from

select a2 from table_a left outer join table_b on table_a.a1=Table_b.b2 and table_b.b1=999999

Hi,

Your first query will return all the rows of table_a with a 'NULL' in places where the join condition fails. While the second query will first filter out those rows which dont satisfy the filter condition and return only the matched rows. This can be considered like an inner join.

[debasisdas]

Hi
in your first query u r using the symbol (+) to specify the outer join.
this is oracle 8 syntax which is still supported for backward compartability.
(+)-is called the predicate or the outer join operator.
It specifies in a join the table on which the (+) is specified,will display only matching records from that table and matching as well as non-matching from other table.

in this case matching only from table_b and matching aswell as non-matching from table_a

Note--when specifying the join the predicate is specified on the right side table in case of a left join and on the left side table in case of a right join.
And performance wise left join is always faster than right join.and

But in your 2nd query u use the key word left outer join.
This us supported version 9i onwards.which is called ISQL join as per ANSI specification.
In this case there is no consfusion of left/right side and specifying the predicate

OUTER JOIN
-----------
OUTER JOINS ARE AN EXTENSION TO INNER JOIN.AN OUTER JOIN RETURNS THE ROWS THAT SATISFY THE JOIN CONDITION AND ALSO THE ROWS FROM ONE TABLE FOR WHICH THERE IS NO CORRESPONDING ROWS IN THE OTHER TABLE.

1.LEFT:-SPECIFIES THAT THE RESULTS BE GENERATED USING ALL ROWS FROM TABLE1.FOR THOSE ROWS IN TABLE1 THAT DON'T HAVE CORRESPONDING ROWS IN TABLE2.NULLS ARE RETURNED IN THE RESULTSET OF TABLE2 COLUMNS.

2.RIGHT:-SPECIFIES THAT THE RESULTS BE GENERATED USING ALL ROWS FROM TABLE2.FOR THOSE ROWS IN TABLE2 THAT DON'T HAVE CORRESPONDING ROWS IN TABLE1.NULLS ARE RETURNED IN THE RESULTSET OF TABLE1
COLUMNS.

3.FULL:-SPECIFIES THAT THE RESULTS BE GENERATED USING ALL ROWS FROM TABLE1 & TABLE2.FOR THOSE ROWS IN TABLE1 THAT DON'T HAVE CORRESPONDING ROWS IN TABLE2.NULLS ARE RETURNED IN THE RESULTSET OF TABLE2 COLUMNS. ADDITIONALLY FOR THOSE ROWS IN TABLE2 THAT DON'T HAVE CORRESPONDING ROWS IN TABLE1,NULLS ARE RETURNED IN THE RESULT SET OF TABLE1 COLUMNS.

[takumar1701]

Hi,
In the first query u need to include a '+' sign for all the conditions which involves the the second table, that is table_b in this case.

So the query:

select a2 from table_a,table_b where table_a.a1=table_b.b1 (+) and table_b.b1=999999

becomes

select a2 from table_a,table_b where table_a.a1=table_b.b1 (+) and table_b.b1(+)=999999

so that the the result set will be same as that of

select a2 from table_a left outer join table_b on table_a.a1=Table_b.b2 and table_b.b1=999999

please check and confirm... thanks!

[CaptainGrebo]

The first script is a left outer join as it says

The second is an inplicit inner join