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
- Lower case all keywords. See Why Shout in your SQL?
- Keywords that group sections – select, from, where, group by, having, order by, and limit should be on their own line with no leading spaces.
- Code between the keyword groupings should be indented
- Indentation should be 3 spaces.
- No line should exceed 79 characters
- All table names must have an alias, if more than one table is used
- All field names should be prefixed with an alias, if more than one table is used
- Select, group by, and order by clauses should have each field on it’s own line, but may not if the list is small.
- 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.
- Select clauses may horizontally align ‘as’ names
- From clause should have each table on its own line
- From join clauses may want to have each join condition on its own line indented in, if they are long
- Where and having clauses should have each ‘and’ condition on its own line
- Conditions may horizontally align on =, <, <=, >, >=, !=
- Joining keywords on, and, and or should be never start a new line.
- Nested queries should be formated the same way with additional indentation necessary to line them up
- 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.