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?
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?