In following query I'm use both Common Table Expression and Outer queries. Apparently both looks same to me.If I summarize my requirement, I have employees in SalaryTrans table and I want to find out amounts to be recovered from their salaries monthly. Table Rcovery contains all recovery data.  Table Instalment contains all previously paid amounts so I want to sum all paid amounts and then it is deducted from total amount to calculate balance amount.So what I'm doing here is in CTE, I'm getting two numeric fields called recovered_amount and total_amount. In second query I'm checking whether recovered_amount is not null and if it's not null I'm calculating the balance.In outer query I have a criteria to filter out less than 0 balances from the result.WITH CTE (employee_id, RID, instalment, reference, recovered_amount, total_amount) AS           (SELECT SalaryTrans.employee_id, Recovery.RID, Recovery.amount / Recovery.duration AS instalment,           SalaryTrans.reference, SUM(Instalment.recovered_amount) AS recovered_amount,       Recovery.amount AS total_amount          FROM Recovery INNER JOIN                        SalaryTrans ON Recovery.emp_id = SalaryTrans.employee_id LEFT OUTER JOIN                        Instalment ON Recovery.RID = Instalment.recovery_id          GROUP BY SalaryTrans.employee_id, SalaryTrans.reference, Recovery.RID, Recovery.amount / Recovery.duration, Recovery.amount)SELECT RID, Balance, reference  FROM       (SELECT RID,        Balance = CASE WHEN recovered_amount !=NULL                    THEN total_amount-recovered_amount                    ELSE total_amount END ,        reference FROM CTE) AS Result WHERE Balance >0This query gives intended output. But I want to know Can I do whole thing using CTE or simply should I use outer queries in this case. Also any idea to make it look better?Could you please review this and give your feedback?
Using Common Table Expression and Outer queries
sql;sql server;null
Common table expressionsIn general, common table expressions are an excellent way to build complex queries.  For databases that support CTEs, I fully recommend using them in preference to correlated subqueries, since chaining is much more readable than nesting.  I would suggest that you rewrite the nested Result subquery as a CTE as well, if there weren't a completely superior approach (see Rewrite below).If you're going to use CTEs, name them properly.  CTE is just about the least informative name possible for a CTE.SchemaYou have some inconsistencies in the schema.  For example, Recovery has an emp_id column, but SalaryTrans has an employee_id column.  You should also name the columns with consistent capitalization.NitpicksLooking inside your CTE, I see a few minor problems.  I won't go into too much detail, since the whole query should be rewritten anyway.I'm going to make a guess that the Recovery table has RID as its primary key, and that the SalaryTrans table has reference as its primary key.  (It would be nice to include such information about the schema when asking questions  it makes reviews easier to write.)  Since Recovery.amount / Recovery.duration AS instalment is never used in the main query, there is no point to computing it in the CTE.  Also, it would be redundant to GROUP BY Recovery.amount / Recovery.duration, since there would never be two different values of Recovery.amount / Recovery.duration for any given RID.  (The only effect it has would be to trigger a division-by-zero error if duration is zero, and I doubt that that was intentional.)Your CTE should mention the columns, tables, and GROUP BY columns in a logical and consistent order.Part of the reason you have so many GROUP BY columns is that you are joining SalaryTrans prematurely.RewriteThe LEFT OUTER JOIN is problematic.  Assuming that the Instalment table is sanely designed such that the recovered_amount column is non-nullable, the LEFT OUTER JOIN introduces a needless null-handling complication.  Rather than joining the Recovery and Instalment tables side-by-side, you want to combine them vertically.WITH RecoveryEntries AS (    SELECT RID, emp_id, amount        FROM Recovery    UNION ALL    SELECT Recovery.RID, Recovery.emp_id, -Instalment.recovered_amount        FROM Recovery            INNER JOIN Instalment                ON Recovery.RID = Instalment.recovery_id), NetRecovery AS (    SELECT RID, emp_id, SUM(amount) AS Balance        FROM RecoveryEntries        GROUP BY RID, emp_id)SELECT NetRecovery.RID, NetRecovery.Balance, SalaryTrans.reference    FROM NetRecovery        INNER JOIN SalaryTrans            ON NetRecovery.emp_id = SalaryTrans.employee_id    WHERE NetRecovery.Balance > 0