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
1条答案
按热度按时间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:Though I don't see why you feel a
LEFT JOIN
would be more performant than aNOT EXISTS
; I doubt it will be.