Astuce SQL pour remplacer les sous-requêtes

Ceci intéressera ceux qui travaillent avec les bases de données. Voici une petite astuce pour créer une requête SQL qui permet de compiler des statistiques avec des fonctions d'agrégations (COUNT, SUM, AVG, etc), sans utiliser de sous-requêtes.

Si on imagine la requête SQL suivante qui comptabilise le nombre d'entreprises par province dont la première lettre est A, B ou C, on pourrait écrire l'énoncé SQL suivant :

-- Syntaxe PostgreSQL
SELECT province, COUNT(*) as total,
(
  SELECT COUNT(*)
  FROM companies as c2
  WHERE substr(company_name, 1, 1) = 'A'
  AND c1.province = c2.province
) as total_starting_with_a,
(
  SELECT COUNT(*)
  FROM companies as c2
  WHERE substr(company_name, 1, 1) = 'B'
  AND c1.province = c2.province
) as total_starting_with_b,
(
  SELECT COUNT(*)
  FROM companies as c2
  WHERE substr(company_name, 1, 1) = 'C'
  AND c1.province = c2.province
) as total_starting_with_c
FROM companies as c1
GROUP BY province
ORDER BY province

-- Syntaxe SQL Server
SELECT province, COUNT(*) as total,
(
  SELECT COUNT(*)
  FROM companies as c2
  WHERE left(company_name, 1) = 'A'
  AND c1.province = c2.province
) as total_starting_with_a,
(
  SELECT COUNT(*)
  FROM companies as c2
  WHERE left(company_name, 1) = 'B'
  AND c1.province = c2.province
) as total_starting_with_b,
(
  SELECT COUNT(*)
  FROM companies as c2
  WHERE left(company_name, 1) = 'C'
  AND c1.province = c2.province
) as total_starting_with_c
FROM companies as c1
GROUP BY province
ORDER BY province

Cependant, on pourrait aussi bien l'écrire en éliminant les sous-requêtes, en utilisant simplement une somme selon une condition spécifiée à l'intérieur par un CASE WHEN. Si la condition est respectée, on additionne 1, sinon 0, ce qui revient à l'équivalent d'un COUNT.

-- Syntaxe PostgreSQL
SELECT province, COUNT(*) as total,
SUM(CASE WHEN substr(company_name, 1, 1) = 'A' THEN 1 ELSE 0 END) as starting_with_a,
SUM(CASE WHEN substr(company_name, 1, 1) = 'B' THEN 1 ELSE 0 END) as starting_with_b,
SUM(CASE WHEN substr(company_name, 1, 1) = 'C' THEN 1 ELSE 0 END) as starting_with_c
FROM companies as c1
GROUP BY province
ORDER BY province

-- Syntaxe SQL Server
SELECT province, COUNT(*) as total,
SUM(CASE WHEN left(company_name, 1) = 'A' THEN 1 ELSE 0 END) as starting_with_a,
SUM(CASE WHEN left(company_name, 1) = 'B' THEN 1 ELSE 0 END) as starting_with_b,
SUM(CASE WHEN left(company_name, 1) = 'C' THEN 1 ELSE 0 END) as starting_with_c
FROM companies as c1
GROUP BY province
ORDER BY province

Question performance, j'ai noté que sur 2 millions d'enregistrements, le modèle à sous-requête prenait environ 13 secondes à s'exécuter tandis que celui qui n'en utilisait pas nécessitait un peu moins de 4 secondes.