SQL §

#syntax/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.