I am using outer join, I have two tables tblaccount with 33 records and tblcustchannelacct with 146 records, and I am writing a query to have only tblaccounts matching records with tblcustchannelacct, so only 33 records should come, but no matter how much I change the query I get 146 records.
Queries are below:
*-Select tab1.ACCOUNT_ID, tab2.ACCOUNT_ID
from tblaccount tab1, tblcustchannelacct tab2
where tab1.account_id (+) = tab2.account_id;
Here I used (+) in the WHERE clause for tblaccount to include all the rows that is tblaccount but 146 i.e.: tblcustchannelacct no. of records are showing.
Now I used outer join with keyword of ‘outer join’ instead of (+)
*-Select tab1.ACCOUNT_ID, tab2.ACCOUNT_ID
from tblcustchannelacct tab2
left join tblaccount tab1
on tab1.ACCOUNT_ID= tab2.ACCOUNT_ID
LEFT JOIN keyword returns all rows from the left table tblaccount, even if there are no matches in the right table tblcustchannelacct.
(but showing the same 146 records of tblcustchannelacct).

__________________
How to do programming?

Hi,

if you only want records matching tblaccounts and tblcustchannelacct you should use an inner join:
Further, the old Oracle join syntax is a bit confusing. What you wrote was actually a right outer join (so you indeed got all records from tblcustchannelacct and from tblaccounts only those that match). If you want to have all records from tblaccounts and only those from tblcustchannelacct you need a left outer join as follows:
Just think of the plus-sign as meaning "fill all the blanks for me in, will you"..

I hope this helps.

@disel2010: No matter where I use (+) on the right side or the left side, I still get tblcustchannelacct no. of records


Select tab1.ACCOUNT_ID, tab2.ACCOUNT_ID
from tblaccount tab1, tblcustchannelacct tab2
where tab1.account_id = tab2.account_id(+);

gives exactly the same answer as:

Select tab1.ACCOUNT_ID, tab2.ACCOUNT_ID
from tblaccount tab1, tblcustchannelacct tab2
where tab1.account_id (+) = tab2.account_id;

whereas it should give only 33 records that tblaccount has.

__________________
How to do programming?

Hi arbab,

and does the inner join (without the plus sign) also give you the same number?
Then you are probably just looking at duplicate entries of the same account_id... (guessing the relation between tblaccount and tblcustchannelacct is 1-n)

Try this to see how many unique account_id's you have:

Dear disel2010:

select count (*) from tblaccount
Ans: 38 rows

select count (*) from tblcustchannelacct
Ans:155 rows

SELECT DISTINCT tab1.account_id, tab2.account_id
FROM tblaccount tab1, tblcustchannelacct tab2
WHERE tab1.account_id = tab2.account_id;

Ans: 38 rows

After removing 'Distinct':

SELECT tab1.account_id, tab2.account_id
FROM tblaccount tab1, tblcustchannelacct tab2
WHERE tab1.account_id = tab2.account_id;

Ans: 155 rows

Now no matter where I use (+):

SELECT tab1.account_id, tab2.account_id
FROM tblaccount tab1, tblcustchannelacct tab2
WHERE tab1.account_id = tab2.account_id (+);

or

SELECT tab1.account_id, tab2.account_id
FROM tblaccount tab1, tblcustchannelacct tab2
WHERE tab1.account_id (+)= tab2.account_id;

the answer is same: 155

Conclusion: So left or right outer joins do not work when there is duplication of records?

__________________
How to do programming?

Hi arbab,


the outer join gives the same result as the inner join because all your primary keys of tblaccount are present in tblcustchannelacct.

(As said before, an outer join works as "fill all the blanks for me in, will you"...
and here you don't have missing joins so also no blanks)