SQL Best Practices: 5 Tips for Writing Queries

SQL Best Practices: How to Use SQL Effectively

Wherever there is a relational database, there is SQL or Structured Query Language. Used to interact with a database, SQL is one of the most widely used programming languages in the world.

Let’s explore some of the best practices when using SQL, from indentation to writing readable SQL queries and formatting.

At the end of the post, we are also introducing you to a powerful tool that lets you create a SQL database and create SQL queries that follow the best practices introduced here! Make sure to watch the video at the end of the post.

Practice Your SQL and Build a Data-Driven Web App

Let’s get hands-on with SQL: follow our free code-along tutorial to build a database from scratch and query it in SQL!

Code Along: Build a SQL Database and Query It
Follow our FREE step-by-step code-along tutorial

5 Best Practices for Writing SQL

Capitalization and Case Sensitivity in SQL

First, let’s speak about capitalization and case sensitivity. Most SQL dialects, such as MySQL, PostgreSQL, or MS SQL Server, don’t care whether you write SELECT, select or sELEct in your query.

But other people do care. The developers on your team or anyone else analysing your code will appreciate a well-written SQL query that follows commonly-used best practices.

To make your queries look professional, be consistent in your capitalization. SQL best practice says that:

1. Use all caps for clause starters, such as SELECT, FROM, WHERE or functions, such as SUM()
2. Column names (i.e. the things are you SELECT-ing) should be all lowercase, and
3. Table names should be in CamelCase, such as CustomerOrders or shortened to meaningful abbreviations.

So, to sum this up, here’s what your query should look like:

SELECT
  u.id AS user_ID,
  u.name AS user_name,
  u.age AS user_age
FROM
  Users u
INNER JOIN
  UserProfiles p
ON
  u.id = p.user_id;

Looks great, doesn’t it? It’s easy to read, cleanly formatted, and can easily be debugged if necessary.

While looking at this query, you might have noticed something else: our Users table was renamed as u and our UserProfiles table as p.

This is a fairly common practice in SQL, but not to everyone’s liking. On the one hand, it shortens your query and requires less typing. On the other hand, single letters are not very meaningful.

If you decide to shorten your table names, consider using descriptive acronyms rather than single letters. If the table names are quite short and well-named already, keep them as they are.

For example, a table named related_party_transactions could be shortened to rpt, which improves conciseness without sacrificing readability.

Let’s move on to quotes: what’s the SQL best practice for using quotes?

Comments in SQL

First up, we recommend to comment your SQL queries, provided your comment adds explanatory value to your code.

For example, this comment does not have explanatory value. It is rather just restating the obvious and therefore redundant.

COUNT(*) AS "NumberOfUsers" 
-- Count Number Of Users

Useful comments explain your code. For example:

-- This query retrieves the total sales amount for each product in the last quarter of the current fiscal year.

SELECT 
    ProductID,
    SUM(Amount) AS TotalSales
FROM 
    Sales
WHERE 
    OrderDate >= '2024-01-01' AND OrderDate < '2024-04-01'
GROUP BY 
    ProductID;

Only use comments when they add information or have explanatory value.

In addition, think about whether you are adding a single-line or multi-line comment. The correct syntax for each is this:

-- This is a single-line comment

/*
This is a
multi-line
comment
*/

Anything marked as a comment will be ignored by the database engine. Using two dashes — for single line comments is compatible with all SQL dialects.

Quotes in SQL: ” or “”

Most SQL dialects accept both single and double quotes to mark a text as a string. For example, 'This is a string' is as valid as "This is a string".

The only times you need to pay attention to your quotes in SQL is when you are writing a word with an apostrophe or quotation mark inside a string. For example, consider this query:

 SELECT 
   COUNTRY
 FROM
   COUNTRIES
 WHERE 
   CAPITAL = Saint John's

