A、B 和 C 之间的 SQL JOIN 混合全连接和左连接

Question

我想知道是否有更好的方法在下面的示例中创建我的左连接:

SELECT TOP 10 COALESCE(A.COD_PRODUCT, B.COD_PRODUCT),
              COALESCE(A.COD_FAMILY, B.COD_FAMILY),
              COALESCE(A.DATE_EXTRACT, B.DATE_EXTRACT),
              A.MASS
              B.VOLUME
              C.PRICE
FROM FIRSTTABLE A FULL JOIN SECONDTABLE B ON B.COD_PRODUCT = A.COD_PRODUCT
                                          AND B.COD_FAMILY = A.COD_FAMILY
                                          AND B.DATE_EXTRACT = A.DATE_EXTRACT
LEFT JOIN THIRDTABLE C ON C.COD_PRODUCT = COALESCE(A.COD_PRODUCT,B.COD_PRODUCT)
                      AND C.COD_FAMILY = COALESCE(A.COD_FAMILY, B.COD_FAMILY)
                      AND C.DATE_EXTRACT = COALESCE(A.DATE_EXTRACT, B.DATE_EXTRACT)

这种接合需要很长时间，而且我怀疑它非常昂贵且无法改进

编辑:我想改进 View 中的此 SELECT FROM JOIN 语句。

Answer 1

您可以将查询分成两部分:收集与 FIRSTTABLE 匹配的所有数据。然后将其与所有匹配 SECONDTABLE 但不在 FIRSTTABLE 中的数据合并。

这应该允许 SQL Server 更好地使用这些表上的索引。

SELECT A.COD_PRODUCT,
       A.COD_FAMILY,
       A.DATE_EXTRACT,
       A.MASS,
       B.VOLUME,
       C.PRICE
FROM FIRSTTABLE A
LEFT OUTER JOIN SECONDTABLE B
     ON B.COD_PRODUCT = A.COD_PRODUCT
     AND B.COD_FAMILY = A.COD_FAMILY
     AND B.DATE_EXTRACT = A.DATE_EXTRACT
LEFT OUTER JOIN THIRDTABLE C
     ON C.COD_PRODUCT = A.COD_PRODUCT
     AND C.COD_FAMILY = A.COD_FAMILY
     AND C.DATE_EXTRACT = A.DATE_EXTRACT
UNION ALL
SELECT B.COD_PRODUCT,
       B.COD_FAMILY,
       B.DATE_EXTRACT,
       NULL AS MASS,
       B.VOLUME,
       C.PRICE
FROM SECONDTABLE B
LEFT OUTER JOIN THIRDTABLE C
     ON C.COD_PRODUCT = B.COD_PRODUCT
     AND C.COD_FAMILY = B.COD_FAMILY
     AND C.DATE_EXTRACT = B.DATE_EXTRACT
WHERE NOT EXISTS (SELECT 1
                  FROM   FIRSTTABLE A
                  WHERE  A.COD_PRODUCT = B.COD_PRODUCT
                  AND    A.COD_FAMILY = B.COD_FAMILY
                  AND    A.DATE_EXTRACT = B.DATE_EXTRACT)