Nov
26

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

SELECT E.FirstName, E.LastName, D.DepartmentName
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

SELECT E.FirstName, E.LastName, D.DepartmentName
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.

Aaron West's Gravatar
About this post:

This entry was posted by Aaron West on November 26, 2006 at 10:46 PM. It was filed in the following categories: ColdFusion, SQL. It has been viewed 28904 times and has 8 comments.

8 Responses to SQL Tip: Rewriting Old-Style Queries Into Joins

  1. Jack's Gravatar Jack

    Who said the first method is the old one? It is also a standard SQL syntax!

    If you also use "old" method in a proper way, you would have more efficient query results.

  2. Polygraf's Gravatar Polygraf

    Exactly, apart from the fact that joins are far more taxing on the server than the simpler method of using WHERE clauses.

  3. Dunno about anyone else, but I can never remember how inner, outer, left and right JOIN work, so for me the former syntax is much clearer.

  4. Tom,

    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.

  5. Ryan Cooper's Gravatar Ryan Cooper

    There is no perfomance difference in the 2 join syntaxes above. The
    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

  6. I like using the JOIN / ON syntax as it allows you to put WHERE criteria directly into the ON clause. While this doesn't make much of a difference with a good SQL optimizer, since the ON clause it processed before the WHERE clause (it comes directly before in the command execution list), it gives you the ability to limit the size of the record set that is available at the time of WHERE clause execution. A minor, but cool optimization.

  7. Adam Ness's Gravatar Adam Ness

    As an aside, I've been told by our DBA that in Microsoft SQL Server 2005 the "old" ANSI method of joining in the From Clause is deprecated.

    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'

  8. Just like Steve I used to prefer what I called "old style" queries because they made more sense to me. Once I latched on to actually writing the joins my SQL became more clear than ever before. To me it is partially a style issue and a clarity issue. While Ryan says there may be no performance gains I can't help but think SQL Server intends for us to write join statements in the from clause since that is how the server itself will write them through the process I outlined in this post.

    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.

Leave a Reply

Leave this field empty

If you subscribe, any new posts to this thread will be sent to your email address.

RSS