Note that this WHERE will not produce the correct result, because the ‘ in Saint John’s isn’t treated as an apostrophe. The correct query is:

 SELECT 
   COUNTRY
 FROM
   COUNTRIES
 WHERE 
   CAPITAL = "Saint John's"

Now the query would produce the correct result: Antigua and Barbuda (but I guess you knew that already anyway).

What’s the rule here? Generally, go with single quotes ”. Single quotes are accepted across almost all SQL dialects. Use double quotes “” when encapsulating a text that contains an apostrophe or quotation marks.

Using Aliases in SQL

Using aliases can make queries easier to read and understand, especially when dealing with complex joins or subqueries. They provide shorthand references to database tables and column names, reducing the need for verbose or repetitive syntax. This improves the clarity and conciseness of your SQL query and makes them part and parcel of SQL best practices.

Aliases are typically used to rename columns in the result set, providing more meaningful or descriptive names for presentation purposes without altering the underlying data model. Typically, snake_case is used for aliases. Note that the choice of naming convention for aliases often depends on the coding standards and preferences of the development team or organisation, and that using snake_case is not a hard rule.

Here’s an example:

SELECT 
    COUNT(customers) AS total_customers
FROM 
    customers;

Indentation

Correct indentation can greatly increase the readability of a SQL query. Here are a few helpful tips:

1. Keep the length of each line of code below 100 characters to improve readability.
2. Use indentation as a tool to highlight the logic of your code.

For example, consider this query and note how the indentation helps with our understanding of its workings:

SELECT 
    CASE 
        WHEN age < 18 THEN 'Underage'
        WHEN age >= 18 AND age < 65 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group,
    COUNT(*) AS count_of_people
FROM 
    users
GROUP BY 
    CASE 
        WHEN age < 18 THEN 'Underage'
        WHEN age >= 18 AND age < 65 THEN 'Adult'
        ELSE 'Senior'
    END;

In this query:

• The CASE expression categorizes users into different age groups (‘Underage’, ‘Adult’, ‘Senior’).
• The GROUP BY clause groups the data by the age group.
• COUNT(*) calculates the count of people in each age group.

Now, let’s re-write the query into one big chunk of code. Do you still understand it’s purpose?

SELECT CASE WHEN age < 18 THEN 'Underage' WHEN age >= 18 AND age < 65 THEN 'Adult' ELSE 'Senior' END AS age_group, COUNT(*) AS count_of_people FROM users GROUP BY CASE WHEN age < 18 THEN 'Underage' WHEN age >= 18 AND age < 65 THEN 'Adult' ELSE 'Senior' END;

SQL Tools: Applying SQL Best Practices

Following these SQL best practices can be difficult at the start. There’s a lot that database administrators or developers need to pay attention to. Luckily, some tools make this job easier.

Five, for example, offers an easy-to-use interface to create an SQL database and write SQL queries. It can even translate queries designed in point-and-click into standard SQL. Here’s how.

1. Create Your Database Visually

Five’s Database Modeler gives developers a handy tool to create, manage, and understand their database schema. The Database Modeler can be used to perform all standard SQL tasks, such as adding fields, defining data types or creating relationships.

2. Build SQL Queries Visually

Once a database has been created or added to Five, Five can be used to write SQL in two ways:

1. Through Five’s Query Designer, a point-and-click interface to build SQL queries, or
2. Through Five’s SQL Editor, a standard coding interface for SQL.

The best part is that any query that’s built inside the designer gets translated right into SQL.

Watch this video to better understand how Five can be used to write SQL queries.

SQL Best Practices: Writing Clean, Concise, and Readable SQL Queries

This sums up our guide to writing clean, concise, and readable SQL queries. By following these 5 simple SQL best practices, you can greatly improve your SQL. So, whenever you write SQL, following these standards should become second nature to you. Practice often and enjoy!

For more SQL and database knowledge, tips and tricks, and useful advice, visit our blog post on many-to-many relationships, one-to-many relationships, or surrogate vs primary keys in SQL.