Two identical queries that give different results

I have 2 queries: one is written in ANSI SQL, another is written using oracle dialect. I think that they both must give the same resultset, but it is no true. First query gives 385 rows and the second - only 25

First:

SELECT  idclient, cl.surname, sum(sub1.s) 
    FROM client cl 开发者_JS百科JOIN incomestatement incst USING(idclient)
                   JOIN (SELECT c.idincome ID, sum(inst.total) AS s  
                         FROM instalment inst JOIN credit c  USING(idcredit)
                         WHERE inst.paydate > c.paydate AND c.isloaned = 1
                         GROUP BY c.idincome) sub1 ON incst.idincome = sub1.ID
   GROUP BY idclient, cl.surname;

Second:

SELECT  c.idclient, c.surname, sum(sub.s)
FROM client c, incomestatement inc,
      (SELECT sum(inst.total) as s, cr.idincome as id
       FROM instalment inst, credit cr
       WHERE inst.paydate > cr.paydate AND cr.isloaned = 1 AND cr.idcredit = inst.idcredit
       GROUP BY cr.idincome
       ) sub
WHERE c.idclient = inc.idclient AND inc.income = sub.ID
group by c.idclient, c.surname;

So why they don't give the same result?

I'd approach the problem in steps.

1. Do the two sub-queries produce the same data sets?
   • If they do, proceed to step 2.
   • If not, then you have two simpler queries to analyze and dissect.
2. Given that the pair of sub-queries produce the same answer, you can then establish whether the Client and IncomeStatement joins give the same results (treat it as another sub-query)
   • If they do, proceed to step 3.
   • If not, then you have a pair of queries (one with JOIN, one with classic SQL notation) to analyze and dissect.
3. Given that the pair of joins and the pair of subqueries each produce the same result, analyze why the join of these does not work correctly.

Have you made a commit? It's possible that you don´t commited some transactions, so the results can be differents.