* Question about SQL 2000 query

20 Jul 2006 8:36 PM

David R.

In SQL 2000 Enterprise Manager, when I type this query:

SELECT     *
FROM         Users u, Companies c
where u.CompanyID = c.CompanyID

it always converts to:

SELECT     *
FROM         USERS u INNER JOIN
                      COMPANIES c ON u.CompanyID = c.CompanyID

Why does it do that? Can I prevent it from doing the conversion?

20 Jul 2006 8:46 PM

Narayana Vyas Kondreddi

No, you have no control over that behavior. Further, you are using the old
style join syntax. You better get used to the new ANSI standard join syntax.
Also, Enterprise Manager is not the right tool for writing queries. Please
use Query Analyser instead.

--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/

Show quote

"David R." <davidrnoreply@nospam.com> wrote in message
news:%23ecQYwDrGHA.3592@TK2MSFTNGP02.phx.gbl...
> In SQL 2000 Enterprise Manager, when I type this query:
>
>
> SELECT     *
> FROM         Users u, Companies c
> where u.CompanyID = c.CompanyID
>
>
> it always converts to:
>
>
> SELECT     *
> FROM         USERS u INNER JOIN
>                      COMPANIES c ON u.CompanyID = c.CompanyID
>
>
> Why does it do that? Can I prevent it from doing the conversion?
>

20 Jul 2006 8:47 PM

David Portas

David R. wrote:
Show quote

> In SQL 2000 Enterprise Manager, when I type this query:
>
>
> SELECT     *
> FROM         Users u, Companies c
> where u.CompanyID = c.CompanyID
>
>
> it always converts to:
>
>
> SELECT     *
> FROM         USERS u INNER JOIN
>                       COMPANIES c ON u.CompanyID = c.CompanyID
>
>
> Why does it do that? Can I prevent it from doing the conversion?

Most people seem to prefer the INNER JOIN syntax. For one thing it is
much easier to understand when you have multiple joins involved. As to
why EM's query designer does it that way, it's probably because the
JOIN syntax is required for outer joins so I expect they wanted a
consistent approach for inner joins too.

Is there a way to avoid it? Certainly. Write your queries in Query
Analyzer. That's what it's best for. The EM designer is a very blunt
instrument with which to develop queries.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

21 Jul 2006 1:02 AM

Bob

I actually find the syntax

> > SELECT *
> > FROM Users u, Companies c
> > where u.CompanyID = c.CompanyID

easier to understand than

> > SELECT     *
> > FROM         USERS u INNER JOIN
> >                       COMPANIES c ON u.CompanyID = c.CompanyID

I'd like to learn and understand the latter constructs. Can you direct me
to a site that explains it well?

Show quote

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1153428420.106771.97330@b28g2000cwb.googlegroups.com...
> David R. wrote:
> > In SQL 2000 Enterprise Manager, when I type this query:
> >
> >
> > SELECT     *
> > FROM         Users u, Companies c
> > where u.CompanyID = c.CompanyID
> >
> >
> > it always converts to:
> >
> >
> > SELECT     *
> > FROM         USERS u INNER JOIN
> >                       COMPANIES c ON u.CompanyID = c.CompanyID
> >
> >
> > Why does it do that? Can I prevent it from doing the conversion?
>
>
> Most people seem to prefer the INNER JOIN syntax. For one thing it is
> much easier to understand when you have multiple joins involved. As to
> why EM's query designer does it that way, it's probably because the
> JOIN syntax is required for outer joins so I expect they wanted a
> consistent approach for inner joins too.
>
> Is there a way to avoid it? Certainly. Write your queries in Query
> Analyzer. That's what it's best for. The EM designer is a very blunt
> instrument with which to develop queries.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>