关联两个无关表查询存在未结清余额的续期记录

方法1：将未结清余额记录作为筛选条件（推荐） #

你的核心需求是同时满足「续期记录且父记录已过期」和「存在未结清余额」，不需要用UNION（它是取两个结果的并集），而是把第二个查询的结果作为第一个查询的过滤条件，用IN、EXISTS或JOIN实现，这更高效且贴合需求。

用IN子查询 #

Select BL1.RECORDID as RECIID, BL2.EXPIRATIONDATE as ParentRecordExpirationDate, 
        (DATEDIFF(DD,GETDATE(),BL2.ExpirationDate)*-1)as DAYSOVER 
from BLLICENSE BL1 
JOIN RECORD BL2 on BL1.RECORDPARENTID = BL2.RECORDID 
Where CONVERT(date,BL2.EXPIRATIONDATE) < GETDATE() 
    AND BL1.RECORDSTATUSID IN('Renewed', 'Issued', 'In Review', 'On Hold', 'Submitted', 'Fees Due')
    AND BL1.RECORDID IN (
        Select DISTINCT BLL.RECORDID 
        from CAINVOICE CAI 
        JOIN CAINVOICEFEE CAIF on CAIF.CAINVOICEID = CAI.CAINVOICEID
        JOIN CACOMPUTEDFEE CACF on CACF.CACOMPUTEDFEEID = CAIF.CACOMPUTEDFEEID
        JOIN RECORD BLLF on BLLF.CACOMPUTEDFEEID = CACF.CACOMPUTEDFEEID
        JOIN RECORD BLL on BLL.RECORDID = BLLF.RECORDID
        WHERE CAI.CASTATUSID in (1,2,3,6,7,8)
    )

用EXISTS子查询（大表场景性能更优） #

Select BL1.RECORDID as RECIID, BL2.EXPIRATIONDATE as ParentRecordExpirationDate, 
        (DATEDIFF(DD,GETDATE(),BL2.ExpirationDate)*-1)as DAYSOVER 
from BLLICENSE BL1 
JOIN RECORD BL2 on BL1.RECORDPARENTID = BL2.RECORDID 
Where CONVERT(date,BL2.EXPIRATIONDATE) < GETDATE() 
    AND BL1.RECORDSTATUSID IN('Renewed', 'Issued', 'In Review', 'On Hold', 'Submitted', 'Fees Due')
    AND EXISTS (
        SELECT 1
        from CAINVOICE CAI 
        JOIN CAINVOICEFEE CAIF on CAIF.CAINVOICEID = CAI.CAINVOICEID
        JOIN CACOMPUTEDFEE CACF on CACF.CACOMPUTEDFEEID = CAIF.CACOMPUTEDFEEID
        JOIN RECORD BLLF on BLLF.CACOMPUTEDFEEID = CACF.CACOMPUTEDFEEID
        WHERE BLLF.RECORDID = BL1.RECORDID
          AND CAI.CASTATUSID in (1,2,3,6,7,8)
    )

这里不需要DISTINCT，因为EXISTS只要找到匹配记录就返回，重复数据不影响结果。

用JOIN关联 #

Select DISTINCT BL1.RECORDID as RECIID, BL2.EXPIRATIONDATE as ParentRecordExpirationDate, 
        (DATEDIFF(DD,GETDATE(),BL2.ExpirationDate)*-1)as DAYSOVER 
from BLLICENSE BL1 
JOIN RECORD BL2 on BL1.RECORDPARENTID = BL2.RECORDID 
JOIN (
    Select DISTINCT BLL.RECORDID 
    from CAINVOICE CAI 
    JOIN CAINVOICEFEE CAIF on CAIF.CAINVOICEID = CAI.CAINVOICEID
    JOIN CACOMPUTEDFEE CACF on CACF.CACOMPUTEDFEEID = CAIF.CACOMPUTEDFEEID
    JOIN RECORD BLLF on BLLF.CACOMPUTEDFEEID = CACF.CACOMPUTEDFEEID
    JOIN RECORD BLL on BLL.RECORDID = BLLF.RECORDID
    WHERE CAI.CASTATUSID in (1,2,3,6,7,8)
) AS UnpaidRecords ON UnpaidRecords.RECORDID = BL1.RECORDID
Where CONVERT(date,BL2.EXPIRATIONDATE) < GETDATE() 
    AND BL1.RECORDSTATUSID IN('Renewed', 'Issued', 'In Review', 'On Hold', 'Submitted', 'Fees Due')

外层加DISTINCT是避免JOIN产生重复行，子查询的DISTINCT可以减少关联行数，提升性能。

方法2：调整UNION的列数（仅当需要取两个结果并集时使用） #

如果你的需求是要么是续期记录（父记录过期），要么是有未结清余额的记录，可以给第二个查询补对应列的默认值（比如NULL），让两个查询的列数、数据类型一致：

-- 第一个查询：续期记录且父记录过期
Select BL1.RECORDID as RECIID, BL2.EXPIRATIONDATE as ParentRecordExpirationDate, 
        (DATEDIFF(DD,GETDATE(),BL2.ExpirationDate)*-1)as DAYSOVER 
from BLLICENSE BL1 
JOIN RECORD BL2 on BL1.RECORDPARENTID = BL2.RECORDID 
Where CONVERT(date,BL2.EXPIRATIONDATE) < GETDATE() 
    AND BL1.RECORDSTATUSID IN('Renewed', 'Issued', 'In Review', 'On Hold', 'Submitted', 'Fees Due')

UNION

-- 第二个查询：有未结清余额的记录，补NULL对应列
Select DISTINCT BLL.RECORDID as RECIID, NULL as ParentRecordExpirationDate, NULL as DAYSOVER
from CAINVOICE CAI 
JOIN CAINVOICEFEE CAIF on CAIF.CAINVOICEID = CAI.CAINVOICEID
JOIN CACOMPUTEDFEE CACF on CACF.CACOMPUTEDFEEID = CAIF.CACOMPUTEDFEEID
JOIN RECORD BLLF on BLLF.CACOMPUTEDFEEID = CACF.CACOMPUTEDFEEID
JOIN RECORD BLL on BLL.RECORDID = BLLF.RECORDID
WHERE CAI.CASTATUSID in (1,2,3,6,7,8)

注意UNION会自动去重，不需要去重时用UNION ALL性能更好。

内容的提问来源于stack exchange，提问作者SirThicc