Pages

Sunday 1 August 2010

Readable queries


It is important to every developer, to write semantic query to allow himself or other to easily understand what a query is intended to do. In this post I'm going to describe a simple technique that I use every day to make my database queries easy to read and understand.
Let us suppose that we have the following database:


Casing

Write your queries using Pascal Casing, i.e, capitalize the first character of every word as in:

Select * From Student

Joins
When writing 'joins', put the main entity in the first place. For instance, if I need to know all the students and their courses, notice that here I am talking about the students, so the query would look like this:

Select *
From Student s Join StudentCourse sc
On s.StudentId = sc.StudentId

 

In this query, notice that I used an alias for each table, since my tables are named using Pascal Casing, I can use the first character of every word for compounding the corresponding alias. Also notice that 'On' statement is indented used one tab which commonly is equivalent to four white spaces. The comparison of values for the join is made following the same order of the tables, first for Student and then for StudentCourse.


If you need to write longer joins as in 'students with their corresponding payment and the courses', the query would be written as follows:

      Select *
      From Student s Join Payment p          
         On s.StudentId = p.StudentId
      Join StudentCourse sc
         On s.StudentId = sc.StudentId

 

Here I put the second join in a new line and keep the intent for the on statement. This is very useful when writing very long queries that imply joining data between several tables.

Select
In the select statement put the columns separated by comma in the same line except for those that either has an alias or is inside any function or both like this:

Select Name, LastName, Hobby,
Datepart(Year, Isnull(DoB, getDate())) Year,
Amount, Description

From Student s Join Payment p
   On s.StudentId = p.StudentId

 

Notice that in this query, the Select and the From are separated by a blank line, this is easily separate the sections of the query. This query is written in Ms Sql Server but the idea applies for other engines as well.

Sub-queries
When writing sub-queries, follow the same rules but tab each sub-query according to its level and use the parenthesis as in the following example:

Select *
From Student s Join (
    Select StudentId
    From Payment
    Where Amount > 0
) HavePayments
   On s.StudentId = Havepayments.StudentId

In and not in
Put the in or the not in at the end of the where statement, this avoid confusing when reading the query. Compare these two queries:

Select *
From Student s
Where DoB = getDate()
And Name = 'Bob'
And StudentId Not In(
    Select StudentId
    From Payment
    Where Amount > 0
)

 
Select *
From Student s
Where DoB = getDate()
And StudentId Not In(
    Select StudentId
    From Payment
    Where Amount > 0
)
And Name = 'Bob'

The first query is easier to understand because after the not in there is no more query to read so you don't have to keep in mind the rest of the predicates in the where statement.

Conclusion
This techniques help to write more semantic queries, easy to read and easy to understand. The examples given above are written in sql server but the main idea can be applied to other database engies.

Every query is different and no rules can be taken so seriously because you cannot force your query requirements fit these recommendations, but certainly you will notice the benefits of applying these techniques to your queries specially then they turn very long and complex.

Enhanced by Zemanta

No comments:

Post a Comment