In vs Outer Join

Question

In vs Outer Join

farax_x

Hall of Fame

Points: 3579
More actions
November 9, 2015 at 12:31 am

#399296

I want to compare two tables via liked server (number of records which exists only in dbo.Token)
SELECT COUNT(1)
FROM dbo.Token TD WITH ( NOLOCK )
WHERE TD.ID NOT IN (
SELECT TM.Id
FROM [192.168.1.2].Tag.mig.Token TM WITH ( NOLOCK ) )
the result is 0
but this query's result is something else :
SELECT COUNT(1)
FROM dbo.Token TD
LEFT OUTER JOIN ( SELECT Id
FROM [192.168.1.2].Tag.mig.Token TM
WITH ( NOLOCK )
) AS vw ON TD.ID = vw.Id
WHERE vw.Id IS NULL
whats the problem ???

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

farax_x (11/9/2015)
I want to compare two tables via liked server (number of records which exists only in dbo.Token)
SELECT COUNT(1)
FROM dbo.Token TD WITH ( NOLOCK )
WHERE TD.ID NOT IN (
SELECT TM.Id
FROM [192.168.1.2].Tag.mig.Token TM WITH ( NOLOCK ) )
the result is 0
but this query's result is something else :
SELECT COUNT(1)
FROM dbo.Token TD
LEFT OUTER JOIN ( SELECT Id
FROM [192.168.1.2].Tag.mig.Token TM
WITH ( NOLOCK )
) AS vw ON TD.ID = vw.Id
WHERE vw.Id IS NULL
whats the problem ???

Probably this[/url]. Confirm by filtering out nulls from the remote query.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101200 More actions · Answer 2

Yep. NULLs can invalidate NOT IN logic. Add a check to eliminate NULLs from the subquery:

SELECT COUNT(1)

FROM dbo.Token TD WITH ( NOLOCK )

WHERE TD.ID NOT IN (

SELECT TM.Id

FROM [192.168.1.2].Tag.mig.Token TM WITH ( NOLOCK )

WHERE TM.Id IS NOT NULL )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Sergiy SSC Guru Points: 110209 More actions · Answer 3

Both options suck.

WHERE (NOT) EXISTS

does the job way more effectively.

And NULL handling is much more clear in this case.

_____________
Code for TallyGenerator