SQL bits & bytes-Best Practices
These pertain to general SQL programming.
I have not covered Intermediate and Advanced SQL best practices; those are for another day.
1. Order of keywords
The order of keywords in the given below sequence is an absolute must, else the query will not execute.
a. SELECT
b. FROM
c. WHERE
d. GROUP BY
e. HAVING
f. ORDER
g. LIMIT
2. Indentation & New Lines
Below three queries are the same, they will give the same result, the difference between the three in indentation. I personally prefer the second one as I find it easiest to read. Indentation increases readability.
SELECT emp_name FROM employees WHERE emp_id=101;
Vs
SELECT emp_name
FROM employees
WHERE emp_id=101;
Vs
SELECT
emp_name
FROM
employees
WHERE
emp_id=101;
3. Aliases (AS)
Aliases allow us to give a temporary new name to a column, during the query’s execution. The best part is that it does not affect the underlying data in any way. The column header is changed for display purposes ONLY. Aliases make the query more understandable.
SELECT department_id as Dept_id
FROM employees
SELECT salary*12 as Yearly_Salary
FROM employees
4. Comments
Commenting the query is highly recommended in any language. It makes your code more readable and help you and other developers understand it better in the future.
— (Single Line Comments)
/*
*/ (Multi Line Comments)
5. GROUP BY & ORDER BY <column name>
We know that column names and column numbers can both be used GROUP BY and ORDER BY clauses and both will work fine, as shown in the example below. But best practice is, to use column names, and the reason is that the query may be altered in the future and query will order by the wrong column (if you forget to change the column number after updating the query.)
SELECT max(salary), department_id, last_name
FROM employees
GROUP BY department_id
ORDER BY 3;
6. Use of LIMIT
LIMIT clause basically solves the purpose of restricting the query result. When I query any table for the first time, I normally run the following:
DESC <table_name> (to get the column names/type)
SELECT *
FROM <table_name>
LIMIT 5;
This is typically, to get the feel of column values. I use LIMIT so that efficiency is not compromised.
Use of SELECT * from <table-name> is usually not a good practice but one-time usage with LIMIT is often acceptable. LIMIT clause is not available with all database systems.
7. Keywords as column/table names
Table and column names should not be same as SQL Keywords. KEYWORDS are reserved words in SQL and are used to perform various operations (defining, manipulating, accessing) in the database, i.e., SELECT, FROM, WHERE, DESC, EXISTS etc.
Column & Table names should be different from the reserved SQL words. Column name should not be DESC.
8. Snake vs Camel case formatting convention
Snake case refers to the style of writing in which each space is replaced by an underscore (_) character, and the first letter of each word written in lowercase. It is a commonly used naming convention in computing i.e., employee_id, last_name.
Camel case is the practice of writing phrases without spaces or punctuation, indicating the separation of words with a single capitalized letter, and the first word starting with either case. Camel case is also often used as a naming convention in computer programming, but is an ambiguous definition due to the optional capitalization of the first letter i.e., EmployeeId, lastName.
Generally speaking, SQL variables are written using Snake case. In other words, only lowercase letters, numbers and underscores are used for table/column/schema names.
