Stylepart of SQL for Web Nerds by Philip Greenspun |
Doesn't seem so simple, eh? How about if we rewrite it:select user_id, count(*) as how_many from bboard where not exists (select 1 from bboard_authorized_maintainers bam where bam.user_id = bboard.user_id) and posting_time + 60 > sysdate group by user_id order by how_many desc;
If your code isn't properly indented then you will never be able to debug it. How can we justify using the word "properly"? After all, the SQL parser doesn't take extra spaces or newlines into account.select user_id, count(*) as how_many from bboard where not exists (select 1 from bboard_authorized_maintainers bam where bam.user_id = bboard.user_id) and posting_time + 60 > sysdate group by user_id order by how_many desc;
Software is indented properly when the structure of the software is revealed and when the indentation style is familiar to a community of programmers.
If it doesn't fit nicely on one line, give each clause a separate line:select email from users where user_id = 34;
If the stuff for a particular clause won't fit on one line, put a newline immediately after the keyword that opens the clause. Then indent the items underneath. Here's an example from the ArsDigita Community System's static .html page administration section. We're querying theselect * from news where sysdate > expiration_date and approved_p = 't' order by release_date desc, creation_date desc
static_pages
table, which holds a copy of any
.html files in the Unix file system:
In this query, there are two items in the select list, a count of all the rows and a sum of the bytes in theselect to_char(count(*),'999G999G999G999G999') as n_pages, to_char(sum(dbms_lob.getlength(page_body)),'999G999G999G999G999') as n_bytes from static_pages;
page_body
column (of
type CLOB, hence the requirement to use dbms_lob.getlength
rather than simply length
). We want Oracle to format these
numbers with separation characters between every three digits. For
this, we have to use the to_char
function and a mask of
'999G999G999G999G999'
(the "G" tells Oracle to use the
appropriate character depending on the country where it is installed,
e.g., comma in the U.S. and period in Europe). Then we have to give the
results correlation names so that they will be easy to use as Tcl
variables. By the time we're done with all of this, it would be
confusing to put both items on the same line.
Here's another example, this time from the top-level comment administation page for the ArsDigita Community System. We're going to get back a single row with a count of each type of user-submitted comment:
Notice the use ofselect count(*) as n_total, sum(decode(comment_type,'alternative_perspective',1,0)) as n_alternative_perspectives, sum(decode(comment_type,'rating',1,0)) as n_ratings, sum(decode(comment_type,'unanswered_question',1,0)) as n_unanswered_questions, sum(decode(comment_type,'private_message_to_page_authors',1,0)) as n_private_messages from comments
sum(decode
to count the number of each
type of comment. This gives us similar information to what we'd get
from a GROUP BY, but we get a sum total as well as category totals.
Also, the numbers come out with the column names of our choice. Of
course, this kind of query only works when you know in advance the
possible values of comment_type
.
Next: proceduralselect links.user_id, first_names, last_name, count(links.page_id) as n_links from links, users where links.user_id = users.user_id group by links.user_id, first_names, last_name order by n_links desc
The where clause is in two lines in the example above though the text suggests one line:
"If it doesn't fit nicely on one line, give each clause a separate line:
select *
from news
where sysdate > expiration_date
and approved_p = 't'
order by release_date desc, creation_date desc"
In this case, one line would be the better, of course.
-- Peter Tury, June 12, 2002