All Forums Database
Raja G 4 posts Joined 09/13
03 Sep 2014
Full OuterJoin Issue

Hi All,
I have two Full Outer Join Queries like below,
Select * from A full outer join B on A.Col1=B.Col1 where A.Col2='value';
Select * from B full outer join A on A.Col1=B.Col1 where A.Col2='value';
Though the Result of the two queries are exactly same , when I try to Insert the result of the above queries in to table , some records are missing from the first Query , and From the second Query the record count is correct.
Can you tell me what is the difference .?

dnoeth 4628 posts Joined 11/04
03 Sep 2014

You wrote FULL join but in fact both are LEFT/RIGHT joins, simply check explain.
A WHERE-condition on the inner table removes the outer join, resulting in:

Select * from A LEFT join B on A.Col1=B.Col1 where A.Col2='value';
Select * from B RIGHT join A on A.Col1=B.Col1 where A.Col2='value';

But both still return the same result, so you should provide more details like the actual SQLs and the DDL. Is the target table empty?

Dieter

Raja G 4 posts Joined 09/13
03 Sep 2014

 
Hi , 
Yes the result of the two queries is same , Please find the below SQL and target table ddl,

SLQ Removed at Posters Request:

 

While I try to insert the data from above query to the mentioned target table , Some records are missing.

 

Thanks,

..Raja

 

 

NOTICE: Poster notified us of the existence of Customer Proprietry Information within the SQL, so it has been removed by DevX Admin's. Forum users are reminded that this is  a public web site and that they are responsible for any content they chose to post here.
 

dnoeth 4628 posts Joined 11/04
04 Sep 2014

Hi Raja,
it's hard to tell, there's no obvious issue.
Can you compare both Explains (SEL vs. INS/SEL) if there's any difference?
Or do a SEL 1 EXCEPT ALL SEL 2 to see the actual different rows?

Dieter

Raja G 4 posts Joined 09/13
04 Sep 2014

Hi ,
There is no difference between the Results, Both Queries return the same no of records ,
The Problem is while inserting the records , some records are not inserted into the target table while inserting from the above query.
Thanks,
..Raja

Raja_KT 1246 posts Joined 07/09
04 Sep 2014

For analysis purpose, you can load both into some temporary tables. Once loaded, then you can  compare the missing records, how it happens.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Raja G 4 posts Joined 09/13
04 Sep 2014

The Target table it self is a Temporary Table.

Raja_KT 1246 posts Joined 07/09
04 Sep 2014

By temporary :) , I mean a table (not VTT) where you can load and do an analysis. If you get a diff , perhaps, then you can narrow down the doubts.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.