It’s a Matter of Style

W riting SQL can be very enjoyable activity. Reading SQL can be also enjoyable (maybe like reading poetry to some), or very unpleasant… How do you write SQL with style that results in eye pleasing and easy to read/understand code? And does it matter?
    Sometimes code writing drills down to concentrating on the task at hand and producing a brilliant piece of code, which looks like this:

Listing 1

select c.customer_name, o.order_amt,
d.qty from customers c left outer join
orders o on c.customer_nbr = o.customer_nbr
left outer join orderdetails d on d.order_nbr =
o.order_nbr and d.sku = 101

Or maybe like this:

Listing 2

SELECT C.CUSTOMER_NAME, O.ORDER_AMT,
D.QTY FROM CUSTOMERS C LEFT OUTER JOIN
ORDERS O ON C.CUSTOMER_NBR = O.CUSTOMER_NBR
LEFT OUTER JOIN ORDERDETAILS D ON D.ORDER_NBR =
O.ORDER_NBR AND D.SKU = 101

While this code performs exceptionally and solves the problem in a very clever way, is it really that good? What happens when the code review/test team gets their turn? Or when you/someone else has to modify it two years from now? To my opinion this code is a very long way from what a real production code should be. And yes, this is very real and it happens every day, even as I type this. Just pay attention on the next code review, or take a look at any online SQL forum (and no, it is not only the people that ask questions, unfortunately  many SQL gurus that know it all would post an answer with similar “example” style).
    How do you make this code look better? The answer is in the four basic principles of design: contrast, repetition, alignment, and proximity. Let’s look how applying these principles of design (which many think are applicable only to graphic design) can lead to stylish and enjoyable code.

Contrast
The idea is to use contrast for elements that a very different. One example is columns and reserved keyword. They are not the same and the code should make that distinction very clear. Let’s apply that:

Listing 3

SELECT C.customer_name...

Here the SELECT keyword is capitalized to differentiate from the lower case column name. Also, the table alias is capitalized to indicate clearly the table source.

Repetition
Repeating the same element styles for all similar items adds consistency and organization throughout code. For example, repeat and maintain capitalization for all keyword, do not mix style in different context of the code. Like the style of the SELECT and FROM reserved keywords in Listing 4.

Listing 4

SELECT C.customer_name... FROM Customers AS C...

This allows to “visualize” the shape of the query code. Now the eye can easily flow from one section of code to the next one and concentrate on each element.

Alignment
Code elements should not be placed arbitrarily on the lines of code. Every code item should have some visual connection with another item in the code.  One example is aligning the start of each clause of the query (SELECT, FROM, WHERE, etc.) on a new line:

Listing 5

SELECT C.customer_name...
FROM Customers AS C...

Alignment creates a clean and pleasing look of the code structure.

Proximity
Code items that relate to each other should be grouped close together. When several items are in close proximity they become one visual unit. Like placing SELECT and column names together on the line, similar for FROM and table names or WHERE and predicates. Listing 6 demonstrates this.

Listing 6

SELECT C.customer_name, O.order_amt...
FROM Customers AS C
LEFT OUTER JOIN Orders AS O...

This makes the code structure very clear and eliminates clutter.

Let’s apply all four principles to the initial query. Here is one way it may look:

Listing 7

SELECT C.customer_name, O.order_amt, D.qty
FROM Customers AS C
LEFT OUTER JOIN Orders AS O
  ON C.customer_nbr = O.customer_nbr
LEFT OUTER JOIN OrderDetails AS D
  ON D.order_nbr = O.order_nbr
 AND D.sku = 101;

I added a couple extra styling elements (compared to the original query), can you catch them?

Another form of alignment is this:

Listing 8

         SELECT C.customer_name, O.order_amt, D.qty
           FROM Customers AS C
LEFT OUTER JOIN Orders AS O
             ON C.customer_nbr = O.customer_nbr
LEFT OUTER JOIN OrderDetails AS D
             ON D.order_nbr = O.order_nbr
            AND D.sku = 101;

There are many different ways to style your SQL. You may agree or disagree with some elements, but the bottom line is this: style matters!

Labels: ,