SQL Server how to re-write left join with multiple or conditions

insrf1ej 于 12个月前发布在其他

关注(0)|答案(2)|浏览(144)

I'm trying to insert data from staging tableA to target tableB after incremental logic. TableA is staging table and has new and updated records compared to target table. Update query is running fine but the insert is taking a lot of time to run, so trying to re-write the logic.

Select
    s.[column1],
    s.column2
FROM
    TableA S with (nolock) 
    LEFT JOIN TableB t with (nolock) ON
        (
            ISNULL(t.column1, -1) = ISNULL(s.column1, -1)
            AND
            ISNULL(t.[column2],-1) = ISNULL(s.[column2],-1)
        )
        OR
        (
            ISNULL(t.[column2],-1) = -1
            AND
            ISNULL(s.[column2],-1) <> -1
            AND
            ISNULL(t.[column1], -1) = ISNULL(s.column1, -1)
        )
        OR
        (
            ISNULL(t.[column1],-1) = -1
            AND
            ISNULL(s.[column1],-1) <> -1
            AND
            ISNULL(t.[column2], -1) = ISNULL(s.[column2], -1)
        ) 
WHERE
    t.column2 IS NULL

The current process is running for 3- 4 hours. I have added indexes for both columns on 2 tables, but it is not helping. How to re-write the logic without using or in join condition.

sql-server

来源：https://stackoverflow.com/questions/77615574/how-to-re-write-left-join-with-multiple-or-conditions

2条答案

按热度按时间

j2qf4p5b1#

you can try using separate LEFT JOIN clauses for each condition and use WHERE clause to filter results.

LEFT JOIN TableB t1 with (nolock)
   ON ISNULL(t1.column1, -1) = ISNULL(s.column1, -1) AND 
   ISNULL(t1.column2, -1) = ISNULL(s.column2, -1)
LEFT JOIN TableB t2 with (nolock)
   ON ISNULL(t2.column1, -1) = ISNULL(s.column1, -1) AND 
   t2.column2 IS NULL
LEFT JOIN TableB t3 with (nolock)
   ON ISNULL(t3.column2, -1) = ISNULL(s.column2, -1) AND 
   t3.column1 IS NULL
WHERE t1.column1 IS NULL AND t2.column1 IS NULL AND t3.column2 IS NULL

Here the WHERE clause checks if all three LEFT JOIN conditions fail.

⚠️keep in mind that the performance is still depends on the size of the tables, and the indexing strategy. Ensure the appropriate indexes are still in place.

赞(0）回复(0）举报 12个月前

hkmswyz62#

You can try rewritting using UNION ALL instead of OR condtion in LEFT JOIN as:

Select
s.[column1],
s.column2
FROM
TableA S 
LEFT JOIN TableB t 
    ON ISNULL(t.column1, -1) = ISNULL(s.column1, -1)
    AND ISNULL(t.[column2],-1) = ISNULL(s.[column2],-1)
WHERE
t.column2 IS NULL 
UNION ALL
Select
s.[column1],
s.column2
FROM
TableA S 
LEFT JOIN TableB t ON
    t.[column2] IS NULL -- ISNULL(t.[column2],-1) = -1
    AND s.[column2] IS NOT NULL -- ISNULL(s.[column2],-1) <> -1
    AND ISNULL(t.[column1], -1) = ISNULL(s.column1, -1)
    
WHERE
t.column2 IS NULL 
UNION ALL
Select
s.[column1],
s.column2
FROM
TableA S 
LEFT JOIN TableB t 
    ON  t.[column1] IS NULL -- ISNULL(t.[column1],-1) = -1
    AND s.[column1] IS NOT NULL -- ISNULL(s.[column1],-1) <> -1
    AND ISNULL(t.[column2], -1) = ISNULL(s.[column2], -1)
  
WHERE
t.column2 IS NULL

赞(0）回复(0）举报 12个月前

我来回答

SQL Server how to re-write left join with multiple or conditions

2条答案

相关问题

热门标签

最新问答