如何高效连接两表：主表ID匹配从表3个可选ID列

架构老林

2026-7-27

问题描述

尝试通过字符串ID连接两张表，但第二张表有3个可选ID列用于匹配。用OR条件连接时查询速度极慢，创建包含3份表数据的视图又会产生冗余，希望找到更优方案，连接时仅保留第二张表字段的一份副本。

表结构与期望输出

Table1

ID	DocumentID	Value
1	Doc123	54
2	Doxx24	85
3	Doxx24	111
4	Docodoc1	90

Table2

ID2	Document1ID	Document2ID	Document3ID	Value
1	Doc123	null	null	54
2	null	Doxx24	null	85
3	null	Doxx24	null	111
4	null	null	Docodoc1	90

期望输出

ID	DocumentID	Value	ID2	Document1ID	Document2ID	Document3ID	Value
1	Doc123	54	1	Doc123	null	null	54
2	Doxx24	85	2	null	Doxx24	null	85
3	Doxx24	111	3	null	Doxx24	null	111
4	Docodoc1	90	4	null	null	Docodoc1	90

Table1.DocumentID会匹配Table2中三个DocumentID列之一，需要获取匹配的Table2全量数据。

当前查询语句

SELECT ledger.ACCOUNTINGDATE, ledger.DOCUMENTNUMBER, ledger.JOURNALNUMBER, ledger.SUBLEDGERVOUCHER, ledger.ACCOUNTINGCURRENCYAMOUNT, ledger.GENERALJOURNALENTRY, ledger.MAINACCOUNTVALUE, 
              ledger.TRANSACTIONCURRENCYCODE, ledger.TRANSACTIONCURRENCYAMOUNT, ledger.Typ_Ksiegowania, ledger.Typ_Transakcji, ledger.JOURNALCATEGORY, ledger.LEDGERACCOUNT, ledger.REPORTINGCURRENCYAMOUNT, 
              ledger.RECID AS ledgerREcid, ledger.CREATEDTRANSACTIONID, ledger.MAINACCOUNTNAME, ledger.PARTITION, trans.ITEMID, trans.VOUCHERPHYSICAL, trans.VOUCHER, trans.RECID, trans.REFERENCEID, trans.SettelemntVoucher, 
              trans.COSTAMOUNTPHYSICAL, trans.COSTAMOUNTPOSTED, trans.PACKINGSLIPID, trans.QTY, trans.INVENTTRANSID, trans.CURRENCYCODE, trans.INVOICEID, row_number() over (order by ledger.partition) as MyKey
FROM     dbo.TestGeneralTransYear AS ledger LEFT OUTER JOIN
              dbo.SRM_InventTransSettlementTab AS trans ON (ledger.SUBLEDGERVOUCHER = trans.VOUCHER COLLATE Polish_CI_AS or ledger.SUBLEDGERVOUCHER = trans.voucherphysical  COLLATE Polish_CI_AS or ledger.SUBLEDGERVOUCHER = trans.SettelemntVoucher COLLATE Polish_CI_AS)

优化方案

方法1：拆分连接逻辑用UNION ALL合并

把OR条件拆成三个独立的LEFT JOIN，再用UNION ALL合并结果，同时通过NOT EXISTS避免重复匹配。这种方式能让数据库单独利用每个ID列的索引，大幅提升查询效率：

SELECT ledger.ACCOUNTINGDATE, ledger.DOCUMENTNUMBER, ledger.JOURNALNUMBER, ledger.SUBLEDGERVOUCHER, 
       ledger.ACCOUNTINGCURRENCYAMOUNT, ledger.GENERALJOURNALENTRY, ledger.MAINACCOUNTVALUE, 
       ledger.TRANSACTIONCURRENCYCODE, ledger.TRANSACTIONCURRENCYAMOUNT, ledger.Typ_Ksiegowania, 
       ledger.Typ_Transakcji, ledger.JOURNALCATEGORY, ledger.LEDGERACCOUNT, ledger.REPORTINGCURRENCYAMOUNT, 
       ledger.RECID AS ledgerREcid, ledger.CREATEDTRANSACTIONID, ledger.MAINACCOUNTNAME, ledger.PARTITION, 
       trans.ITEMID, trans.VOUCHERPHYSICAL, trans.VOUCHER, trans.RECID, trans.REFERENCEID, trans.SettelemntVoucher, 
       trans.COSTAMOUNTPHYSICAL, trans.COSTAMOUNTPOSTED, trans.PACKINGSLIPID, trans.QTY, trans.INVENTTRANSID, 
       trans.CURRENCYCODE, trans.INVOICEID, row_number() over (order by ledger.partition) as MyKey
FROM dbo.TestGeneralTransYear AS ledger
LEFT JOIN dbo.SRM_InventTransSettlementTab AS trans 
  ON ledger.SUBLEDGERVOUCHER = trans.VOUCHER COLLATE Polish_CI_AS
