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.