OUTER JOIN table limit?

I came across this statement from ASP.NET forum : "..There is a limit to the level for OUTER JOIN ANSI SQL limit is four after that you may get strange results. ..." . I did a little research but without getting clear answer from the SQL92 standard itself. I am wondering whether I can get help about this in SQL Server 2005 implementation here.

I put this question in another way, How many tables can we use in OUTER JOIN(or INNER JOIN) in SQL Server 2005?

Thanks.

There is no limit in the ANSI SQL standard or SQL Server. In fact, the ANSI SQL standard doesn't talk about such limits anywhere. They provide specifications on the syntax and how it should work etc. Note that a particular database implementation can have limits imposed. SQL Server 2005 for example has a maximum limit of 256 table references in a SELECT statement. So you can end up in some situation where the query optimizer cannot produce a plan due to insufficient resources in the system if the query is too complex and contains large number of table references. See below example for SQL Server 2005 which allows more than 4 outer joins:

with t(i)
as (
select 1
)
select *
from t as t1
left join t as t2
left join t as t3
left join t as t4
left join t as t5
left join t as t6
left join t as t7
left join t as t8
left join t as t9
left join t as t10
on t10.i = t9.i
on t9.i = t8.i
on t8.i = t7.i
on t7.i = t6.i
on t6.i = t5.i
on t5.i = t4.i
on t4.i = t3.i
on t3.i = t2.i
on t2.i = t1.i

|||

as far as sql server is concerned there is no limitation.

it could have been a limitation from data access tier

e.g. dataset

|||

Hello:

I was pointed to this "Three-Way Joins and Beyond" in SQL Pperformance Tuning by Peter Gulutzan and Trudy Pelzer.

Can I get some explaination for this statement: "You can expect the DBMS optimizer to start going wrong if five or more joins are in the query (until recently Microsoft's admitted limit was four). "

Is there anything I miss here?

This book was published on September 10, 2002.

Thank you.

|||

That article makes several incorrect assumptions and looks like the authors are not well informed about SQL Server. For example, the support for recognizing transitive predicates has been in the product since SQL Server 7.0. See link below:

http://www.microsoft.com/technet/prodtechnol/sql/70/reskit/part9/sqc13.mspx?mfr=true

I didn't go through all the chapters but the first page itself has several errors and doesn't apply to SQL Server. Performance of joins involving large number of tables can be a problem depending on the resources and the query plan. This has also been improved considerably in the product for every release starting from SQL 6x. If you are looking for querying tips for SQL Server, you may want to look at some of the new Inside SQL Server 2005 series for example.

|||

Hi Umachandar:

Thank you for answering this question. I used multiple outer joins to extract a dataset from my database. That table limit statement could pose a serious problem to my query if it were true for SQL Server 2000 or 2005.

Outer Join across many-to-many table ?

