Jointures de tables (1) — INNER JOIN et alias

Les données utilisées dans cet article — department et employee

Jusqu'ici tu as travaillé avec une seule table à la fois, mais les données du monde réel sont réparties sur plusieurs tables. Les noms et salaires des employés vivent dans la table employee, les noms et lieux des départements vivent dans la table department — la conception habituelle répartit les données par rôle et les relie avec employee.dept_id qui pointe vers department (cette « colonne qui pointe vers une ligne d'une autre table » s'appelle une clé étrangère).

Dans cet article tu vas apprendre le INNER JOIN, la forme la plus basique d'un JOIN qui combine deux tables séparées en un seul résultat. Le matériel est la table department (6 départements) et la table employee (30 employés). À travers un exercice qui construit une liste affichant le nom du département de chaque employé, tu vas essayer tour à tour les alias de table, ON et USING.

Avant de te lancer dans les exercices, jette un œil aux définitions de colonnes et aux données d'exemple des deux tables utilisées dans cet article — department et employee.

① Exécute PRAGMA table_info(department); et PRAGMA table_info(employee); pour vérifier les définitions de colonnes des deux tables.

② Exécute SELECT * FROM department LIMIT 5; et SELECT * FROM employee LIMIT 5; pour prévisualiser les 5 premières lignes. Remarque que certaines lignes de employee ont NULL dans la colonne dept_id.

INNER JOIN — relier deux tables avec une condition de jointure

La table employee ne contient que dept_id (le numéro de département). Quand tu veux que la liste des employés affiche aussi le nom du département, tu prends les lignes où employee.dept_id et department.dept_id coïncident, tu les alignes côte à côte et tu les fusionnes en une seule ligne. C'est un JOIN, et le plus basique est le INNER JOIN (INNER = jointure interne).

La forme est SELECT colonnes FROM table_gauche JOIN table_droite ON condition_jointure. JOIN est le raccourci de INNER JOIN — les deux se comportent à l'identique. Dans ON tu écris la condition de jointure, qui dit « si telle colonne coïncide avec telle colonne, traite ces lignes comme la même ligne ». INNER JOIN ne garde que les lignes qui ont coïncidé dans les deux tables ; les lignes sans contrepartie d'un côté disparaissent du résultat.

-- Afficher dept_name à côté de chaque employé (INNER JOIN)
SELECT employee.name, employee.salary, department.dept_name
FROM employee
JOIN department
  ON employee.dept_id = department.dept_id;

Imagine une exigence : « construire une table pour la liste des employés avec le numéro d'employé, le nom et le nom du département auquel ils appartiennent ». (Exécute-la correctement et l'explication apparaîtra.)

① INNER JOIN la table employee et la table department. La condition de jointure est que dept_id coïncide dans les deux tables.

② Récupère trois colonnes : emp_id et name depuis employee, et dept_name depuis department.

③ Trie le résultat par emp_id croissant.

Alias de table — écrire court les noms de table longs

Écrire le nom de la table à chaque fois, comme dans employee.name ou department.dept_name, rend les requêtes longues. Si tu places un alias après le nom de la table, comme dans FROM employee e, tu peux ensuite y faire référence avec un nom court comme e.name. L'écrire avec AS, comme FROM employee AS e, signifie la même chose.

Les alias deviennent aussi nécessaires pour une auto-jointure (utiliser la même table deux fois, abordée dans le prochain article) et pour nommer un résultat dans une sous-requête. Dans cet article on écrira employee comme e et department comme d.

-- Alias employee en e et department en d
SELECT e.name, e.city, d.dept_name, d.location
FROM employee e
JOIN department d
  ON e.dept_id = d.dept_id;

Imagine une exigence : « afficher uniquement les noms des employés du département Engineering, ainsi que le lieu de ce département ».

① INNER JOIN employee avec l'alias e et department avec l'alias d. La condition de jointure est que dept_id coïncide.

② Restreins aux lignes où dept_name est Engineering.

③ Récupère deux colonnes — name depuis e et location depuis d — triées par name croissant.

USING — un raccourci quand les noms de colonnes de jointure coïncident

employee et department sont joints sur le même nom de colonne, `dept_id`, dans les deux tables. Quand le nom de la colonne utilisée pour la jointure est exactement le même dans les deux tables comme ici, tu peux écrire USING (dept_id) au lieu de ON e.dept_id = d.dept_id.

USING (nom_colonne) signifie la même chose que ON gauche.nom_colonne = droite.nom_colonne, et c'est plus court car tu n'écris la colonne de jointure qu'une seule fois. De plus, une colonne jointe avec USING apparaît une seule fois dans le résultat, et tu peux la référencer directement avec SELECT dept_id (sans nom de table). Quand les noms de colonnes diffèrent (par exemple e.dept_id et d.id), tu ne peux pas utiliser USING — tu utilises ON.

-- Écrire le même sens que ON e.dept_id = d.dept_id avec USING
SELECT e.name, dept_id, d.dept_name
FROM employee e
JOIN department d
  USING (dept_id);

Imagine une exigence : « parmi tous les employés, extraire le top 5 par salaire et lister leur nom, le nom du département et le salaire ».

① Joins employee (alias e) et department (alias d) avec la clause `USING`. La colonne de jointure est dept_id, commune aux deux tables.

② Récupère trois colonnes : name depuis e, dept_name depuis d et salary depuis e.

③ Trie par salary décroissant et restreins aux 5 premières lignes.

INNER JOIN supprime les lignes qui n'existent que d'un seul côté

INNER JOIN retourne uniquement les lignes où la condition de jointure a coïncidé dans les deux tables. Les 4 personnes dont dept_id est NULL (Ivan, Quinn, Xander, Brian) et le département Legal sans aucun employé n'ont pas de partenaire de jointure, donc elles n'apparaissent pas dans le résultat. « Je veux tous les employés mais seulement 26 lignes reviennent » est, dans la plupart des cas, causé par ce comportement. Quand tu veux garder aussi les lignes qui n'existent que d'un seul côté, utilise le OUTER JOIN abordé dans le prochain article.