将非标准连接转换为标准连接

Question

我试图重做一个查询，以便它使用标准连接（在from子句中）而不是旧版本（迁移到SQL服务器以便强制执行此转换）。我遇到的问题是我下面的查询版本返回的记录比原始记录少得多。所以我认为我的一个外连接不是那么外在。感谢您的帮助，特别是如果您真的通过整个查询。我似乎无法找到任何有关如何进行这种转换的良好文档。

declare 
@startdate datetime, @enddate datetime 

select @startdate = dateadd(dd,-6,convert(datetime,convert(varchar,getdate(),101))) 
select @enddate = dateadd(dd,6,@startdate) 

SELECT PAT.CUSID as ID , 
     substring(CPTREC.CompletedDateTime,1,12) AS DateOfService, 
     SUBSTRING(PCMH.ProcedureCode,CHARINDEX('-',PCMH.ProcedureCode)+ 1,50) AS CPT_Code 
FROM cpt_records as CPTREC 
left Outer Join ProcedureCodeMH as PCMH 
    on CPTREC.ProcedureCodeRevCount = PCMH.ProcedureCodeRevCount 
     and CPTREC.ProcedureCodeSer= PCMH.ProcedureCodeSer 
left outer join Credit as CR 
    on CPTREC.ActInstProcCodeSer = CR.ActInstProcCodeSer 
     and CPTREC.ActInstProcCodeRevCount = CR.ActInstProcCodeRevCount 
inner join ActivityInstance as ACTIN 
    on CPTREC.ActivityInstanceSer= ACTIN.ActivityInstanceSer 
     and CPTREC.ActivityInstanceRevCount = ACTIN.ActivityInstanceRevCount 
inner join ActivityCapture as ACTCAP 
    on ACTCAP.ActivityInstanceSer= ACTIN.ActivityInstanceSer 
     and ACTCAP.ActivityInstanceRevCount = ACTIN.ActivityInstanceRevCount 
inner join ActCaptDiagnosisMH as ACTCAPDMH 
    on ACTCAPDMH.ActivityCaptureSer = ACTCAP.ActivityCaptureSer 
     and ACTCAPDMH.ActivityCaptureRevCount=ACTCAP.ActivityCaptureRevCount 
left outer join Department as DEPT 
    on ACTCAP.DepartmentSer = DEPT.DepartmentSer 
left outer join Hospitality as HOSP 
    on DEPT.HospitalitySer = HOSP.HospitalitySer 
Left outer join Diagnosis as DIAG 
    on ACTCAPDMH.DiagnosisSer = DIAG.DiagnosisSer 
inner join TemplateCycle as TEMPCYC 
    on ACTIN.TemplateCycleSer= TEMPCYC.TemplateCycleSer 
inner join Template as PLATE 
    on TEMPCYC.TemplateSer = PLATE.TemplateSer 
inner join Patent as PAT 
    on PLATE.PatentSer = PAT.PatentSer 
Left Outer Join PatentParticular as PATPAR 
    on PAT.PatentSer = PATPAR.PatentSer 
inner join PatentDoctor as PATDOC 
    on PAT.PatentSer = PATDOC.PatentSer 
inner join Doctor as DOC 
    on PATDOC.ResourceSer = DOC.ResourceSer 
WHERE (CPTREC.CompletedDateTime >= @startdate) 
    and (CPTREC.CompletedDateTime < dateadd(dd,1,@enddate)) 
    and (CPTREC.ObjectStatus = 'Active') 
    and (PATDOC.OncologistFlag = 0) 
    and (PATDOC.PrimaryFlag = 1) 
    and (PCMH.ProcedureCode like '%77781%' 
      or PCMH.ProcedureCode like '%77782%' 
      or PCMH.ProcedureCode like '%77783%' 
      or PCMH.ProcedureCode like '%77784%' 
      or PCMH.ProcedureCode like '%77785%' 
      or PCMH.ProcedureCode like '%77786%' 
      or PCMH.ProcedureCode like '%77787%') 

