Performance and fast response of the web and mobile applications largely depend on the quality of writing SQL queries. For most of the junior developers with whom I have worked SQL queries are weak points. So I decided to write a post about best practices for writing SQL queries, which good SQL developers uses every day.

Good practices

Obviously, a list of these practices are much larger, but I decided to gather in this post the most used queries and not overload with rare cases.

Define all the necessary fields for SELECT instead of using SELECT *

As it seems to me, this is the most common mistake to get all available columns using *. With a large number of records and rows in the table, defining all the necessary fields will greatly speed up your query.

Bad practice

SELECT *
FROM users;

Good practice

SELECT name, description, address
FROM users;

Use WHERE to filter records instead of HAVING

HAVING should only be used on filtering on an aggregated field. If the goal is to filter records based on the condition, then a better solution than WHERE cannot be found.

Bad practice

SELECT name
FROM users
GROUP BY name
HAVING age > 25

Good practice

SELECT name
WHERE age > 25
FROM users;

Select more fields rather than use DISTINCT

SELECT DISTINCT query is performed using the grouping of all fields in the query, which is significantly more resource-intensive than using the usual SELECT.

Bad practice

SELECT DISTINCT name, age
FROM users

Good practice

SELECT name, age, address
FROM users

Use INNER JOIN instead of WHERE

If the query optimizer is doing its job right, there should be no difference between those queries. But INNER JOIN makes query more readable.

Bad practice

SELECT users.name, address.street
FROM users.id = address.user_id

Good practice

SELECT users.name, address.street
FROM users
    INNER JOIN address
    ON users.id = address.user_id

Use IN and EXIST in the right situations

IN is efficient when most of the filter criteria is in the subquery.

EXISTS is efficient when most of the filter criteria is in the main query.

Bad practice

Select * from users u
where id IN 
(select user_id from address)

Good practice

Select * from users u 
where EXISTS (select * from address a
where a.user_id = u.user_id)

Summary

Best practices for writing SQL queries:

  • Define all the necessary fields for SELECT instead of using SELECT *
  • Use WHERE to filter records instead of HAVING
  • Select more fields rather than use DISTINCT
  • Use INNER JOIN instead of WHERE
  • Use IN and EXIST in the right situations