The preferred T-SQL syntax for joining tables is by using JOIN. The alternate syntax, the comma join syntax, is still being used out there. In my opinion, we should all forget about that old method and embrace the JOIN syntax.
Here is an example of the comma join syntax:
SELECT pers.FirstName, pers.LastName, p.Name, p.ListPriceFROM Sales.Customer AS c, Sales.SalesOrderHeader AS soh, Sales.SalesOrderDetail AS sod, Production.Product AS p, person.Person AS persWHERE p.ProductID = sod.ProductID AND c.CustomerID = soh.CustomerID AND soh.SalesOrderID = sod.SalesOrderID AND c.PersonID = pers.BusinessEntityID;
Here is the same query using the JOIN syntax:
SELECT pers.FirstName, pers.LastName, p.Name, p.ListPrice FROM Sales.Customer AS cINNER JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerIDINNER JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderIDINNER JOIN Production.Product AS p ON sod.ProductID = p.ProductID INNER JOIN person.Person AS pers ON pers.BusinessEntityID = c.PersonID
The comma join syntax lets you list the tables separated by commas. You add the join conditions to the WHERE clause. While there are several reasons that this syntax should no longer used, the main reason is that you can longer do an outer join with this syntax unless the database is in 2000 compatibility mode. In 2000 and earlier versions, you could do a LEFT OUTER JOIN by changing the equal sign to an asterisk + equal sign (*=) or a RIGHT OUTER JOIN by changing the equal sign to an equal sign + asterisk (=*). This outer join syntax not ANSI-compliant.
At least for these two queries, the execution plan is identical. SQL Server treats the comma join query as a JOIN query.
Besides not being able to do an outer join, here are some other reasons for embracing the JOIN syntax:
Since you are going to have to switch to the JOIN syntax any time you need to an outer join, just stop using comma join today!