Bad Habits to Kick : Using old-style JOINs
See the full index.
I am sure most veterans know better than to use old ANSI-89 JOIN syntax, such as:
SELECT o.OrderID, od.ProductID FROM dbo.Orders AS o, dbo.OrderDetails AS od WHERE o.OrderDate >= '20091001' AND o.OrderID = od.ProductID;
One reason to avoid this syntax is that the query is often less readable than when you separate the join criteria from the filter criteria. For example, the above query re-written to use a "proper" join becomes:
SELECT o.OrderID, od.ProductID FROM dbo.Orders AS o INNER JOIN dbo.OrderDetails AS od ON o.OrderID = od.ProductID WHERE o.OrderDate >= '20091001';
This is subjective, of course, but seems to have broad support in my circles.
Now, bring in four or five more tables to the query, and then try to debug when you are not getting the expected number of rows. When tables are grouped together with their joining criteria, commenting clauses out or changing the criteria becomes a much simpler task.
OUTER JOIN version of this syntax (which uses
=* syntax) is discontinued in SQL Server 2012, and has been deprecated since SQL Server 2008, so there is another reason to avoid the syntax in general. Many people have memorized which is which, but deprecation aside, isn't it better to explicitly state what you are doing? For example:
SELECT p.ProductName, p.ProductID FROM dbo.Products AS p LEFT OUTER JOIN dbo.OrderDetails AS od ON p.ProductID = od.ProductID WHERE od.ProductID IS NULL;
There are also documented cases where the old-style outer joins produce incorrect results depending on the ordering of evaluation of the "filter" criteria (which happened to include the joining criteria). Largely a moot point now, since the old outer join syntax is deprecated, but I wanted to point it out all the same
Are you sold yet? No? Well, there is an even bigger potential problem in using the implicit syntax. What if you forget the join criteria? You've inadvertently turned it into a
CROSS JOIN (otherwise known as the Cartesian Product). Take the first query again, and leave out the last line:
SELECT o.OrderID, od.ProductID FROM dbo.Orders AS o, dbo.OrderDetails AS od WHERE o.OrderDate >= '20091001';
For every single order after October 1st, you're going to get a copy of every single row from
OrderDetails *for all of time* since you've lost the joining criteria *and* the filtering criteria for that table. This could be a disastrous mistake on a production system, and while it can be caught relatively quickly after deployment and testing, it could have been prevented entirely at compile time. The explicit
INNER JOIN syntax requires an
ON clause. Sure, you can still get your join criteria wrong and push the change without noticing (for example,
ON o.OrderID = o.OrderID), but with the newer syntax, there's one less thing that is likely to go wrong.
My biggest beef about trying to help people kick these habits is that Microsoft themselves keep publishing sample code that promotes the syntax. Again, this isn't so much a problem for the veterans (except those with insurmountable inertia), who fully understand how joins work, and know the history of ANSI-89 and ANSI-92. However, for someone brand new to SQL Server, they will pick up a current copy of Books Online, and see sample queries that use these bad examples.
I once complained about this on () Connect, when I came across yet another example of old-style joins in the SQL Server 2008 version of Books Online. They quickly agreed that, when they review the documentation for 2008 R2, they will update any code samples they deem "suspect."
See the full index.