Puzzler

[EdwinGene]

This is not a critical production question, only something I have found that I can't figure out.

I have this query that returns 103,038 records:

Select
a.RecId
From
dbo.TableA a
Left Outer Join dbo.TableB b
on b.TableA_RecId = a.RecId
Where
b.RecId is NULL

If I modify this query by adding a filter in the right join table, the query now returns 103,353 records:

Select
a.RecId
From
dbo.TableA a
Left Outer Join dbo.TableB b
on b.TableA_RecId = a.RecId
and
b.Flag = 'Y'
Where
b.RecId is NULL


Since it's a Left Outer Join, and I'm only selecting those records where the right table returns a NULL record (b.RecId is a non-nullable identity column) shouldn't both queries return the same number of records? But, where clause aside, shouldn't a query with a filter return the same or lesser amount of records? Maybe having the filter in the join causes some interaction that I don't understand.

Is there a guru around who can tell me what is happening, here?

 

[stevexff]

What does

SELECT COUNT(*) FROM tablea
   WHERE recid NOT IN(SELECT TableA_RecId FROM tableb);

return?

 

[mrdenny]

No these should return different results.

In the first query you are pulling all records in A but not in B.

In the second query you are pulling all records in A but not in B, and all records in A but in B, and have B.Flag <> 'Y'

This should give you the same result as the first query.

select a.RecID
from TableA a
where Tablea.RecID not in (select TableB.RecID from TableB)

This should give you the same result as the second query.

select a .recID
from TAbleA a
where Tablea.RecID not in (select TableB.RecID from TableB)
or TableA.RecID in (select TableB.RecID from TableB where Flag <> 'Y')

While my code is less effecient, it should make it a little easier to read.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[r937]

In the second query you are pulling all records in A but not in B, and all records in A but in B, and have B.Flag <> 'Y'

actually, the second query attempts to connect A rows to B rows which have a 'Y', and returns only those rows of A which don't have a matching B with a 'Y'


but those rows of A might have other matching rows of B, ones without a 'Y', and hence the second query could easily return more rows than the first, which returns only As which have no matching B

easy, really

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[EdwinGene]

OK. I can see now that I was so used to using queries to return matched records that I was thinking of this query in the same way. But it isn't the same, its the opposite. This query is returning unmatched records. So if I insert a filter which decreases the number of matched records, the number of unmatched records must, obviously (since the total number of records in TableA remains static), increase.

(embarrassed grin)