By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,072 Members | 530 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,072 IT Pros & Developers. It's quick & easy.

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

P: 4
I have a query which performs the following

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. select a2 from table_a,table_b where table_a.a1=table_b.b1 and table_b.b1=999999
  2.  
I am using oracle 9i... Why is it different...

Not too sure...

regards
rneel
Apr 28 '07 #1
Share this Question
Share on Google+
5 Replies

chandu031
Expert
P: 78
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.
Apr 29 '07 #2

P: 4
Sorry

I had missed the outer join in my second query

it is

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. 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.
May 1 '07 #3

debasisdas
Expert 5K+
P: 8,127
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.
May 2 '07 #4

P: 1
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:
Expand|Select|Wrap|Line Numbers
  1. select a2 from table_a,table_b where table_a.a1=table_b.b1 (+) and table_b.b1=999999
becomes

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. 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!
May 17 '07 #5

P: 1
The first script is a left outer join as it says

The second is an inplicit inner join
Nov 13 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.