Full OuterJoin Issue

Database
Fan

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 .?

7 REPLIES
Junior Contributor

Re: Full OuterJoin Issue

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?

Fan

Re: Full OuterJoin Issue

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.
Junior Contributor

Re: Full OuterJoin Issue

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?

Fan

Re: Full OuterJoin Issue

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

Enthusiast

Re: Full OuterJoin Issue

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

Fan

Re: Full OuterJoin Issue

The Target table it self is a Temporary Table.

Enthusiast

Re: Full OuterJoin Issue

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.