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.
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.
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.
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.
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.
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.
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
Full-stack web developer. I enjoy writing Php, Java, and Js.