10 Best Practices in Writing SQL Queries

Have you ever found your queries taking much longer than expected? Often, we focus on how our database works when querying, but we overlook the load we generate. I’ve had IT departments approach me asking why I heavily load the database and whether the data I retrieved was genuinely necessary.

In today’s post, you’ll find several points that will help you create more optimized queries. This will result in:

  • Better code readability,
  • Reduced server load and, consequently, time.

#1 Avoid using SELECT * in your queries

Instead of *, choose specific columns from the set that you need. Rarely do we need to retrieve all columns, and listing them in the code will make it easier for other users to understand our query.

#2 Avoid using DISTINCT

The SELECT DISTINCT syntax is most commonly used to select unique records from the database. Instead of using distinct, consider selecting multiple columns that lead to unique values.

#3 Avoid using UNION on large tables

The UNION ALL function combines two tables. Instead, try the UNION function, which not only merges your data but also removes duplicates resulting from the union of two tables.

#4 Use JOINs instead of joining tables with WHERE

Almost all types of databases support JOINs. Most databases create a temporary table when using the WHERE function to join data, adding an extra burden to our server.

#5 Use WHERE for non-aggregatable column filtering instead of HAVING

Using the HAVING function requires grouping data, an additional step that the server must take to process our query. The WHERE function allows filtering non-aggregatable columns without additional grouping.

#6 Avoid using special characters at the beginning of an expression (Wildcards)

Searching for, e.g., ‘%abc%’, causes the server to scan the entire table for a matching text string since it is valid from the beginning (i.e., each row can start with any character). Instead, try using ‘abc%’ in your query.

#7 Avoid using functions on the left side of the operator when filtering WHERE

Filtering a column specified by a function is resource-intensive for the server. If a function is applied to a column, the server won’t use the index imposed on it. It’s better to filter the column using the function on the right side of the operator, e.g.,

WRONG:

WHERE TO_CHAR(sales_date, ‘YYYYMMDD’) = ‘20200703’

RIGHT:

WHERE Sales_date = CAST(‘20200703' as date)

#8 Avoid concatenating columns in the WHERE clause

Try not to use CONCATENATE functions on two columns to create a filtering key. Instead, list two conditions in the WHERE function, e.g.,

WHERE Filter1=x
AND Filter2=y

#9 Avoid using calculated fields in WHERE filters and JOINs

Do not use calculations on fields that are subject to filters or joins. Try to filter and join specific columns. Joining based on calculated fields consumes many server resources (each record in the table must be recalculated and checked for filtering/connection possibilities).

#10 Consider the proper order in the GROUP BY clause

Your GROUP BY query might be a bit faster if you arrange the columns in the correct grouping order.

Interesting article? Share further!

Leave A Comment