SQL

sql

SQL Best Practises

Avoid N+1 Problem Antipattern

The N+1 query antipattern happens when a query is executed for every result of a previous query.

Avoid

SELECT "cookies".* FROM "cookies"
SELECT "toppings".* FROM "toppings" WHERE "toppings"."cookie_id" = 1
SELECT "toppings".* FROM "toppings" WHERE "toppings"."cookie_id" = 2
SELECT "toppings".* FROM "toppings" WHERE "toppings"."cookie_id" = 3

Solve by fetching eagerly

SELECT "cookies".* FROM "cookies"
SELECT "toppings".* FROM "toppings" WHERE "toppings"."cookie_id" IN (1, 2, 3)

Be careful when using ORMs.

Avoid duplicate Calculations with subqueries

SELECT first_name, last_name, salary * 1.5
FROM EMPLOYEES
where (salary * 1.5) <= 3000
ORDER BY last_name;

To avoid the calculation, we introduce a subquery:

SELECT *
FROM (SELECT first_name, last_name, salary, salaray * 1.5 as calcSalary FROM EMPLOYEES)
where calcSalary <= 3000
ORDER BY last_name;

Avoid Natural Joins

Natural Joins join on a column with the same name. This can be a problem if both tables contain a column with the same name. For example, a mod_time time-stamp column would be used for joining, confusing the developer.

Prefer uncorrelated over correlated sub queries

Correlated sub queries are sub queries that rely on results from a record in the outer most query. This requires the sub query to be executed for every row of the outer most query. Unrelated sub queries are independent and thus only need to be executed once.