ANSI vs. non-ANSI SQL JOIN syntax

Question

I have my business-logic in ~7000 lines of T-SQL stored procedures, and most of them has next JOIN syntax:

SELECT A.A, B.B, C.C
FROM aaa AS A, bbb AS B, ccc AS C
WHERE
    A.B = B.ID
AND B.C = C.ID
AND C.ID = @param

Will I get performance growth if I will replace such query with this:

SELECT A.A, B.B, C.C
FROM aaa AS A
JOIN bbb AS B
   ON A.B = B.ID
JOIN ccc AS C
   ON B.C = C.ID
   AND C.ID = @param

Or they are the same?

Answer 1

OK, they execute the same. That's agreed. Unlike many I use the older convention. That SQL-92 is "easier to understand" is debatable. Having written programming languages for pushing 40 years (gulp) I know that 'easy to read' begins first, before any other convention, with 'visual acuity' (misapplied term here but it's the best phrase I can use). When reading SQL the FIRST thing you mind cares about is what tables are involved and then which table (most) defines the grain. Then you care about relevant constraints on the data, then the attributes selected. While SQL-92 mostly separates these ideas out, there are so many noise words, the mind's eye has to interpret and deal with these and it makes reading the SQL slower.

SELECT Mgt.attrib_a   AS attrib_a
      ,Sta.attrib_b   AS attrib_b
      ,Stb.attrib_c   AS attrib_c
FROM   Main_Grain_Table  Mgt
      ,Surrounding_TabA  Sta
      ,Surrounding_tabB  Stb
WHERE  Mgt.sta_join_col  = Sta.sta_join_col
AND    Mgt.stb_join_col  = Stb.stb_join_col
AND    Mgt.bus_logic_col = 'TIGHT'

Visual Acuity! Put the commas for new attributes in front It makes commenting code easier too Use a specific case for functions and keywords Use a specific case for tables Use a specific case for attributes Vertically Line up operators and operations Make the first table(s) in the FROM represent the grain of the data Make the first tables of the WHERE be join constraints and let the specific, tight constraints float to the bottom. Select 3 character alias for ALL tables in your database and use the alias EVERYWHERE you reference the table. You should use that alias as a prefix for (many) indexes on that table as well. 6 of 1 1/2 dozen of another, right? Maybe. But even if you're using ANSI-92 convention (as I have and in cases will continue to do) use visual acuity principles, verticle alignment to let your mind's eye avert to the places you want to see and and easily avoid things (particularly noise words) you don't need to.