Réponse à la question

Historique de la question

La division relationnelle a été formellement définie par Edgar F. Codd en 1970 comme l'inverse du produit cartésien, conçue pour exprimer la quantification universelle (∀) en algèbre relationnelle. Bien que SQL ANSI mette en œuvre la quantification existentielle (∃) naturellement à travers des clauses WHERE et des jointures, il lui manque un opérateur de division natif, ce qui oblige les développeurs à simuler cette opération théorique par des stratégies de négation logique ou de comptage. Ce modèle apparaît constamment dans la conformité réglementaire, les matrices d'autorisation et les systèmes de suivi de compétences où l'identification des "ensembles complets" est cruciale.

Le problème

Étant donné une table dividende EmployeeTraining(employee_id, module_id) et une table diviseur RequiredModules(module_id), l'objectif est de renvoyer chaque employee_id associé à toutes les lignes dans le diviseur. Le défi transcende les simples jointures, qui trouvent n'importe quelle correspondance ; la division nécessite de vérifier la couverture totale. Il est crucial que la solution gère les enregistrements de complétion en double, les ensembles d'exigences vides (vérité vacuole), et s'exécute efficacement sans logique procédurale.

La solution

L'approche canonique SQL ANSI utilise la double négation : sélectionner les employés pour lesquels il n'existe pas de module requis qu'ils n'ont pas complété. Cela se traduit par des clauses imbriquées NOT EXISTS. Alternativement, une méthode de comptage compare les complétions distinctes au total requis, bien qu'elle nécessite une manipulation soignée des doublons.

-- Double Négation : Division Relationnelle Pure
SELECT DISTINCT e.employee_id
FROM EmployeeTraining e
WHERE NOT EXISTS (
    SELECT 1
    FROM RequiredModules r
    WHERE NOT EXISTS (
        SELECT 1
        FROM EmployeeTraining e2
        WHERE e2.employee_id = e.employee_id
          AND e2.module_id = r.module_id
    )
);

-- Méthode de Comptage (avec gestion des doublons)
SELECT employee_id
FROM (
    SELECT e.employee_id, 
           COUNT(DISTINCT e.module_id) AS completed_count
    FROM EmployeeTraining e
    JOIN RequiredModules r ON e.module_id = r.module_id
    GROUP BY e.employee_id
) sub
WHERE completed_count = (SELECT COUNT(*) FROM RequiredModules);

Situation de la vie

Une entreprise de maintenance aéronautique devait certifier des mécaniciens pour la réparation de moteurs. La FAA a exigé l'achèvement de cinq modules de sécurité spécifiques suivis dans Mechanic_Completions, mais les mécaniciens retentaient souvent des modules échoués, créant des lignes en double. Faire cette vérification quotidiennement pour 1 200 mécaniciens contre 200 modules possibles nécessitait une requête qui ignorait les doublons et gérait des scénarios d'audit où la liste des exigences pouvait temporairement être vide.

Solution 1 : GROUP BY avec COUNT(DISTINCT) Cette approche a joint les tables, regroupé par mécanicien et comparé les décomptes distincts. L'avantage principal était la lisibilité ; les développeurs junior comprenaient immédiatement la logique. Cependant, elle a souffert d'une dégradation significative des performances en raison de l'opération DISTINCT sur 2 millions d'enregistrements historiques. Plus critique encore, sans gestion explicite de COALESCE, elle renvoyait zéro mécaniciens lorsque la table RequiredModules était vide (mode audit), violant le principe mathématique selon lequel la quantification universelle sur un ensemble vide est vacuole vraie pour tous les éléments.

Solution 2 : Double Négation avec NOT EXISTS Cette méthode utilisait deux clauses imbriquées NOT EXISTS pour vérifier les modules manquants. Elle gérait naturellement les enregistrements de complétion en double parce qu'elle vérifiait uniquement l'existence (comportement de semi-jointure) plutôt que de compter les occurrences. Elle renvoyait correctement tous les mécaniciens lorsque l'ensemble de exigences était vide. L'inconvénient concernait des plans d'exécution plus complexes ; les optimisateurs choisissaient parfois des jointures par boucle imbriquée par rapport aux jointures de hachage, bien qu'un bon index sur module_id atténuait cela.

Solution Choisie et Résultat L'équipe a choisi l'approche de la double négation car les règles d'intégrité des données autorisaient les entrées de complétion en double, rendant la méthode de comptage risquée sans coûteuses opérations DISTINCT. La requête a identifié 847 mécaniciens entièrement certifiés sur 1 200 en moins de 150 ms. Lors d'un audit réglementaire subséquent où toutes les exigences étaient temporairement suspendues, la requête a correctement identifié tous les 1 200 mécaniciens comme conformes (vérité vacuole), empêchant un atterrissage inutile de la main-d'œuvre tout en maintenant la correction logique.

Ce que les candidats négligent souvent

Comment la requête se comporte-t-elle lorsque la table RequiredModules contient zéro lignes, et pourquoi cela a-t-il de l'importance mathématiquement ?

Lorsque le diviseur est vide, la division relationnelle doit renvoyer l'ensemble complet du dividende (tous les employés) car la vérité vacuole dicte que chaque élément satisfait "pour tous les éléments dans l'ensemble vide." La méthode de double négation y parvient naturellement ; comme aucun module requis n'existe, la clause NOT EXISTS intérieure ne trouve jamais un module manquant, donc la clause extérieure n'exclut personne. En revanche, la méthode de comptage completed_count = (SELECT COUNT(*) FROM RequiredModules) équivaut à zéro, ne renvoyant que les mécaniciens avec zéro complétions. Les candidats doivent implémenter un wrapper COALESCE ou une logique CASE pour renvoyer toutes les lignes lorsque le diviseur est vide, ou utiliser le modèle de double négation qui gère ce cas particulier implicitement.

Pourquoi la méthode de comptage avec COUNT(*) au lieu de COUNT(DISTINCT module_id) produit-elle de faux positifs, et comment les doublons affectent-ils l'approche de la double négation ?

Si un mécanicien complète le Module A deux fois (échec initial, puis reprise), COUNT(*) renvoie 2. Avec seulement les Modules A et B requis, un mécanicien manquant B mais avec deux enregistrements A affiche un décompte de 2, satisfaisant faussement le test d'égalité. Cela crée des lacunes critiques de conformité. Les candidats omettent souvent DISTINCT, supposant que les contraintes de clés étrangères empêchent les doublons. La méthode de double négation vérifie uniquement l'existence (SELECT 1), ce qui la rend immunisée contre les lignes en double dans la table de dividende ; si une association existe, le module est satisfait. Comprendre cette distinction est crucial pour des environnements de données sans contraintes d'unicité parfaites.

Quelle est la différence entre la division relationnelle exacte et la division avec reste, et comment modifieriez-vous la requête pour trouver les employés qui ont complété exactement les modules requis sans extras ?

Les solutions ci-dessus mettent en œuvre "la division avec reste" (division lâche), renvoyant des employés avec au moins les modules requis (soupersets). La division exacte exige que l'employé possède aucuns modules supplémentaires au-delà de ceux requis. Pour y parvenir, les candidats doivent ajouter une condition de filtrage garantissant que le total des modules distincts du mécanicien égale le nombre requis : HAVING COUNT(DISTINCT module_id) = (SELECT COUNT(*) FROM RequiredModules). De nombreux candidats supposent incorrectement que la division relationnelle implique "exactement ceux-ci et seulement ceux-ci," ce qui conduit à des bugs d'autorisation où les employés avec des certifications expirées ou inappropriées sont approuvés à tort pour des tâches sensibles.