SQL syntax - INNER or OUTER JOIN vs. WHERE

[mheydman@yahoo.com]

I apologize if this has been asked before- I searched google but could
not find a concrete answer.

I recently inherited a database whose t-sql code is written in a format
that I find difficult to read (versus the format I have used for
years).

I have tested the queries below using the SQL Profiler, and both have
identical costs. Is there any advantage of one format over the other?

Format 1:
---------
SELECT *
FROM Customers c, Orders o
WHERE c.CustomerID = o.CustomerID


Format 2:
---------
SELECT *
FROM Customers c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID


Is it just a matter of personal preference? Does the same hold true for
using OUTER JOIN versus the old *= or =* ?

Thanks,

Matt

[David Portas]

Re: SQL syntax - INNER or OUTER JOIN vs. WHERE

The two statements you posted are logically identical. There isn't really a
practical reason to prefer one over the other, it's largely a matter of
personal style and readability. Some people prefer the INNER JOIN syntax and
some prefer just to use WHERE.
[color=blue]
> Does the same hold true for
> using OUTER JOIN versus the old *= or =* ?[/color]

No. You should never use the *= / =* syntax. This notation was deprecated in
the ANSI SQL92 standard and Microsoft has stated that support for the now
obsolete syntax may be dropped from a future version of SQL Server. The
SQL92 OUTER JOIN syntax is much more powerful because it allows joins to be
based on expressions other than equality (something that wasn't possible
under the old method). Also, under the old syntax some outer join
specifications could have more than one possible interpretation - different
RDBMS products interpreted these queries in different ways, which caused
portability problems.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Re: SQL syntax - INNER or OUTER JOIN vs. WHERE

[posted and mailed, please reply in news]

(mheydman@yahoo .com) writes:[color=blue]
> I have tested the queries below using the SQL Profiler, and both have
> identical costs. Is there any advantage of one format over the other?
>
> Format 1:
> ---------
> SELECT *
> FROM Customers c, Orders o
> WHERE c.CustomerID = o.CustomerID
>
>
> Format 2:
> ---------
> SELECT *
> FROM Customers c
> INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
>
>
> Is it just a matter of personal preference?[/color]

Yes. I too found the second format bulky and verbose when I first
encountered. These days, it is the only format I use. I find it
much clearer, as I can separate the join conditions from the filter
conditions. And I can't do a cartesian join by mishap, I have to
say CROSS JOIN the few times I need it.

But as long as you are only doing inner joins it is just a matter of
style.
[color=blue]
> Does the same hold true for using OUTER JOIN versus the old *= or =* ?[/color]

No, this is where the map changes completely. David mentioned a few
advantages with the newer syntax, but permit me to add a few more:

* You can do a FULL JOIN.
* You can inner-join the outer table to a lookup table.

There a few gotchas with the new syntax. It is a common error to say:

SELECT ...
FROM A LEFT JOIN B ON A.col = B.col
WHERE B.col = 12

and when wonder where all non-matching rows of A went. The answer is
that the whole FROM constructs a virtual table, and then the WHERE
conditions filter that table. Which means that here all rows with
NULL for B.col goes out the window. The remedy is one of:

SELECT ...
FROM A LEFT JOIN B ON A.col = B.col
WHERE (B.col = 12 OR B.col IS NULL)

SELECT ...
FROM A LEFT JOIN B ON A.col = B.col
AND B.col = 12

To do what I mentioned above, inner-join the outer table to a lookup
table you need a syntax which is not documented in Books Online:

SELECT ...
FROM a LEFT JOIN (b JOIN c ON b.lookupcol = c.lookupcol)
ON a.col = b.col

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Matt]

Re: SQL syntax - INNER or OUTER JOIN vs. WHERE

Thank you both for the clear explanations. I now see it would be in my
best interest to adopt the SQL92 conventions and adjust my coding
styles appropriately. I must admit the OUTER JOIN syntax seems very
foreign, but I look forward to experimenting with the new
functionality.

Thanks again,

Matt



Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns952BF4 0724C4AYazorman @127.0.0.1>...[color=blue]
> [posted and mailed, please reply in news]
>
> (mheydman@yahoo .com) writes:[color=green]
> > I have tested the queries below using the SQL Profiler, and both have
> > identical costs. Is there any advantage of one format over the other?
> >
> > Format 1:
> > ---------
> > SELECT *
> > FROM Customers c, Orders o
> > WHERE c.CustomerID = o.CustomerID
> >
> >
> > Format 2:
> > ---------
> > SELECT *
> > FROM Customers c
> > INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
> >
> >
> > Is it just a matter of personal preference?[/color]
>
> Yes. I too found the second format bulky and verbose when I first
> encountered. These days, it is the only format I use. I find it
> much clearer, as I can separate the join conditions from the filter
> conditions. And I can't do a cartesian join by mishap, I have to
> say CROSS JOIN the few times I need it.
>
> But as long as you are only doing inner joins it is just a matter of
> style.
>[color=green]
> > Does the same hold true for using OUTER JOIN versus the old *= or =* ?[/color]
>
> No, this is where the map changes completely. David mentioned a few
> advantages with the newer syntax, but permit me to add a few more:
>
> * You can do a FULL JOIN.
> * You can inner-join the outer table to a lookup table.
>
> There a few gotchas with the new syntax. It is a common error to say:
>
> SELECT ...
> FROM A LEFT JOIN B ON A.col = B.col
> WHERE B.col = 12
>
> and when wonder where all non-matching rows of A went. The answer is
> that the whole FROM constructs a virtual table, and then the WHERE
> conditions filter that table. Which means that here all rows with
> NULL for B.col goes out the window. The remedy is one of:
>
> SELECT ...
> FROM A LEFT JOIN B ON A.col = B.col
> WHERE (B.col = 12 OR B.col IS NULL)
>
> SELECT ...
> FROM A LEFT JOIN B ON A.col = B.col
> AND B.col = 12
>
> To do what I mentioned above, inner-join the outer table to a lookup
> table you need a syntax which is not documented in Books Online:
>
> SELECT ...
> FROM a LEFT JOIN (b JOIN c ON b.lookupcol = c.lookupcol)
> ON a.col = b.col[/color]