WHERE trans.RECID IS NOT NULL

UNION ALL

SELECT ledger.ACCOUNTINGDATE, ledger.DOCUMENTNUMBER, ledger.JOURNALNUMBER, ledger.SUBLEDGERVOUCHER, 
       ledger.ACCOUNTINGCURRENCYAMOUNT, ledger.GENERALJOURNALENTRY, ledger.MAINACCOUNTVALUE, 
       ledger.TRANSACTIONCURRENCYCODE, ledger.TRANSACTIONCURRENCYAMOUNT, ledger.Typ_Ksiegowania, 
       ledger.Typ_Transakcji, ledger.JOURNALCATEGORY, ledger.LEDGERACCOUNT, ledger.REPORTINGCURRENCYAMOUNT, 
       ledger.RECID AS ledgerREcid, ledger.CREATEDTRANSACTIONID, ledger.MAINACCOUNTNAME, ledger.PARTITION, 
       trans.ITEMID, trans.VOUCHERPHYSICAL, trans.VOUCHER, trans.RECID, trans.REFERENCEID, trans.SettelemntVoucher, 
       trans.COSTAMOUNTPHYSICAL, trans.COSTAMOUNTPOSTED, trans.PACKINGSLIPID, trans.QTY, trans.INVENTTRANSID, 
       trans.CURRENCYCODE, trans.INVOICEID, row_number() over (order by ledger.partition) as MyKey
FROM dbo.TestGeneralTransYear AS ledger
LEFT JOIN dbo.SRM_InventTransSettlementTab AS trans 
  ON ledger.SUBLEDGERVOUCHER = trans.voucherphysical COLLATE Polish_CI_AS
WHERE trans.RECID IS NOT NULL
AND NOT EXISTS (
    SELECT 1 FROM dbo.SRM_InventTransSettlementTab t
    WHERE t.RECID = trans.RECID
    AND ledger.SUBLEDGERVOUCHER = t.VOUCHER COLLATE Polish_CI_AS
)

UNION ALL

SELECT ledger.ACCOUNTINGDATE, ledger.DOCUMENTNUMBER, ledger.JOURNALNUMBER, ledger.SUBLEDGERVOUCHER, 
       ledger.ACCOUNTINGCURRENCYAMOUNT, ledger.GENERALJOURNALENTRY, ledger.MAINACCOUNTVALUE, 
       ledger.TRANSACTIONCURRENCYCODE, ledger.TRANSACTIONCURRENCYAMOUNT, ledger.Typ_Ksiegowania, 
       ledger.Typ_Transakcji, ledger.JOURNALCATEGORY, ledger.LEDGERACCOUNT, ledger.REPORTINGCURRENCYAMOUNT, 
       ledger.RECID AS ledgerREcid, ledger.CREATEDTRANSACTIONID, ledger.MAINACCOUNTNAME, ledger.PARTITION, 
       trans.ITEMID, trans.VOUCHERPHYSICAL, trans.VOUCHER, trans.RECID, trans.REFERENCEID, trans.SettelemntVoucher, 
       trans.COSTAMOUNTPHYSICAL, trans.COSTAMOUNTPOSTED, trans.PACKINGSLIPID, trans.QTY, trans.INVENTTRANSID, 
       trans.CURRENCYCODE, trans.INVOICEID, row_number() over (order by ledger.partition) as MyKey
FROM dbo.TestGeneralTransYear AS ledger
LEFT JOIN dbo.SRM_InventTransSettlementTab AS trans 
  ON ledger.SUBLEDGERVOUCHER = trans.SettelemntVoucher COLLATE Polish_CI_AS
WHERE trans.RECID IS NOT NULL
AND NOT EXISTS (
    SELECT 1 FROM dbo.SRM_InventTransSettlementTab t
    WHERE t.RECID = trans.RECID
    AND (ledger.SUBLEDGERVOUCHER = t.VOUCHER COLLATE Polish_CI_AS 
         OR ledger.SUBLEDGERVOUCHER = t.voucherphysical COLLATE Polish_CI_AS)
)

UNION ALL

