SQL is the language of the database. Many developers have a tendency to allow the visual tool that comes with the database to automatically write the SQL statements for your application. Many web developers even use the rudimentary SQL building tools of Dreamweaver MX to create the SQL for a web page. These tools are not bad, but frequently the SQL becomes hard to read because it has not been formatted or optimized.

Writing a readable SQL statement can be an art in itself. This article will show you a few techniques for creating readable SQL. This will help you read your own SQL statements to make sense out of them, and will allow others to read your SQL and understand it.

Case

The first thing you should concern yourself with is the alphabetic case of your statements. There are several ways to approach using case in your SQL statements, but the main thing to be concerned with is consistency. Take a look at this statement as an example:

select dbo.categories.categoryname, dbo.categories.description, bo.products.productname, dbo.suppliers.companyname, dbo.products.quantityperunit,
dbo.products.unitprice from dbo.categories inner join
dbo.products on dbo.categories.categoryid = dbo.products.categoryid inner join
dbo.suppliers on dbo.products.supplierid = dbo.suppliers.supplierid

Everything is in lower case which makes it hard to read. The rules I like to follow are these:

• SQL keywords in UPPERCASE
• Table names, column names, and column aliases in mixed case (upper/lower)
• Table Aliases and db users in lower case

Using these rules we can change the SQL to look like this:

SELECT dbo.Categories.CategoryName, dbo.Categories.Description, dbo.Products.ProductName, dbo.Suppliers.CompanyName, dbo.Products.QuantityPerUnit,
dbo.Products.UnitPrice
FROM dbo.Categories INNER JOIN
dbo.Products ON dbo.Categories.CategoryId = dbo.Products.CategoryId INNER JOIN
dbo.Suppliers ON dbo.Products.SupplierId = dbo.Suppliers.SupplierId

Using standardized case makes the statement more readable by making the keywords stand out.

Text formatting

Another way to increase the readability of your SQL code is to format the text in a standardized way. There are several ways to do this, but again, the most important thing is consistency. The first, most common, method is to put major keywords on new lines, separate the field names on new lines, and keep joins together on the same line:

SELECT dbo.Categories.CategoryName,
dbo.Categories.Description,
dbo.Products.ProductName,
dbo.Suppliers.CompanyName,
dbo.Products.QuantityPerUnit,
dbo.Products.UnitPrice
FROM dbo.Categories
INNER JOIN dbo.Products
ON dbo.Categories.CategoryId = dbo.Products.CategoryId
INNER JOIN dbo.Suppliers
ON dbo.Products.SupplierId = dbo.Suppliers.SupplierId

To make this even more readable, you can use indentation and spacing:

SELECT dbo.Categories.CategoryName,
dbo.Categories.Description,
dbo.Products.ProductName,
dbo.Suppliers.CompanyName,
dbo.Products.QuantityPerUnit,
dbo.Products.UnitPrice
FROM dbo.Categories

  INNER JOIN dbo.Products
    ON dbo.Categories.CategoryId = dbo.Products.CategoryId

  INNER JOIN dbo.Suppliers
    ON dbo.Products.SupplierId = dbo.Suppliers.SupplierId

I prefer to use a preceding comma style of separating field names:

SELECT dbo.Categories.CategoryName
,dbo.Categories.Description
,dbo.Products.ProductName
,dbo.Suppliers.CompanyName
,dbo.Products.QuantityPerUnit
,dbo.Products.UnitPrice
FROM dbo.Categories

  INNER JOIN dbo.Products
    ON dbo.Categories.CategoryId = dbo.Products.CategoryId

  INNER JOIN dbo.Suppliers
    ON dbo.Products.SupplierId = dbo.Suppliers.SupplierId

This allows me to comment out fields or sections of code easily during debugging in the SQL Query Analyzer or within a stored procedure:

SELECT dbo.Categories.CategoryName
,dbo.Categories.Description
/*,dbo.Products.ProductName
,dbo.Suppliers.CompanyName */
,dbo.Products.QuantityPerUnit
--,dbo.Products.UnitPrice
FROM dbo.Categories

  INNER JOIN dbo.Products
    ON dbo.Categories.CategoryId = dbo.Products.CategoryId

  INNER JOIN dbo.Suppliers
    ON dbo.Products.SupplierId = dbo.Suppliers.SupplierId

Aliases

The last and most important way to improve your code readability in SQL is to use table aliases. Most RDBMS systems allow the use of a table alias in place of a table name. To use an alias, simply put an arbitrary name after your table reference in the SQL. This should be in your FROM or JOIN statement, as in this example:

SELECT * FROM Products p

Here I've named the table p. In a simple statement you don't need to use aliases, but in a more complex statement the alias gives you a much more readable statement. It is also usually a shorter statement as a result. The statement we have been using as an example would look like this:

SELECT c.CategoryName,
c.Description,
p.ProductName,
s.CompanyName,
p.QuantityPerUnit,
p.UnitPrice
FROM dbo.Categories c

  INNER JOIN dbo.Products p
    ON c.CategoryId = p.CategoryId

  INNER JOIN dbo.Suppliers s
    ON p.SupplierId = s.SupplierId

Here we've given the tables the following aliase names:

• Products p
• Categories c
• Suppliers s

The code is about a third smaller, but more importantly, it is easier to read because it doesn't have all the extraneous table names. I generally use the first letter of a table for the alias name just to keep the statement as short as possible, but if you have more than one table that begins with the same letter you can use two or three letters or come up with your own naming scheme.

Using the preceding comma formatting method, the statement would look like this:

SELECT c.CategoryName
,c.Description
,p.ProductName
,s.CompanyName
,p.QuantityPerUnit
,p.UnitPrice
FROM dbo.Categories c

  INNER JOIN dbo.Products p
    ON c.CategoryId = p.CategoryId

  INNER JOIN dbo.Suppliers s
    ON p.SupplierId = s.SupplierId

Conclusion

Writing readable SQL is one of the first steps in learning the SQL language, yet is one of the most overlooked areas of web development. Hopefully this article will get you thinking about how you can improve the readability of your own SQL statements.