SQL & how different databases implement it

Some vaguely structured thoughts on the differences that you see between SQL as implemented by one DBMS and that of another.

11 March 2006


How do you layout SQL statements?

Do you try to establish a visual style for laying out your SQL statements? I have a particular style for my SQL, but to be honest I am not sure if it adds any value or not. Your feedback would be appreciated.

In essence what I try to do is have a single space after the first keyword of the statement which is carried through a vertical line all the way down the subsequent lines of the SQL statement.

For example:
SELECT ramp_agent_id
  FROM dispatches
 WHERE delay_mins > 5

Notice how I have right justified the keywords to the left of the vertical spacer (i.e. SELECT, FROM and WHERE) and left justified text to the right of the spacer.

Group By is too long to fit as a whole to the left of the spacer, so I only fit Group to the left and put By to the right. For example:
SELECT ramp_agent_id, COUNT(*)
  FROM dispatches
 WHERE delay_mins > 5
 GROUP BY ramp_agent_id

Although when adding a group by I could move the first three lines of the above example to the right so that the vertical spacer was after "BY" I deliberately don't do this as 2 or more select statements near each other in the code, one with and one without a group by look odd (to me).

Things get a little more complicated with more complex WHERE clauses. This is how I deal with that:
SELECT ramp_agent_id, COUNT(*)
  FROM dispatches
 WHERE delay_mins > 5
   AND (   delay_code = 500
        OR flight_status = 'A')
   AND destination = 'LHR'
 GROUP BY ramp_agent_id

Here I am lining up a subsidiary vertical spacer in the WHERE clause to align the various predicates that are placed inside the brackets.

The above is based on my background with Oracle (the DBMS I was using when I was first paying serious attention to these things).

Anybody got other schemes they use?

Comments: Post a Comment

Subscribe to Post Comments [Atom]

<< Home



September 2005   October 2005   December 2005   January 2006   March 2006   May 2006   June 2006   September 2006   May 2007   June 2007   November 2007   December 2007   March 2008   April 2008   December 2008   January 2011  

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]