-- 处理没有匹配到任何trans记录的ledger行
SELECT ledger.ACCOUNTINGDATE, ledger.DOCUMENTNUMBER, ledger.JOURNALNUMBER, ledger.SUBLEDGERVOUCHER, 
       ledger.ACCOUNTINGCURRENCYAMOUNT, ledger.GENERALJOURNALENTRY, ledger.MAINACCOUNTVALUE, 
       ledger.TRANSACTIONCURRENCYCODE, ledger.TRANSACTIONCURRENCYAMOUNT, ledger.Typ_Ksiegowania, 
       ledger.Typ_Transakcji, ledger.JOURNALCATEGORY, ledger.LEDGERACCOUNT, ledger.REPORTINGCURRENCYAMOUNT, 
       ledger.RECID AS ledgerREcid, ledger.CREATEDTRANSACTIONID, ledger.MAINACCOUNTNAME, ledger.PARTITION, 
       NULL AS ITEMID, NULL AS VOUCHERPHYSICAL, NULL AS VOUCHER, NULL AS RECID, 
       NULL AS REFERENCEID, NULL AS SettelemntVoucher, NULL AS COSTAMOUNTPHYSICAL, 
       NULL AS COSTAMOUNTPOSTED, NULL AS PACKINGSLIPID, NULL AS QTY, NULL AS INVENTTRANSID, 
       NULL AS CURRENCYCODE, NULL AS INVOICEID, row_number() over (order by ledger.partition) as MyKey
FROM dbo.TestGeneralTransYear AS ledger
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.SRM_InventTransSettlementTab trans
    WHERE ledger.SUBLEDGERVOUCHER = trans.VOUCHER COLLATE Polish_CI_AS
       OR ledger.SUBLEDGERVOUCHER = trans.voucherphysical COLLATE Polish_CI_AS
       OR ledger.SUBLEDGERVOUCHER = trans.SettelemntVoucher COLLATE Polish_CI_AS
)

方法2：用CROSS APPLY精准匹配

利用CROSS APPLY只返回符合条件的第一条记录（如果有多个匹配，可通过ORDER BY指定优先级），避免OR条件导致的全表扫描：

SELECT ledger.ACCOUNTINGDATE, ledger.DOCUMENTNUMBER, ledger.JOURNALNUMBER, ledger.SUBLEDGERVOUCHER, 
       ledger.ACCOUNTINGCURRENCYAMOUNT, ledger.GENERALJOURNALENTRY, ledger.MAINACCOUNTVALUE, 
       ledger.TRANSACTIONCURRENCYCODE, ledger.TRANSACTIONCURRENCYAMOUNT, ledger.Typ_Ksiegowania, 
       ledger.Typ_Transakcji, ledger.JOURNALCATEGORY, ledger.LEDGERACCOUNT, ledger.REPORTINGCURRENCYAMOUNT, 
       ledger.RECID AS ledgerREcid, ledger.CREATEDTRANSACTIONID, ledger.MAINACCOUNTNAME, ledger.PARTITION, 
       trans.ITEMID, trans.VOUCHERPHYSICAL, trans.VOUCHER, trans.RECID, trans.REFERENCEID, trans.SettelemntVoucher, 
       trans.COSTAMOUNTPHYSICAL, trans.COSTAMOUNTPOSTED, trans.PACKINGSLIPID, trans.QTY, trans.INVENTTRANSID, 
       trans.CURRENCYCODE, trans.INVOICEID, row_number() over (order by ledger.partition) as MyKey
FROM dbo.TestGeneralTransYear AS ledger
CROSS APPLY (
    SELECT TOP 1 *
    FROM dbo.SRM_InventTransSettlementTab
    WHERE ledger.SUBLEDGERVOUCHER = VOUCHER COLLATE Polish_CI_AS
       OR ledger.SUBLEDGERVOUCHER = voucherphysical COLLATE Polish_CI_AS
       OR ledger.SUBLEDGERVOUCHER = SettelemntVoucher COLLATE Polish_CI_AS
    -- 可添加ORDER BY指定匹配优先级，比如优先匹配VOUCHER列
    -- ORDER BY CASE WHEN VOUCHER IS NOT NULL THEN 1 WHEN voucherphysical IS NOT NULL THEN 2 ELSE 3 END
) AS trans

索引优化建议

不管用哪种方法，给Table2的三个ID列创建索引是提速的关键：

给每个ID列创建非聚集覆盖索引，包含查询需要的所有trans表字段：

CREATE NONCLUSTERED INDEX IX_SRM_InventTransSettlementTab_Voucher 
ON dbo.SRM_InventTransSettlementTab(VOUCHER COLLATE Polish_CI_AS)
INCLUDE (ITEMID, VOUCHERPHYSICAL, RECID, REFERENCEID, SettelemntVoucher, COSTAMOUNTPHYSICAL, COSTAMOUNTPOSTED, PACKINGSLIPID, QTY, INVENTTRANSID, CURRENCYCODE, INVOICEID);

同理给voucherphysical和SettelemntVoucher列创建类似索引；
注意排序规则一致性：如果查询用了Polish_CI_AS，索引也要指定相同的排序规则，或者确保列本身的排序规则就是Polish_CI_AS，否则索引会失效。

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

如何高效连接两表：主表ID匹配从表3个可选ID列

表结构与期望输出 #

Table1 #

Table2 #

期望输出 #

当前查询语句 #

方法1：拆分连接逻辑用UNION ALL合并 #

方法2：用CROSS APPLY精准匹配 #

索引优化建议 #