When you write SQL queries it is important to follow company policies and be consistent because others will appreciate your efforts when they work with your code. They will understand it more quickly. It will be easier on the eyes and easier to read. It will be documented and easier to understand. Here are a few best practices when writing SQL code.

Case Sensitivity

Vendors of SQL databases may use slightly different variations of SQL. These variations are called SQL dialects. Some SQL dialects are case-sensitive. BigQuery and Vertica are case-sensitive. But most, like MySQL, PostgreSQL, and SQL Server, aren’t case-sensitive. This means if you searched for country_code = ‘us’, it will return all entries that have ‘us’, ‘uS’, ‘Us’, and ‘US’. This isn’t the case with BigQuery. BigQuery is case-sensitive.

Capitalization

To follow the best practices, use upper case for clause starters (e.g., SELECT, FROM, WHERE, etc.). However, when using keywords, you can write SELECT, select, Select, and so on. Functions should also be in all caps (e.g., SUM()). Column names should be all lowercase (refer to the section on snake_case later in this guide). Table names should be in CamelCase. The only time that capitalization does matter is when it is inside quotes.

Single or double quotes

For the most part, it also doesn’t matter if you use single quotes ‘ ‘ or double quotes ” ” when referring to strings. A general rule across almost all SQL dialects is to use single quotes for strings. This helps get rid of a lot of confusion. So if we want to reference the country US in a WHERE clause (e.g., country_code = ‘US’), then use single quotes around the string ‘US’. The second situation is when your string has quotes inside it. In the WHERE clause, if the string you are looking for contains a single quote, use double quotes areound the string.

Comments

It’s good to comment your code to explain what you are trying to do. Single-line comments start with two dashes – –. The database query engine will ignore everything in the same line after – –. If you have more than two lines of comments, it might be cleaner and easier is to use /* to start the comment and */ to close the comment.

Snake_case for Column_names

Suppose you are writing a SELECT query in which you are simply renaming one of the columns. You are using the AS keyword. As another example, you are counting the number of rows in a table and you are using COUNT(*) AS number_of_rows. Snake case uses underscores to separate words and uses the small case.

CamelCase for Tables

You can also use CamelCase capitalization when naming your table. CamelCase capitalization means that you capitalize the start of each word, like a two-humped (Bactrian) camel.

Indentation

As a general rule, you want to keep the length of each line in a query relatively short for ease of readability. When writing a SELECT clause, you want to see the main parts tight to the left with the sub-parts indented. For example, you want to see the following keywords tight to the left: SELECT, FROM, WHERE, and ORDER BY. To the SQL engine, indentation doesn’t matter, but to your human readers it does matter.

SQL Text Editors

There are many SQL platforms. At this website, most of the SQL discussed is on the SQL Server platform. Be aware that not all SQL platforms provide native script editors to write SQL code. SQL text editors give you an interface where you can write your SQL queries in an easier and color-coded way. You can use a text editor like Sublime Text or Atom.