44. Style

Style, the conventions used to make your code more readable and consistent, varies from person to person and workplace to workplace. Over the years, I have developed a preferred style for SQL, elements of which you can see in this course. For example, when selecting multiple columns in a query, I prefer to put each column name on its own line and to use commas before the column name rather than after. I'd write this:

select
    account_id
    ,transaction_date
    ,amount
from transactions

But this is a style preference. Equally valid would be these queries:

select
    account_id,
    transaction_date
    amount,
from transactions

select account_id, transaction_date, amount from transactions

In my experience, placing each column on its own line and using commas at the start of the line makes the code more readable, especially in more complex queries. In this chapter, I will summarize style guidelines that I have found useful in my career. None of this is set in stone. You may have different preferences or opinions, or you may work somewhere with style requirements. The important thing about style is simply to develop a style purposefully, and be consistent.

Use Meaningful Table Aliases

When writing SQL queries that contain multiple tables in the from clause, you will often find yourself wanting (or needing) to alias the table names. It can be tempting to alias them with meaningless ordinal values like these examples:

select
    a.account_id
    ,a.transaction_date
    ,b.first_name
from transactions as a inner join accounts as b on b.account_id = a.account_id

select
    t1.account_id
    ,t2.city
    ,t2.state
from accounts as t1 inner join addresses as t2 on t1.account_id = t2.account_id

These table aliases are easy to confuse or forget as you're writing your query. Instead, I recommend using some type of initials or abbreviations that relate to the table name itself. For example ad for addresses, t for transactions, etc. Doing so makes it easier for you (and others!) to remember which alias relates to which table without having to go back and re-read your from clause. And as I've already recommended, you should use as when establishing table aliases, even though technically it is optional.

Clauses on New Lines

I prefer to start each clause (select, from, where, etc.) on its own line, like this:

select
    last_name
    ,count(*) as number_of_marys
from accounts
where first_name = 'Mary'
group by last_name

This makes it easier to see where one clause begins and one clause ends. Contrast the readability of the query below, and you'll see what I mean:

select last_name, count(*) as number_of_marys from accounts where first_name = 'Mary' group by last_name

This is a very short query, but if my query were selecting 30 columns, had 6 joins, and had a complex where clause, the former style would be much more readable than the latter. It's ok to write short, one-off queries on a single line. But as soon as you have the inkling that you'll need to use this query again in the future, or that it's so long that it's wrapping to a new line to fit on your screen, stop what you're doing and reformat it. You'll be happy you did.

Use Indentation

Throughout this course, you may have noticed that the SQL I have written includes tab indentation between the various clauses of my queries. This spacing makes it visually clear which lines of code are part of each clause of my query, like in this example:

select
    account_id
    ,fund
    ,count(*) as number_of_fund_gifts
from transactions
where transaction_date >= '2020-01-01'
    and fund = 'Trees'
group by account_id
    ,fund
having count(*) > 1

It is easy to visually scan this query and immediately understand which lines belong to which clauses.

Match Casing to Your Database

Generally, there are two types of casing used in database table and field names:

• snake_case - where words are lower case and underscores are used to represent spaces between words.
• PascalCase - where each word is upper case and there are no characters in place of the space.

If your workplace does not already have standards for which type of casing to use, the fields and tables in your database likely already adhere to one of these two types of casing. Use the casing style that is in use by your database.