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

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语句。

最佳答案

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

这样可以使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)

10-07 19:10
查看更多