---

是带回更多的记录的原始是

---

SELECT PAT.CUSID as ID , substring(cpt_records.CompletedDateTime,1,12) AS DateOfService, SUBSTRING(ProcedureCodeMH.ProcedureCode,CHARINDEX('-',ProcedureCodeMH.ProcedureCode)+ 1,50) AS CPT_Code INTO cpt777 FROM cpt_records , ActivityCapture , ActivityInstance , Patent as PAT, ProcedureCodeMH , Template , TemplateCycle , Department , Hospitalityity , Credit , Doctor, PatentDoctor , ActCaptDiagnosisMH , Diagnosis , PatentParticular WHERE (#cpt_records.ProcedureCodeSer *= ProcedureCodeMH.ProcedureCodeSer) and 

(ProcedureCodeMH.ProcedureCodeRevCount =*cpt_records.ProcedureCodeRevCount) and 

(ActivityCapture.DepartmentSer *= Department.DepartmentSer) and 

(Department.HospitalitySer *= Hospitality.HospitalitySer) and 

(ActivityCapture.ActivityCaptureSer *= Credit.ActivityCaptureSer) and 

(ActivityCapture.ActivityCaptureRevCount *= Credit.ActivityCaptureRevCount) and 

(Credit.ActInstProcCodeSer =* cpt_records.ActInstProcCodeSer) and 

(Credit.ActInstProcCodeRevCount =* cpt_records.ActInstProcCodeRevCount) and 

(Patent.PatentSer *= PatentParticular.PatentSer) and 

(cpt_records.ActivityInstanceSer = ActivityInstance.ActivityInstanceSer) and 

(cpt_records.ActivityInstanceRevCount = ActivityInstance.ActivityInstanceRevCount) and 

(ActivityInstance.ActivityInstanceSer = ActivityCapture.ActivityInstanceSer) and 

(ActivityInstance.ActivityInstanceRevCount = ActivityCapture.ActivityInstanceRevCount) and 

(Patent.PatentSer = Template.PatentSer) and 

(Template.TemplateSer = TemplateCycle.TemplateSer) and 

(TemplateCycle.TemplateCycleSer = ActivityInstance.TemplateCycleSer) and 

(Patent.PatentSer = PatentDoctor.PatentSer) and 

(PatentDoctor.ResourceSer = Doctor.ResourceSer) and 

(ActivityCapture.ActivityCaptureSer = ActCaptDiagnosisMH.ActivityCaptureSer) and 

(ActivityCapture.ActivityCaptureRevCount = ActCaptDiagnosisMH.ActivityCaptureRevCount) and (cpt_records.CompletedDateTime >= @startdate) and 

(cpt_records.CompletedDateTime < dateadd(dd,1,@enddate)) and 

(cpt_records.ObjectStatus = 'Active') and 

(PatentDoctor.OncologistFlag = 0) and 

(PatentDoctor.PrimaryFlag = 1) and 

(ActCaptDiagnosisMH.DiagnosisSer *= Diagnosis.DiagnosisSer) and 

(ProcedureCodeMH.ProcedureCode like '%77781%' or ProcedureCodeMH.ProcedureCode like '%77782%' or ProcedureCodeMH.ProcedureCode like '%77783%' or ProcedureCodeMH.ProcedureCode like '%77784%' or ProcedureCodeMH.ProcedureCode like '%77785%' or ProcedureCodeMH.ProcedureCode like '%77786%' or ProcedureCodeMH.ProcedureCode like '%77787%') 

Answer 1

的问题是左连接为在没有找到匹配的行的联接的列值，则返回空值，但您正在检查where子句中的那些列值，但where谓词是在执行后所有行ar e加入，所以他们永远不会匹配，你的外部连接被击沉。

即本例中查询：

select * 
from table1 t1 
left join table2 t2 on t2.fk = t1.id 
where t2.col1 = 'x' 

将永远返回不具备相应table2行任何行，因为col1将null，并与null比较始终false，除了col1 is null。

要解决这个问题，就需要测试移动到ON条款，因此比较发生而join正在取得，像这样：

select * 
from table1 t1 
left join table2 t2 on t2.fk = t1.id and t2.col1 = 'x' 

现在左连接仍然会返回行同时在关键字上进行匹配，并应用额外的谓词来进一步优化匹配。

在你的情况，你正在做外（即left）加入到ProcedureCodeMH as PCMH，但WHERE条款，类似于上面的第一个例子中的测试PCMH列。

你的查询重写将被（见这里的变化是行注释）：

SELECT PAT.CUSID as ID , 
     substring(CPTREC.CompletedDateTime,1,12) AS DateOfService, 
     SUBSTRING(PCMH.ProcedureCode,CHARINDEX('-',PCMH.ProcedureCode)+ 1,50) AS CPT_Code 
FROM cpt_records as CPTREC 
left Outer Join ProcedureCodeMH as PCMH 
    on CPTREC.ProcedureCodeRevCount = PCMH.ProcedureCodeRevCount 
     and CPTREC.ProcedureCodeSer= PCMH.ProcedureCodeSer 
     -- MOVED THIS TEST FROM WHERE CLAUSE TO HERE 
     and (PCMH.ProcedureCode like '%77781%' 
       or PCMH.ProcedureCode like '%77782%' 
       or PCMH.ProcedureCode like '%77783%' 
       or PCMH.ProcedureCode like '%77784%' 
       or PCMH.ProcedureCode like '%77785%' 
       or PCMH.ProcedureCode like '%77786%' 
      or PCMH.ProcedureCode like '%77787%') 
left outer join Credit as CR 
    on CPTREC.ActInstProcCodeSer = CR.ActInstProcCodeSer 
     and CPTREC.ActInstProcCodeRevCount = CR.ActInstProcCodeRevCount 
inner join ActivityInstance as ACTIN 
    on CPTREC.ActivityInstanceSer= ACTIN.ActivityInstanceSer 
     and CPTREC.ActivityInstanceRevCount = ACTIN.ActivityInstanceRevCount 
inner join ActivityCapture as ACTCAP 
    on ACTCAP.ActivityInstanceSer= ACTIN.ActivityInstanceSer 
     and ACTCAP.ActivityInstanceRevCount = ACTIN.ActivityInstanceRevCount 
inner join ActCaptDiagnosisMH as ACTCAPDMH 
    on ACTCAPDMH.ActivityCaptureSer = ACTCAP.ActivityCaptureSer 
     and ACTCAPDMH.ActivityCaptureRevCount=ACTCAP.ActivityCaptureRevCount 
left outer join Department as DEPT 
    on ACTCAP.DepartmentSer = DEPT.DepartmentSer 
left outer join Hospitality as HOSP 
    on DEPT.HospitalitySer = HOSP.HospitalitySer 
Left outer join Diagnosis as DIAG 
    on ACTCAPDMH.DiagnosisSer = DIAG.DiagnosisSer 
inner join TemplateCycle as TEMPCYC 
    on ACTIN.TemplateCycleSer= TEMPCYC.TemplateCycleSer 
inner join Template as PLATE 
    on TEMPCYC.TemplateSer = PLATE.TemplateSer 
inner join Patent as PAT 
    on PLATE.PatentSer = PAT.PatentSer 
Left Outer Join PatentParticular as PATPAR 
    on PAT.PatentSer = PATPAR.PatentSer 
inner join PatentDoctor as PATDOC 
    on PAT.PatentSer = PATDOC.PatentSer 
inner join Doctor as DOC 
    on PATDOC.ResourceSer = DOC.ResourceSer 
WHERE CPTREC.CompletedDateTime >= @startdate 
and CPTREC.CompletedDateTime < dateadd(dd,1,@enddate) 
and CPTREC.ObjectStatus = 'Active' 
and PATDOC.OncologistFlag = 0 
and PATDOC.PrimaryFlag = 1 

注：inner join可以简化为简单join - inner是默认的连接类型。