Books I've recently read and recommend!
Too often I forget about this handy SQL trick. Maybe I'll help to cement it in my brain by blogging it and hopefully someone out there will find it useful. Nearly every week I find myself rewriting SQL queries on an application that I didn't originally build. This typically involves replacing join criteria written in the where clause with equivalent joins in the from clause. Most of the time I just rewrite the statements on my own but you don't have to. Take the following example for instance:
Old School Method
FROM Employees E, Departments D
WHERE E.fkDepartment = D.ID AND D.ID = 5
This query seems simply enough, and it is. But when you add additional tables to the joins in the where clause and perhaps more filter criteria in the where, things quickly get unreadable. SQL's join statement does a much better job at specifying how your tables relate to one another without any filter criteria (like department ID = 5) getting in the way. Here's the above query rewritten using joins.
Preferred Method
FROM Employees E INNER JOIN Departments D ON E.fkDepartment = D.ID
WHERE D.ID = 5
While you can rewrite your old queries on your own let SQL Server's tools do the work for you. Open SQL Server's Enterprise Manager and right-click any table. Elect to view the contents of the table and once they show up hit the "SQL" button at the top left in Enterprise Manager. This will show you the SQL used to display the contents of your table. Next, copy the offending query and paste it over the SQL Enterprise Manager is displaying. Press the "!" (run) button and Enterprise Manager will take your old query, rewrite it using the appropriate joins, and display the results. Pretty handy.
If you also use "old" method in a proper way, you would have more efficient query results.
I used to find the join syntax more difficult as well. Once I started using it, however, I found that it really made the query much more clear. It is nice to have a clear indicator of which criteria or for a join and which criteria are really part of the filter.
The only drawback that I have found to the "new" method is that Access limits sequential joins to two tables - forcing you to use the "old" method or complicate your joins with parenthetical groupings.
As to performance, my tests haven't shown any significant difference in performance. I prefer the join syntax for clarity. In most cases, clarity trumps performance any way.
query optimizer in SQL Server sees the two queries excatly the same.
Test for yourself by checking out the Execution Plans.
The main reason for using the join in the from clause is because
1) it is ANSI standard complient
2) you get the same query with less code
3) better support for full outer joins
Personally, I prefer the LEFT JOIN ... ON ... syntax, because it makes it clearer which parts of the where clause are filters, and which parts are relational between tables.
As to the Access complaint, you can still use multiple tables in the join clause with Access, but you have to parenthesize them:
SELECT p.name, m.name as Manager, d.name as divisionName
FROM (( People p
LEFT JOIN People m ON p.managerID = m.ID)
LEFT JOIN Divisions d ON p.DivisionID = d.ID )
WHERE p.loginid = 'foo'
Furthermore, SQL 2005 does indeed deprecate some of the syntax. Apparently, writing outer joins in the where clause will no longer be supported.
All these things, when taken together, are why I personally prefer (and require of my development team) joins in the from clause.