Structured Query Language (SQL) is a simple, yet powerful database programming and administration language used to manipulate data stored in a relational database or relational database management system (RDBMS). It is easy to learn compared to a lot of traditional programming languages. While that is great for both new database developers and veterans alike, it can lead to issues; since learning the database language is so simple, programmers can often overlook best practices and just dive right in.
To avoid this issue, this database programming tutorial will cover the best practices for writing SQL code and querying relational databases.
Looking to learn SQL or database development in an online course? We have a list of the Best Online Courses to Learn SQL to help get you started.
Best Practices for Structure Query Language
Below is a list of best practices for database programmers and database administrators to follow when querying relational databases with SQL.
SQL Keywords Should Use Uppercase
Perhaps one of the most basic best practices for SQL is to always ensure that you are using uppercase letters for your SQL keywords. This can be a bone of contention among db admins, as different flavors of SQL will have different casing for code; in general, however, the core SQL keyword commands should always use uppercase, such as:
SELECT FirstName, LastName FROM Customers;
or
SELECT * from Customers;
versus writing it:
--THIS IS WRONG select FirstName, LastName from Customers;
While some debate the matter, using uppercase for SQL functions is also a best practice – if nothing else, for consistency.
Comment Your SQL Code
One of the most important best practices in any programming language is to always add comments to your code. This is true of your SQL statements as well. Commenting is a way to leave notes to yourself – or other developers – explaining what a particular line or block of code was meant for.
You should only comment your SQL code when a statement is not self-explanatory or its intent is not readily apparent. Adding comments to code whose purpose is obvious is redundant and can lead tl clutter in your codebase – something you want to avoid. Try to create a good balance and only comment when it makes sense to do so.
Here is an example of how to comment in SQL:
-- This is a comment SELECT * FROM Customers;
Single-line – or inline – comments in SQL begin with two — symbols. When the SQL interpreter sees the — symbols, it ignores the remaining code on that line.
You can also leave more than one line of comments. There are two ways to achieve this in SQL. The first involves simply leaving multiple single-line comments, as show in this code example:
--This is a comment --This is also a comment SELECT * FROM Customers;
Another way to leave more than one comment is to use multi-line commenting. In multi-line comments, you start the comment by using the opening /* symbol. You then right your comments and close the multi-line comment using the closing */ symbol. Here is an example of how to write multi-line comments in SQL:
/*This is the start of a multi-line comment This is a continuation of our multi-line comment SQL does not read this line or any other line Until we close the comment. */ SELECT * FROM Customers;
The SELECT Asterisk Problem
In our SQL examples above, you may have noticed that we use the SELECT * statement. If you are unfamiliar, SELECT lets you choose data from a given table. If we add the wildcard asterisk character (*), then all of the information in a table will be chosen or processed. This becomes a problem in Big Data scenarios or where large amounts of data exist in a table. Instead of selecting every column in a table, instead, choose only the columns you need to query. For instance, if you only need the First Name from our Customers table example, you would use:
SELECT FirstName FROM Customers;
That isn’t to say you shouldn’t ever use SELECT * – just make sure you only use it in scenarios where you actually need all of the data in a table.
Format Queries
Another SQL best practice is to format the text in yoru queries. This means that yuo want to place each clause on its own line. This makes your code easier to read and less prone to mistakes. For example, instead of writing:
SELECT FirstName, LastName, FROM Customers WHERE FirstName = 'Ronnie';
You would want to format the query this:
SELECT FirstName, LastName FROM Customers WHERE FirstName = 'Ronnie';
This makes understanding the SQL easier and finding errors much simpler.
Final Thoughts on SQL Best Practices
There are many best practices for writing clean, efficient, and maintainable SQL statements; this database programming tutorial only scratches the surface. We will update this listing from time to time, so be certain to check back often to learn more tips for writing quality SQL.
