Could someone advise me on the following
I am in the very early stages of the process of migrating an application from using an access 97 database (jet 3.51) to a sql server 2005 express database.
As part of my learning curve I was reading John Connell's book (Beginning vb6 databases). In this he said that "outer joins" via the jet engine applied the filtering of the "where" clause before the tables are joined whereas the "outer join" in sql server applied the filter after the joining of the tables.
That this was because SQL server was developed before the ANSI standard was brought in whereas Access 97 complied with ANSI standards.
As my application has hundreds of these joins in the code this was a bit of a shock.
This thread seems to suggest that SQL server 2005 now does joins the same way as Access 97.
Is this correct?
Thanks

I'll take that as ..um..a yes?

Probably not... I'd say the silence means that no one knows off the top of their head.

--Jeff Moden

SQL Server 2000 (and maybe before that, but surely with 2000) does the WHERE clause after the JOIN.

I have an old post that demos this and the side effects and and and.

http://www.aspmessageboard.com/forum...96501&F=20&P=1

And SQLTeam.com (a site that specializes in SQL Server) has a similar thread/article:

http://www.sqlteam.com/item.asp?ItemID=11122

But quite frankly, I thought that this action *WAS* in compliance with the ANSI spec. *AND* the actual test that I performed, as I showed it in my post noted above, was run on an Access2002 database!!! So if Access97 is different, then *IT* is the "odd man out" here.

Just because it had been so many years since I wrote that post, I wanted to ensure that senility hasn't yet set in.

It hasn't.

I just tried it with Access2003 and indeed Access *DOES* apply the WHERE *AFTER* the JOIN. So... I dunno if Access97 really was different, but I tend to doubt it. JJB: I think you misread or misremember that article. I think SQL Server *USED* to do the WHERE *before* the JOIN (when it used the old style joins) and *NOW* it does them after, same as Access has always done.

If you don't believe me about Access, I'd be happy to send you a ".mdb" file that proves what I say.