Outer reference of Not Exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Outer reference of Not Exists

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2011-10-18 : 19:44:18

The count for this


SELECT DISTINCT a.ID
FROM Table1 a INNER JOIN Table2 b ON a.ID = b.ID
WHERE b.Type = 'X'
AND b.Code NOT IN ('A','B','C')

matches the count for this


SELECT DISTINCT a.ID
FROM Table1 a INNER JOIN Table2 b ON a.ID = b.ID
WHERE b.Type = 'X' 
AND NOT EXISTS (
SELECT ID
FROM Table2
WHERE Code IN ('A','B','C')
AND Code = b.Code
)

but not for this, which returns a lower figure


SELECT DISTINCT a.ID
FROM Table1 a INNER JOIN Table2 b ON a.ID = b.ID
WHERE b.Type = 'X' 
AND NOT EXISTS (
SELECT ID
FROM Table2
WHERE Code IN ('A','B','C')
AND ID = b.ID
)

Why does this third option correlate differently than the second? I'm betting there's a "set" answer to this.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-18 : 21:19:38

Why would you expect the third one to be the same? It has a different where clause and is not equivalent to the other ones. Show us some data so we can help better answer this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dmilam
Posting Yak Master

185 Posts

Posted - 2011-10-19 : 00:41:17

I expect an outer correlation on ID restricted by the subquery iteration A,B,C to be equivalent to the NOT IN example here, i.e., "don't give me IDs which are in ('A','B','C')". It's production data that I abstracted.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-19 : 12:33:28

The third is definitely not equivalent. If you want us to help explain it, you will need to show us some sample data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dmilam
Posting Yak Master

185 Posts

Posted - 2011-10-19 : 13:35:10

Fair enough, but I can't reproduce it. The production data counts differ between 104,675 rows and 93,700 rows, whereas this sample data does not.


SET NOCOUNT ON;

DECLARE @Table1 TABLE (
[ID] INT NOT NULL
)

INSERT @Table1 (ID)
SELECT TOP 2000 Number
FROM T.dbo.TallyNumbers -- 1 through 8000

DECLARE @Table2 TABLE (
[ID] INT NOT NULL,
[Type] CHAR(1) NOT NULL,
[Code] CHAR(1) NOT NULL
)

INSERT @Table2 ( ID, Type, Code)
SELECT Number, [Type] = 'X', [Code] = '' 
FROM T.dbo.TallyNumbers
WHERE Number BETWEEN 150 AND 800

UPDATE @Table2
SET [Code] = 'A'
WHERE ID BETWEEN 150 AND 250

UPDATE @Table2
SET [Code] = 'B'
WHERE ID BETWEEN 250 AND 350 

UPDATE @Table2
SET [Code] = 'C'
WHERE ID BETWEEN 350 AND 450

UPDATE @Table2
SET [Code] = 'D'
WHERE ID BETWEEN 450 AND 550

UPDATE @Table2
SET [Code] = 'E'
WHERE ID BETWEEN 550 AND 800

SET NOCOUNT OFF;

SELECT DISTINCT a.ID
FROM @Table1 a INNER JOIN @Table2 b ON a.ID = b.ID
WHERE b.Type = 'X'
AND b.Code NOT IN ('A','B','C')

SELECT DISTINCT a.ID
FROM @Table1 a INNER JOIN @Table2 b ON a.ID = b.ID
WHERE b.Type = 'X' 
AND NOT EXISTS (
SELECT ID
FROM @Table2
WHERE Code IN ('A','B','C')
AND Code = b.Code
)

SELECT DISTINCT a.ID
FROM @Table1 a INNER JOIN @Table2 b ON a.ID = b.ID
WHERE b.Type = 'X' 
AND NOT EXISTS (
SELECT ID
FROM @Table2
WHERE Code IN ('A','B','C')
AND ID = b.ID
)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-19 : 13:40:13

We really can't help unless we have a valid sample. Can't you query prod and determine what rows are making this differ?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dmilam
Posting Yak Master

185 Posts

Posted - 2011-10-19 : 16:33:13

I can the remaining rows, yes, none of which contain A,B,C of course. Thanks, though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources