I recently came across some sql code that caught me off guard. Here is is:
select something
from table1, table2
where table1.id = table2.id
I immediately thought that a
Cartesian product was happening and the rows were being filtered afterward. All my database experience has been with Microsoft databases, so I didn't know that this syntax used to be the way most people did sql.. Like any good engineer, I set out to find out for myself what was really going on.
I used the Northwind database to compare the following two queries: the first with Sql server syntax, and the second with "old school" syntax (which Sql server 2000 suppports).
SELECT *
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
SELECT *
FROM Customers c, Orders o, [Order Details] od, Products p
WHERE o.CustomerID = c.CustomerID
AND o.OrderID = od.OrderID
AND od.ProductID = p.ProductID
I ran these two queries many, many times together and in isolation, and I examined the execution plans, the client statistics as well as the Sql trace. It appears that at a lower level, these two operations are identical. Both queries took the same Duration, CPU cycles, and Reads to execute. Here is the Execution plan. Both queries have this same exact exectuion plan:
Personally, I like the INNER JOIN syntax. It's very explicit, and it's easy to add RIGHT and LEFT to dictate OUTER joins. A plus is that it is the ANSI standard and Microsoft's recommendation for Sql Server.
The
objective conclusion of this experiment is that the style picked for a query will not affect the speed at which that query runs. The differences are
subjective. My advice, however, is that a single style be adopted as part of the team's coding standard.