SQL Server Converting NOT EXISTS to LEFT JOINS

lnlaulya  于 2023-08-02  发布在  其他
关注(0)|答案(1)|浏览(106)

Currently I am trying to convert a SELECT statement that uses NOT EXISTS in the WHERE clause just like this:

SELECT 
    T1.TOneID
FROM
    TOne T1
WHERE
    NOT EXISTS (
    SELECT 
        1
    FROM 
    TTwo T2
    LEFT JOIN TThree T3 ON
    T3.TTwoId = T2.TTwoId
    WHERE
    T2.TOneId = T1.TOneId AND 
    (
    T2.Column IS NULL OR
    T3.Column IS NULL
    ) 
)

To another query using LEFT JOINS and a condition with WHERE X IS NULL on the Primary Keys of T2 and T3 instead. Unfortunately I don't get the same result in the new query as with the old query. Where am I going wrong? The reason why I try to do this is to optimize the performance of the query.

I tried the following query:

SELECT 
    T1.TOneID
FROM
    TOne T1
    LEFT JOIN TTwo T2 ON
      T2.TOneId = T1.TOneId AND
      T2.Column IS NULL
    LEFT JOIN TThree T3 ON
      T3.TTwoId = T2.TTwoId AND
      T3.Column IS NULL
WHERE
    T2.TTwoID IS NULL AND
    T3.TThreeID IS NULL

But this query gave me the wrong result compared to the old one. It produced rows that should not have benen there

pvabu6sv

pvabu6sv1#

Perhaps what you need is a derived table and a LEFT JOIN to that. Impossible to test with no sample data and expected results mind:

SELECT T1.TOneID
FROM dbo.TOne T1
     LEFT JOIN (SELECT T2.TOneId
                FROM dbo.TTwo T2
                     LEFT JOIN dbo.TThree T3 ON T3.TTwoId = T2.TTwoId
                WHERE T2.Column IS NULL OR T3.Column IS NULL) T ON T1.TOneId = T.TOneId
WHERE T.TOneId IS NULL;

Though I don't see why you feel a LEFT JOIN would be more performant than a NOT EXISTS ; I doubt it will be.

相关问题