SQL Formatting

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:

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


  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
   a.col              as name1
   a.long_column_name as name2,
   func(a.other)      as name3
Long join statements
   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
      when t.trans_type is null then
      when t.trans_type in (20, 50) then
         (-1) * qty * amount
          qty * amount
      end as total
   o.id as order_id
   orders o
   left join (
         count(*) as orders
      ) ois on o.id = ois.order_id
   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)
   ('customers', 'status_id', 8056, 10, 200)
The update statement

Same general rules apply

   status_id = 500
   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


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.

2 thoughts on “SQL Formatting”

  1. Comments received via email include:

    Leaving keywords uppercase, not putting semicolon on its own line, not lining up text, and (this was a big one) not to use 3 space indentation. Use 4.

    I can live with all of that.

Leave a Reply

Your email address will not be published. Required fields are marked *