Given the following data model...
Table a (id int PK)
Table b (id int PK)
Table aXb (a.id int FK, b.id int FK, UNIQUE(a.id, b.id)
Scenario: "b" essentially represents a table of picklist data. I want to join "a" to "b" in such a way that I get all rows in "b"
for each unique row in "a" (typically done with an outer join when "b" has a FK to "a").
I tried this...
SELECT * FROM a
INNER JOIN aXb ON a.id = aXb.a.id
LEFT OUTER JOIN b ON aXb.b.id = b.id
WHERE a.id = 1
...but it isn't giving me what I want.
Can this be done?
Thanks,
ChrisG
See if this is what you want:
SELECT A.id, B.id
FROM A, B
WHERE A.id = 1
David Portas
SQL Server MVP
|||I think the first join should be the outer join. If there are no pick
records for a client (I'm guessing that's what a is), there will be no
record for them in aXb, and no record in the result. Actually, I think
you need the outer join for both joins.
(That seems really odd and/or dangerous to me that you have periods in
the field names a.id, b.id in aXb. I guess those aren't the real names)
Or maybe you could use a subquery with one outer join:
SELECT * FROM a
LEFT OUTER JOIN
(Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
ON a.id = PICK.a.id
WHERE a.id = 1
|||or maybe:
SELECT *
FROM A
CROSS JOIN B
LEFT JOIN AXB
ON A.id = AXB.a_id
AND B.id = AXB.b_id
WHERE A.id = 1
David Portas
SQL Server MVP
|||Wouldn't that result just be a bunch of 1's with all the id's from b
(assuming 1 is in a)? You need aXb to limit the pick records for a.id=1.
|||"Jerry Porter" <jerryp@.personablepc.com> wrote in message news:1110302991.330487.242630@.z14g2000cwz.googlegr oups.com...
|I think the first join should be the outer join. If there are no pick
| records for a client (I'm guessing that's what a is), there will be no
| record for them in aXb, and no record in the result. Actually, I think
| you need the outer join for both joins.
|
| (That seems really odd and/or dangerous to me that you have periods in
| the field names a.id, b.id in aXb. I guess those aren't the real names)
That's pseudo-sql ;-)
| Or maybe you could use a subquery with one outer join:
| SELECT * FROM a
| LEFT OUTER JOIN
| (Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
| ON a.id = PICK.a.id
| WHERE a.id = 1
Sorry, that didn't work.
Thanks, tho.
ChrisG
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegr oups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Both of your suggestions worked as I asked. (I didn't ask the right question, tho). I was hoping to see a null in the "aXb" join so
I knew which rows in "b" linked to the row in "a". All the columns in the "aXb" join are returning NULL
I'll take off my obtuse hat and try to better state what I'm looking for.
"a" = Users
"b" = Roles
"aXb" = UsersXRoles
I'm looking to create a view that shows each user and all the roles they can be assigned to. I was hoping to alias a column of the
UsersXRoles table to indicate assignment, i.e.,
User Roles Assigned
UserA Group1 Yes
UserA Group2 No
UserA Group3 Yes
UserB Group1 No
UserB Group2 No
UserB Group3 Yes
etc.
I'm open to any suggestions. I'd like to stick with the existing data model (described in the op) if possible.
Thanks,
ChrisG
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegr oups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Just wanted to followup and state that this query works exactly as I *need* it to. I just wasn't paying close attention when I was
cutting, pasting and editing from all my trial scripts.
Thanks David P.
ChrisG

Outer Join across many-to-many table ?

Given the following data model...
Table a (id int PK)
Table b (id int PK)
Table aXb (a.id int FK, b.id int FK, UNIQUE(a.id, b.id)
Scenario: "b" essentially represents a table of picklist data. I want to join "a" to "b" in such a way that I get all rows in "b"
for each unique row in "a" (typically done with an outer join when "b" has a FK to "a").
I tried this...
SELECT * FROM a
INNER JOIN aXb ON a.id = aXb.a.id
LEFT OUTER JOIN b ON aXb.b.id = b.id
WHERE a.id = 1
...but it isn't giving me what I want.
Can this be done?
Thanks,
ChrisGSee if this is what you want:
SELECT A.id, B.id
FROM A, B
WHERE A.id = 1
--
David Portas
SQL Server MVP
--|||I think the first join should be the outer join. If there are no pick
records for a client (I'm guessing that's what a is), there will be no
record for them in aXb, and no record in the result. Actually, I think
you need the outer join for both joins.
(That seems really odd and/or dangerous to me that you have periods in
the field names a.id, b.id in aXb. I guess those aren't the real names)
Or maybe you could use a subquery with one outer join:
SELECT * FROM a
LEFT OUTER JOIN
(Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
ON a.id = PICK.a.id
WHERE a.id = 1|||or maybe:
SELECT *
FROM A
CROSS JOIN B
LEFT JOIN AXB
ON A.id = AXB.a_id
AND B.id = AXB.b_id
WHERE A.id = 1
--
David Portas
SQL Server MVP
--|||Wouldn't that result just be a bunch of 1's with all the id's from b
(assuming 1 is in a)? You need aXb to limit the pick records for a.id=1.|||"Jerry Porter" <jerryp@.personablepc.com> wrote in message news:1110302991.330487.242630@.z14g2000cwz.googlegroups.com...
|I think the first join should be the outer join. If there are no pick
| records for a client (I'm guessing that's what a is), there will be no
| record for them in aXb, and no record in the result. Actually, I think
| you need the outer join for both joins.
|
| (That seems really odd and/or dangerous to me that you have periods in
| the field names a.id, b.id in aXb. I guess those aren't the real names)
That's pseudo-sql ;-)
| Or maybe you could use a subquery with one outer join:
| SELECT * FROM a
| LEFT OUTER JOIN
| (Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
| ON a.id = PICK.a.id
| WHERE a.id = 1
Sorry, that didn't work.
Thanks, tho.
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Both of your suggestions worked as I asked. (I didn't ask the right question, tho). I was hoping to see a null in the "aXb" join so
I knew which rows in "b" linked to the row in "a". All the columns in the "aXb" join are returning NULL
I'll take off my obtuse hat and try to better state what I'm looking for.
"a" = Users
"b" = Roles
"aXb" = UsersXRoles
I'm looking to create a view that shows each user and all the roles they can be assigned to. I was hoping to alias a column of the
UsersXRoles table to indicate assignment, i.e.,
User Roles Assigned
---
UserA Group1 Yes
UserA Group2 No
UserA Group3 Yes
UserB Group1 No
UserB Group2 No
UserB Group3 Yes
etc.
I'm open to any suggestions. I'd like to stick with the existing data model (described in the op) if possible.
Thanks,
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Just wanted to followup and state that this query works exactly as I *need* it to. I just wasn't paying close attention when I was
cutting, pasting and editing from all my trial scripts.
Thanks David P.
ChrisG

Outer Join across many-to-many table ?

Given the following data model...
Table a (id int PK)
Table b (id int PK)
Table aXb (a.id int FK, b.id int FK, UNIQUE(a.id, b.id)
Scenario: "b" essentially represents a table of picklist data. I want to joi
n "a" to "b" in such a way that I get all rows in "b"
for each unique row in "a" (typically done with an outer join when "b" has a
FK to "a").
I tried this...
SELECT * FROM a
INNER JOIN aXb ON a.id = aXb.a.id
LEFT OUTER JOIN b ON aXb.b.id = b.id
WHERE a.id = 1
...but it isn't giving me what I want.
Can this be done?
Thanks,
ChrisGSee if this is what you want:
SELECT A.id, B.id
FROM A, B
WHERE A.id = 1
David Portas
SQL Server MVP
--|||I think the first join should be the outer join. If there are no pick
records for a client (I'm guessing that's what a is), there will be no
record for them in aXb, and no record in the result. Actually, I think
you need the outer join for both joins.
(That seems really odd and/or dangerous to me that you have periods in
the field names a.id, b.id in aXb. I guess those aren't the real names)
Or maybe you could use a subquery with one outer join:
SELECT * FROM a
LEFT OUTER JOIN
(Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
ON a.id = PICK.a.id
WHERE a.id = 1|||or maybe:
SELECT *
FROM A
CROSS JOIN B
LEFT JOIN AXB
ON A.id = AXB.a_id
AND B.id = AXB.b_id
WHERE A.id = 1
David Portas
SQL Server MVP
--|||Wouldn't that result just be a bunch of 1's with all the id's from b
(assuming 1 is in a)? You need aXb to limit the pick records for a.id=1.|||"Jerry Porter" <jerryp@.personablepc.com> wrote in message news:1110302991.33
0487.242630@.z14g2000cwz.googlegroups.com...
|I think the first join should be the outer join. If there are no pick
| records for a client (I'm guessing that's what a is), there will be no
| record for them in aXb, and no record in the result. Actually, I think
| you need the outer join for both joins.
|
| (That seems really odd and/or dangerous to me that you have periods in
| the field names a.id, b.id in aXb. I guess those aren't the real names)
That's pseudo-sql ;-)
| Or maybe you could use a subquery with one outer join:
| SELECT * FROM a
| LEFT OUTER JOIN
| (Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
| ON a.id = PICK.a.id
| WHERE a.id = 1
Sorry, that didn't work.
Thanks, tho.
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Both of your suggestions worked as I asked. (I didn't ask the right question
, tho). I was hoping to see a null in the "aXb" join so
I knew which rows in "b" linked to the row in "a". All the columns in the "a
Xb" join are returning NULL
I'll take off my obtuse hat and try to better state what I'm looking for.
"a" = Users
"b" = Roles
"aXb" = UsersXRoles
I'm looking to create a view that shows each user and all the roles they can
be assigned to. I was hoping to alias a column of the
UsersXRoles table to indicate assignment, i.e.,
User Roles Assigned
---
UserA Group1 Yes
UserA Group2 No
UserA Group3 Yes
UserB Group1 No
UserB Group2 No
UserB Group3 Yes
etc.
I'm open to any suggestions. I'd like to stick with the existing data model
(described in the op) if possible.
Thanks,
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Just wanted to followup and state that this query works exactly as I *need*
it to. I just wasn't paying close attention when I was
cutting, pasting and editing from all my trial scripts.
Thanks David P.
ChrisG

Outer Join - shifting result set.

I'm a quantitative securities analyst working with Compustat data
(company fiscal reports and pricing feeds).

My coworker came across a problem that we fixed, but I'd like to
understand 'why' it was happening and just don't get it yet.

Here's the starting query (reduced to simple prefixes):

--INITIAL--

declare @.mthDate datetime
set @.mthDate = (select max(datadate) from t)
declare @.wkDate datetime
set @.wkDate = (select max(datadate) from z)

Select
...
from
z
left join a on a.idA = z.idA and a.idB = z.idB
and a.datadate = z.datadate
left join b on b.idA = z.idA and b.idB = z.idB
and b.datadate = @.mthDate
left join c on c.idA = z.idA and c.idB = z.idB
and c.datadate = @.mthDate
left join d on d.idA = z.idA and d.idB = z.idB
and d.datadate = z.datadate
left join e on e.idA = z.idA
and e.datadate = @.mthDate
left join f on f.idA = e.idA and f.datadate=e.date2
left join g on g.idA = e.idA and g.datadate=e.date2
left join h on h.idA = z.idA
left join k on k.ticker = z.ticker
left join m on m.idA = z.idA and m.idB=z.idB
where
z.datadate = @.wkDate
<..some other expression filters...>
and k.ticker is null

--END INITIAL----

As you can see 'z' is the main table that things are linked to via
outer joins (our security master). Table 'k' has a list of securities
that we wish not to have results for.

There are 77 entries in table k and 4933 in table z for that given
time. We'd expect 4856 to be in this, but no. it's 4400, and then the
next time you run it (no changes whatsover) it's 2312, and so on.
Every time you execute you get a different record count.

My thought/and fix was to move the (k.ticker) predicate out of the
where clause and get a differenced set from z using NOT EXISTS:

--AMENDED-----
from
(z where not exists(select * from k where k.ticker=y.ticker)) y
left join a on a.idA = y.idA and a.idB = y.idB
and a.datadate = y.datadate
left join b on b.idA = y.idA and b.idB = y.idB
and b.datadate = @.mthDate
left join c on c.idA = y.idA and c.idB = y.idB
and c.datadate = @.mthDate
left join d on d.idA = y.idA and d.idB = y.idB
and d.datadate = y.datadate
left join e on e.idA = y.idA
and e.datadate = @.mthDate
left join f on f.idA = e.idA and f.datadate=e.date2
left join g on g.idA = e.idA and g.datadate=e.date2
left join h on h.idA = y.idA
left join k on k.ticker = y.ticker
left join m on m.idA = y.idA and m.idB=y.idB
where
y.datadate = @.wkDate
<..some other expression filters...
--------

And this works. It's stable now.

I'm hoping someone here can help me up the wisdom curve by explaining
to me 'why' the recordset kept changing before.

My guess is that the cost-based optimizer was resorting the outer joins
and handing back different sets as a result, but i want to understand,
and thought i'd come to this group for help.

I appreciate your time and look forward to replies.

Greg McIntireGreg,

it sounds like a bug to me. If the data doesn't change, then the query
result shouldn't change.

What version and service pack are you running? Can you create a
(simplified?) script that reproceduces the problem?

Gert-Jan

Greg wrote:
> I'm a quantitative securities analyst working with Compustat data
> (company fiscal reports and pricing feeds).
> My coworker came across a problem that we fixed, but I'd like to
> understand 'why' it was happening and just don't get it yet.
> Here's the starting query (reduced to simple prefixes):
> --INITIAL--
> declare @.mthDate datetime
> set @.mthDate = (select max(datadate) from t)
> declare @.wkDate datetime
> set @.wkDate = (select max(datadate) from z)
> Select
> ...
> from
> z
> left join a on a.idA = z.idA and a.idB = z.idB
> and a.datadate = z.datadate
> left join b on b.idA = z.idA and b.idB = z.idB
> and b.datadate = @.mthDate
> left join c on c.idA = z.idA and c.idB = z.idB
> and c.datadate = @.mthDate
> left join d on d.idA = z.idA and d.idB = z.idB
> and d.datadate = z.datadate
> left join e on e.idA = z.idA
> and e.datadate = @.mthDate
> left join f on f.idA = e.idA and f.datadate=e.date2
> left join g on g.idA = e.idA and g.datadate=e.date2
> left join h on h.idA = z.idA
> left join k on k.ticker = z.ticker
> left join m on m.idA = z.idA and m.idB=z.idB
> where
> z.datadate = @.wkDate
> <..some other expression filters...>
> and k.ticker is null
> --END INITIAL----
> As you can see 'z' is the main table that things are linked to via
> outer joins (our security master). Table 'k' has a list of securities
> that we wish not to have results for.
> There are 77 entries in table k and 4933 in table z for that given
> time. We'd expect 4856 to be in this, but no. it's 4400, and then the
> next time you run it (no changes whatsover) it's 2312, and so on.
> Every time you execute you get a different record count.
> My thought/and fix was to move the (k.ticker) predicate out of the
> where clause and get a differenced set from z using NOT EXISTS:
> --AMENDED-----
> from
> (z where not exists(select * from k where k.ticker=y.ticker)) y
> left join a on a.idA = y.idA and a.idB = y.idB
> and a.datadate = y.datadate
> left join b on b.idA = y.idA and b.idB = y.idB
> and b.datadate = @.mthDate
> left join c on c.idA = y.idA and c.idB = y.idB
> and c.datadate = @.mthDate
> left join d on d.idA = y.idA and d.idB = y.idB
> and d.datadate = y.datadate
> left join e on e.idA = y.idA
> and e.datadate = @.mthDate
> left join f on f.idA = e.idA and f.datadate=e.date2
> left join g on g.idA = e.idA and g.datadate=e.date2
> left join h on h.idA = y.idA
> left join k on k.ticker = y.ticker
> left join m on m.idA = y.idA and m.idB=y.idB
> where
> y.datadate = @.wkDate
> <..some other expression filters...>
> --------
> And this works. It's stable now.
> I'm hoping someone here can help me up the wisdom curve by explaining
> to me 'why' the recordset kept changing before.
> My guess is that the cost-based optimizer was resorting the outer joins
> and handing back different sets as a result, but i want to understand,
> and thought i'd come to this group for help.
> I appreciate your time and look forward to replies.
> Greg McIntire|||It's Version 8.00.194

While my ego would like to say it's not me, it's Microsoft, I'm pretty
doubtful. I think it's much more likely that it's a mental mistake...

Greg|||Well, could be, but I would install a service pack anyway. 8.00.194
means you have not installed any SQL-Server service pack, which
basically means you are missing all bug fixes of the last 5 years...

Gert-Jan

Greg wrote:
> It's Version 8.00.194
> While my ego would like to say it's not me, it's Microsoft, I'm pretty
> doubtful. I think it's much more likely that it's a mental mistake...
> Greg|||alright will talk to our tech guy. thanks a bunch.

Greg|||Greg (jacore70@.hotmail.com) writes:
> Select
> ...
> from
> z
> left join a on a.idA = z.idA and a.idB = z.idB
> and a.datadate = z.datadate
> left join b on b.idA = z.idA and b.idB = z.idB
> and b.datadate = @.mthDate
> left join c on c.idA = z.idA and c.idB = z.idB
> and c.datadate = @.mthDate
> left join d on d.idA = z.idA and d.idB = z.idB
> and d.datadate = z.datadate
> left join e on e.idA = z.idA
> and e.datadate = @.mthDate
> left join f on f.idA = e.idA and f.datadate=e.date2
> left join g on g.idA = e.idA and g.datadate=e.date2
> left join h on h.idA = z.idA
> left join k on k.ticker = z.ticker
> left join m on m.idA = z.idA and m.idB=z.idB
> where
> z.datadate = @.wkDate
> <..some other expression filters...>
> and k.ticker is null

The part with e, f and g looks suspicious to me. Not if I can tell
whether they are the cause of your SELECT:s returning a different
number. But you might want to have said:

left join (e
join f on f.idA = e.idA and f.datadate=e.date2
join g on g.idA = e.idA and g.datadate=e.date2)
on e.idA = z.idA
and e.datadate = @.mthDate

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Greg" <jacore70@.hotmail.com> wrote in message
news:1110566766.025803.171120@.l41g2000cwc.googlegr oups.com...
> I'm a quantitative securities analyst working with Compustat data
> (company fiscal reports and pricing feeds).
> My coworker came across a problem that we fixed, but I'd like to
> understand 'why' it was happening and just don't get it yet.
> Here's the starting query (reduced to simple prefixes):
>
> --INITIAL--
> declare @.mthDate datetime
> set @.mthDate = (select max(datadate) from t)
> declare @.wkDate datetime
> set @.wkDate = (select max(datadate) from z)
> Select
> ...
> from
> z
> left join a on a.idA = z.idA and a.idB = z.idB
> and a.datadate = z.datadate
> left join b on b.idA = z.idA and b.idB = z.idB
> and b.datadate = @.mthDate
> left join c on c.idA = z.idA and c.idB = z.idB
> and c.datadate = @.mthDate
> left join d on d.idA = z.idA and d.idB = z.idB
> and d.datadate = z.datadate
> left join e on e.idA = z.idA
> and e.datadate = @.mthDate
> left join f on f.idA = e.idA and f.datadate=e.date2
> left join g on g.idA = e.idA and g.datadate=e.date2
> left join h on h.idA = z.idA
> left join k on k.ticker = z.ticker
> left join m on m.idA = z.idA and m.idB=z.idB
> where
> z.datadate = @.wkDate
> <..some other expression filters...>
> and k.ticker is null
> --END INITIAL----
> As you can see 'z' is the main table that things are linked to via
> outer joins (our security master). Table 'k' has a list of securities
> that we wish not to have results for.
> There are 77 entries in table k and 4933 in table z for that given
> time. We'd expect 4856 to be in this, but no. it's 4400, and then the
> next time you run it (no changes whatsover) it's 2312, and so on.
> Every time you execute you get a different record count.
> My thought/and fix was to move the (k.ticker) predicate out of the
> where clause and get a differenced set from z using NOT EXISTS:
>
> --AMENDED-----
> from
> (z where not exists(select * from k where k.ticker=y.ticker)) y
> left join a on a.idA = y.idA and a.idB = y.idB
> and a.datadate = y.datadate
> left join b on b.idA = y.idA and b.idB = y.idB
> and b.datadate = @.mthDate
> left join c on c.idA = y.idA and c.idB = y.idB
> and c.datadate = @.mthDate
> left join d on d.idA = y.idA and d.idB = y.idB
> and d.datadate = y.datadate
> left join e on e.idA = y.idA
> and e.datadate = @.mthDate
> left join f on f.idA = e.idA and f.datadate=e.date2
> left join g on g.idA = e.idA and g.datadate=e.date2
> left join h on h.idA = y.idA
> left join k on k.ticker = y.ticker
> left join m on m.idA = y.idA and m.idB=y.idB
> where
> y.datadate = @.wkDate
> <..some other expression filters...>
> --------
> And this works. It's stable now.
> I'm hoping someone here can help me up the wisdom curve by explaining
> to me 'why' the recordset kept changing before.
> My guess is that the cost-based optimizer was resorting the outer joins
> and handing back different sets as a result, but i want to understand,
> and thought i'd come to this group for help.
> I appreciate your time and look forward to replies.
> Greg McIntire

Your guess sounds pretty reasonable to me.

If you use the show query plan setting in Query Analyzer, you can look and
see if the plan selected changes from execution to execution.

As a general rule ... I would take the variable comparison filtering out of
the joining and into the where clause.|||> As a general rule ... I would take the variable comparison filtering out of
> the joining and into the where clause.

You can't do that with outer joins, because that changes the meaning
(i.e. the resultset).

Gert-Jansql