Performance comparison of these two queries

Question

I am using this query to get result, where Calander (primary key: DAY_DATE) has all continuous information about time (i.e. date, quarter, year etc.) and EXPENCE and INCOME both have column TXN_DATE as a foreign key to DAY_DATE.

  SELECT
    COALESCE(t1."FISCAL_YEAR",t2."FISCAL_YEAR") "FISCAL_YEAR" ,
    "exp" "exp" ,
    "rev" "rev" 
    FROM
    (SELECT
    Calander.FISCAL_YEAR "FISCAL_YEAR" ,
    (SUM("EXPENCE"."TXN_AMT" )) "exp" 
    FROM
        Calander ,
        EXPENCE 
    WHERE
        "EXPENCE"."TXN_DATE"="Calander"."DAY_DATE" 
    GROUP BY 
        FISCAL_YEAR ) t1 FULL OUTER JOIN (SELECT
        Calander.FISCAL_YEAR "FISCAL_YEAR" ,
        (SUM("INCOME"."TXN_AMT" )) "rev" 
    FROM
        Calander ,
        INCOME 
    WHERE
        "INCOME"."TXN_DATE"="Calander"."DAY_DATE" 
    GROUP BY 
        FISCAL_YEAR ) t2 ON 
        t1."FISCAL_YEAR"=t2."FISCAL_YEAR"  
    ORDER BY
            COALESCE(t1."FISCAL_YEAR",t2."FISCAL_YEAR") 

Now to make query more simple to understand I did this

SELECT
    FISCAL_YEAR "FISCAL_YEAR" ,
    (sum("EXPENCE"."TXN_AMT" )) "exp",
    (sum("INCOME"."TXN_AMT" )) "rev" 
FROM
    Calander ,
    EXPENCE FULL OUTER JOIN INCOME ON
    "EXPENCE"."TXN_DATE" = "INCOME"."TXN_DATE"
WHERE
    "EXPENCE"."TXN_DATE"="Calander"."DAY_DATE" and
    "INCOME"."TXN_DATE"="Calander"."DAY_DATE" 
GROUP BY 
    FISCAL_YEAR
ORDER BY
    FISCAL_YEAR

I am getting same result in both queries. Will the result will be difference in any case (Ist query result is the correct result)? What will be performance impact?

Answer 1

Second one should be more efficient, but to verify it, check actual execution plans. Also set the

SET STATISTICS IO ON

on the session and check the logical reads for both queries on your real data.

Another habit to kick - stop using double quotes around identifiers, or prepare to get hit by QUOTED_IDENTIFIER setting sometime :)