I hate to be tied down with a lot of standards when it comes to developing software. I like to keep things down to a minimum allowing individual creativity and preferences. At the same time, there are a few things I have found which tend to provide value, regardless of the person writing or maintaining the software. Here are a few things I like to see in SQL syntax.
When you have a select statement retrieving data from more than one table, any column contained only in one table does not require full qualification. In my opinion, when you have more than one table, there is much value added by fully qualifying each column name in the select statement. For individuals not familiar with your database schema, or with the code where you have used CTEs or inline views, fully qualifying remove a ambiguity of your intention in your query.
Here’s an example:
SELECT
po.PurchaseOrderId,
OrderDate,
DetailId,
ItemId,
Quantity,
Amount
FROM PurchaseOrders po
JOIN PurchaseOrderItems poi on po.PurchaseOrderId = poi. PurchaseOrderId
Reading this query you can pretty well determine from what table each un-qualified column is derived. The column named Amount, however, could come from either table. It probably comes from PurchaseOrderItems, but you can’t tell by looking at the query. You will have to run the query to see what the data is to know for sure.
Here is how I prefer to write this kind of query:
SELECT
po.PurchaseOrderId,
po.OrderDate,
poi.DetailId,
poi.ItemId,
poi.Quantity,
poi.Amount
FROM PurchaseOrders po
JOIN PurchaseOrderItems poi on po.PurchaseOrderId = poi. PurchaseOrderId
Using this syntax didn’t take me a lot of time. There is no confusion as to where data is found, and you don’t have to run the query to determine what is going on. This is a simple thing, but I have found it can save a lot more time that it takes to follow this technique.
I have read some blogs where people don’t like using aliases for tables in their queries. I can see how that removes all ambiguity. But I don’t find as much value in that practice unless the query runs on for dozens of lines of code. Often I will try to break a query down with CTEs when it starts to get that complicated. Again, that’s pretty much a matter of preference.
Do you have any syntax preferences? Our comments are a great place for you to share your experience.
Cheers,
Ben