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.
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.