SQL JOIN between A, B and C mixing full and left join

Question

I'd like to know if there is a better way to create my left join in the below example:

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)

That kind of jointure takes long time and I suspect it to be highly expensive and improvable

EDIT: I'd like to improve this SELECT FROM JOIN statement in a View.

Answer 1

You could split the query into two: gather all data matching FIRSTTABLE. And then union it with all data matching SECONDTABLE that is not in FIRSTTABLE.

That should allow SQL Server to use the indexes on these tables better.

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)