When some conditions of ‘Where’ part are fetched from another sql statement, usually, we can write as below :
select * from Haaaaaaluation
where HaaaaaavaluationId in
(
select HaaaaaavaluationId from HaaaaaaData
-- where ......
)
Of course we can write like this. But it will make statements efficiency low.
Then how can we improve the efficiency?
Yes, we can use [with CTE as] to implement this goal.
With CTE as
(
select HaaaaaavaluationId from HaaaaaaData
-- where ......
)
select * from Haaaaaaluation
where HaaaaaavaluationId in
(
select * from CTE
)
Let us have a try by using my actual experience in my work :
;with CTE AS
(
select
HaaaaaavaluationId
from haaaaaavaluation
where ServicerLoanNumber = '0017201310' and activeRecord = 1
)
select
me.HaaaaaavaluationId
,data.DecisionMatrixWaterfallId
,WaterFallId = case
when data.PrincipalForbearanceAmount>0
or (data.PrincipalWriteDown =0 and data.PrincipalForbearanceAmount=0 )then 11
else 12 end
,convert (numeric(14,5)
,coalesce((hmea.resetinterestrate/100),data.interestratebeforemodification)) as PreModInterestRate
from Haaaaaavaluation me
inner join HaaaaaavaluationStandardDataNPV sdnpv on sdnpv.Haaaaaavaluationid = me.Haaaaaavaluationid
.........
where me.Haaaaaavaluationid in(
select * from CTE
)
Now, is it more clear and more effective?
Let me show you the provement :
We can use those methods to print the run time of our two statements
Print run time
No [with CTE as] :
With [with CTE as] :
Actually, in most easy statements we need not be like above. Maybe the two have no big difference. However, if we have more complex requirement to be completed, we can see the difference.
Also, let me show a true example in my work:
;With CTE as
(
select ModificationTypeId
from modificationdefinition a
join ModificationCalculation c on a.ModificationCalculationid = c.ModificationCalculationid
where a.ModificationTypeId=1
),
ForgivenessLimitId AS --
(
select LOAN_NO,CTE.modificationlimitid,CTE.ShortDescription
from CTE
where ShortDescription !='NOT ALLOWED' --AG Default
AND limitid = 3
),
ForbearanceLimitId AS
(
select LOAN_NO,CTE.modificationlimitid,CTE.ShortDescription
from CTE
where ShortDescription ='NOT ALLOWED' --SPS Default
AND limitid = 6
)
select distinct data.ServicerLoanNumber,.....,data.CreatedBy
FROM Haaaaadata data
INNER JOIN Haaaaaavaluation HME on data.Haaaaaavaluationid =HME.HaaaaaavaluationId
INNER JOIN CTE ON HME.ServicerLoanNumber = CTE.Loan_no
........
WHERE 1=1
AND data.CreatedBy='********'
AND data.ServicerLoanNumber IN
(
select a.lOAN_no from ForgivenessLimitId a,ForbearanceLimitId b
where a.lOAN_no = b.Loan_no
)
Here we need pay attention to :
- the begin with “;” : if has no other sql statements before or in front of [With CTE as] , we can omit the ‘;’. But if it has, we must not forget the ‘;’.
- Case when …else end : It is easy and frequent use in work. We can use it in “select” column which is defined as you want show by yourself.
- convert (numeric(14,5), coalesce(num1,num2,…) : convert the ‘num1’ or ‘num2’ to [numeric(14,5)] Type.
- coalesce((hmea.resetinterestrate/100),data.interestratebeforemodification)) : If [hmea.resetinterestrate/100] is null, then we fetch “data.interestratebeforemodification”. We will use it frequently in work jobs.
The more details we can serach it by Google or Baidu.