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 .?
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?
Yes the result of the two queries is same , Please find the below SQL and target table ddl,
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?
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.
For analysis purpose, you can load both into some temporary tables. Once loaded, then you can compare the missing records, how it happens.
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.