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.
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.
Another list of standards: http://www.sqlstyle.guide/