Monthly Archives: March 2015

database, Elegance, SQL

SQL Formatting

2 Comments

Let’s standardize how we write SQL.  What joy.   Rarely, have I seen consistency in how SQL is formatted, and often it is not even considered.

Here is the standard I use which makes it  easy to read both simple and complex queries.  Here is an example:

select
   b.id                                       as book_id,
   b.title                                    ,
   group_concat(a.first_name, ' ', 
                a.last_name, separator ', ')  as authors,
   b.published                                ,
   b.isbn
from
   books b
   left join book_authors ba on b.id = ba.book_id
   left join authors a on ba.author_id = a.id 
where
   b.published < '1900' and
   a.nationality = 'English'
group by
   b.id
order by
   b.published desc,
   b.title
limit
   100
;

Rules

  1. Lower case all keywords. See Why Shout in your SQL?
  2. Keywords that group sections – select, from, where, group by, having, order by, and limit should be on their own line with no leading spaces.
  3. Code between the keyword groupings should be indented
  4. Indentation should be 3 spaces.
  5. No line should exceed 79 characters
  6. All table names must have an alias, if more than one table is used
  7. All field names should be prefixed with an alias,  if more than one table is used
  8. Select, group by, and order by clauses should have each field on it’s own line, but may not if the list is small.
  9. Select fields should be broken up into multiple properly indented lines when long or complicated.  For example, when using functions like concat_ws, substring, etc.
  10. Select clauses may horizontally align ‘as’ names
  11. From clause should have each table on its own line
  12. From join clauses may want to have each join condition on its own line indented in, if they are long
  13. Where and having clauses should have each ‘and’ condition on its own line
  14. Conditions may horizontally align on =, <, <=, >, >=, !=
  15. Joining keywords on, and, and or should be never start a new line.
  16. Nested queries should be formated the same way with additional indentation necessary to line them up
  17. Semicolon should be on it’s own line with no leading spaces.

More Examples

Line up field ‘as’ names
select
   a.col              as name1
   a.long_column_name as name2,
   func(a.other)      as name3
Long join statements
from
   usage u
   left join usage_detail ud on
      u.usage_id = ud.usage_id and
      u.status_id <= 500 and
      (ud.trans_type = 'TD' or ud.position = 'R26')
The case statement
select
   case
      when t.trans_type is null then
         0
      when t.trans_type in (20, 50) then
         (-1) * qty * amount
      else
          qty * amount
      end as total
Subqueries
select
   o.id as order_id
   o.order_date,
   ois.orders
from
   orders o
   left join (
      select
         order_id
         count(*) as orders
      from
         order_details
      ) ois on o.id = ois.order_id
where
   o.order_date       >= '2015-02-01 and
   date(o.order_date) <  '2015-03-01
;
The insert statement

Same general rules apply

insert into audits
   (table_name, column_name, id, old_value, new_value)
values
   ('customers', 'status_id', 8056, 10, 200)
;
The update statement

Same general rules apply

update
   customers
set
   status_id = 500
where
   customer_id = 8011
;
Table creation scripts
set foreign_key_checks = 0;

-- drop table /*! if exists */ parties;

create table parties (
  party_id          integer unsigned  not null  auto_increment primary key ,
  party_type_id     integer unsigned  not null ,
  institution_id    integer unsigned  ,
  name              varchar(255)      ,
  first_name        varchar(255)      ,
  middle_name       varchar(255)      ,
  last_name         varchar(255)      ,
  username          varchar(255)      ,
  email             varchar(255)      ,
  phone             varchar(255)      ,
  phone2            varchar(255)      ,
  source            varchar(255)      ,
  comments          text              ,

  created           datetime          not null ,
  last_updated      timestamp         not null
        default current_timestamp on update current_timestamp ,

  unique key name_institution (name, party_type_id, institution_id),
  foreign key (party_type_id)  references party_types (party_type_id),
  foreign key (institution_id) references institutions (party_id)
)
engine InnoDB default charset=utf8;
;

show warnings;

set foreign_key_checks = 1;

desc parties;

Unix utility: sql_pretty

Here is a simple utility that will reformat SQL. It does most of it. It is a work in progress.  It uses regex but needs to do a full SQL parse to do everything: sql_pretty

Concluding

These conventions have made my life easier. Easy to read code is good code is easy living. So go clean up your SQL scripts!  And have fun.