1. Convey your intent
Welcome to the final chapter of this course. Unlike the previous three chapters, which have focused on SQL tools and techniques. This chapter will focus on the application of best practices for writing SQL scripts.
In this lesson, you will learn new strategies to ensure that your SQL scripts are easy to read.
2. Why is this important?
You may be thinking, "if my code does what I want it to do, who cares how its written".
3. ...six months from now
... but what happens if you need to understand your code six months from now.
I can't speak for your experience, but on many occasions I've had to spend more time understanding my old code than it probably took to write it.
From these experiences I've learned that the best way to avoid this is to write code where the intent of what it should do is clear to any reader, especially the future me.
4. Always use AS
One of the easiest way to convey your intent is to simply use the AS command. This command is used to assign an alias to a table or a column and it is by no means required. As you can see in the example here, the column title is renamed to film_title. But, look closely, if you don't know that film_title doesn't exist in the table and don't notice the absence of a comma you may think you're returning two columns.
Instead, if you add the AS command, you make it abundantly clear to whoever is reading your code that you are renaming a column.
5. What kind of JOIN?
Another example of this is JOIN command.
By default, using JOIN will execute an INNER JOIN, and, while that may be a nice shorthand, by explicitly writing INNER JOIN the code makes it abundantly clear what type of JOIN is being performed and doesn't rely on the reader to know this specific SQL fact.
6. Good use of aliases
Conveying your intent can also take other forms. For instance, the use of clearly named aliases.
In this code, aliases are required in order to join the two tables, however, you may have to review your code several times to understand which tables x1 and x2 are referring to. As you can imagine this can be exponentially more challenging to read as the number of tables you use grows.
Instead, you can better convey the intent of your code by either using a single letter abbreviation corresponding to the original table name.
7. Good use of aliases
Or by using a shorthand alias.
Whether the abbreviation is one letter or more really depends on your style and the underlying code. In fact, there isn't really a consensus on whether aliases should be single letters or whole words, but it is clear that either one is still better than arbitrary alias names.
8. Use comments
In the ideal case, every table you're using is well named and the steps you need to take are so clear that the code is easy to read and has no special considerations. In the real world, there are tricks that have to be used and data problems that need to be dealt with. And all of this logic and the thought behind it can easily get lost.
In these scenarios, the use of comments is invaluable. It is the cheat code that let's you simply switch from code to regular text. SQL has two ways in which you can add comments to code;
first, is a multi-line comment which must start with a forward slash and star and end with the inverse.
second, is a single-line comment, which simply must be preceded by two hyphens
When these are used judiciously they can be a powerful asset for conveying your intent.
9. What was your intent?
Now you will review what you have learned in the following exercises.