Derived table where clause behavior

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 2000 Forums

SQL Server Development (2000)

Derived table where clause behavior

Author

Topic

davidreed
Starting Member

7 Posts

Posted - 2006-12-21 : 11:57:17

Can anyone explain the following behavior (SIA for the lengthy post)?

I copy/pasted a portion of a larger query for testing and forgot to remove a table alias prefix from a field ref inside a derived table dec.. I didn't get the results I expected, realized my mistake, fixed it, and all works fine. However, wondering why the results differed in the first place.

Here's the setup (it's a mortgage app, sorry for the length, might be easier to understand in context):

- "loanid" is a Loan
- "delvinstid" is a Delivery Instrument
- each loan is assigned to exactly one delivery instrument
- a delivery instrument may contain multiple loans
- loan-to-delivery instrument relationship is stored in table "alloc"
- alloc also holds "funded" date (date loan is funded)

I want to query alloc for all loans for all delivery instruments that
have at least one un-funded loan. For example:
- delvinst 1 has 3 loans, all 3 are un-funded. Returns all 3 rows.
- delvinst 2 has 3 loans, 1 is un-funded. Returns all 3 rows.
- delvinst 3 has 3 loans, 2 are un-funded. Returns all 3 rows.
I expect all 9 rows to return, hope that makes sense.

>>
CREATE TABLE dbo.ALLOC (
LOANID int NULL
, DELVINSTID int NULL
, FUNDED datetime NULL
)
GO

INSERT ALLOC values (1, 1, NULL);
INSERT ALLOC values (2, 1, NULL);
INSERT ALLOC values (3, 1, NULL);
INSERT ALLOC values (4, 2, '12/21/2006');
INSERT ALLOC values (5, 2, '12/21/2006');
INSERT ALLOC values (6, 2, NULL);
INSERT ALLOC values (7, 3, NULL);
INSERT ALLOC values (8, 3, '12/21/2006');
INSERT ALLOC values (9, 3, NULL);
<<

Here's the query.. note that my mistake was that I copy/pasted from a larger query and forgot to remove the reference to the table alias "a" from the last line:

-- ** Note the "a" ref in the last line
>>
select * from alloc a
where delvinstid in
(select delvinstid from alloc
where a.funded is null)
<<
-- Returns 6 rows

-- ** Removed the "a" reference from the last line...
>>
select * from alloc a
where delvinstid in
(select distinct delvinstid from alloc
where funded is null)
<<
-- Returns 9 rows, which is what I expected

I understand how a table alias can be used inside of a derived table, and I understand that in both queries the "a" is not necessary or even particularly useful. However, in this case it seems like the outer query is being modified by the WHERE clause of the inner derived table query. Am I missing something??

David Reed
David Reed Consulting
White Plains, Maryland

peterlemonjello
Yak Posting Veteran

53 Posts

Posted - 2006-12-21 : 14:01:09

Off topic but you can remove the 'distinct' too.

davidreed
Starting Member

7 Posts

Posted - 2006-12-21 : 15:52:50

quote:
Originally posted by peterlemonjello

Off topic but you can remove the 'distinct' too.

Thanks, yes, I had written it originally without "distinct" but added it thinking it might change the result.. it didn't!

David Reed
David Reed Consulting
White Plains, Maryland

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-21 : 16:45:00

A couple of points:
1. It isn't a derived table. It's a subquery.
2. What is happening is that is is filtering out rows where alloc.funded is null

CODO ERGO SUM

davidreed
Starting Member

7 Posts

Posted - 2006-12-21 : 17:05:33

>> 1. It isn't a derived table. It's a subquery.

Thanks, yes, my bad..

>> 2. What is happening is that is is filtering out rows where
>> alloc.funded is null

Do you mean where alloc.funded is NOT null? Thanks for your help, by the way.. here's what I don't understand:

The subquery should return the set of delvinstid's for all rows with a null funded date; there is at least one row for each of the delvinstid's in my example, so the subquery result should consist of all possible delvinstid's.

The main query is modified only by the list of delvinstid's from the subquery, so it should return all 9 rows.

In the case of my mistake, where the "a" alias is added to the WHERE clause of the subquery, only 6 rows are returned.

If we consider that the alias "a" reference in the subquery points to the alloc table in the main query's FROM clause, in other words, "return all delvinstid's from rows in the alloc table where funded is null" - is acting on the same field as it would in the subquery, because the subquery uses the same table.

Since the "a" points to the ALLOC table, and the subquery also uses the ALLOC table, it just seems like the result should be the same regardless of whether the "a" reference is mistakenly included in the subquery or not..

David Reed
David Reed Consulting
White Plains, Maryland

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-21 : 17:26:04

No. By including the alias of the outer table, you turned it into a correlated subquery, so it will only return rows where ALLOC.FUNDED is null.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources