Nous avions vu dans un précédent article comment gérer MySQL en ligne de commandes. Nous avons donc appris à créer, modifier, supprimer des tables et des bases. Nous avons aussi vu comment lire, écrire, effacer et trier des données dans ces tables. Ces opérations constituent le plus gros du travail que l’on a à effectuer dans des bases SQL.

Cependant, nous avons parfois à gérer des données complexes, dont l’organisation nécessite des traitements plus poussés. Nous allons donc parler d’index, de clefs primaires, de clefs étrangères ou encore de recherches FULLTEXT. Autant de méthodes qui vont nous aider à organiser et gérer des schémas de données complexes.

Index de colonnes

Les index servent à accélérer les requêtes et sont indispensables pour créer des clefs, que nous verrons par la suite. Lorsqu’un index est créé, le moteur SQL stocke cet index dans une structure de données séparée. Cette structure de données contient les valeurs triées spécifiquement pour cette donnée. De cette manière le moteur SQL est en mesure de localiser très efficacement la donnée recherchée.

Les index permettent donc d’accélérer les requêtes qui font usage de critères de recherche, tel que le WHERE.

En revanche, les index prennent de la place en mémoire et ralentissent les INSERT et UPDATE puisqu’il faut écrire l’index à chaque fois. Il faut alors choisir judicieusement les colonnes à indexer et privilégier les colonnes sur lesquelles on fera souvent des recherches par exemple.

Avant d’entrer dans le détails des index et d’en créer, sachez qu’il est bien entendu possible de les afficher :

SHOW INDEX FROM Facebook;
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Facebook |          0 | PRIMARY     |            1 | id          | A         |         378 |     NULL | NULL   |      | BTREE      |         |               |
| Facebook |          1 | ind_user_id |            1 | user_id     | A         |         378 |     NULL | NULL   |      | BTREE      |         |               |
| Facebook |          1 | ind_fb_id   |            1 | fb_id       | A         |         378 |     NULL | NULL   |      | BTREE      |         |               |
| Facebook |          1 | ind_fb_mail |            1 | mail        | A         |         378 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

À ce propos, il est possible de faire un index sur plusieurs colonnes à la fois, et on peut se servir de cet index même si on ne fait pas une recherche sur toutes les colonnes indexées. C’est ce qu’on appelle l’index par la gauche.

Vous avez une table membre :

• nom
• prenom
• pseudo

Vous faites un index sur les trois à la fois. L’index triera par ordre les noms, puis les prénoms, puis les pseudos. Donc, si vous voulez faire une recherche sur les noms uniquement, l’ordre sera exactement le même que si on avait créé un index uniquement sur les noms.

De même si on fait une recherche sur nom et prénom, l’ordre ne bouge toujours pas comparé à un index sur ces deux colonnes. En revanche, si on veut faire une recherche sur pseudo uniquement ou prénom uniquement, ça ne marchera pas.

En effet, les pseudos sont ordonnés par les noms et les prénoms qui les précèdent dans les colonnes de gauches, avant d’être trié eux-même alphabétiquement. C’est pour cette raison que ça s’appelle “par la gauche”, ça marche tant que l’on effectue la recherche sur toutes les colonnes qui sont à gauches. Dans le cas contraire, il faut créer un autre index.

Plusieurs types d’INDEX

UNIQUE

Comme son nom l’indique, il fait en sorte qu’il ne puisse y avoir qu’une seule fois une valeur dans la table. C’est par exemple l’index que nous utilisons dans la PRIMARY KEY.

FULLTEXT

Cet index permet de faire des recherche puissantes sur du contenu texte. Ce type d’index ne fonctionne que sur les champs de type TEXT, CHAR et VARCHAR (sinon ce n’est plus du texte !). On verra ensuite ce qu’il permet de faire.

Note : Il n’est pas possible d’utiliser la technique “d’index par la gauche” avec le FULLTEXT. De plus, cet index n’est disponible qu’avec le moteur myisam.

SPATIAL

Cet index est un peu spécifique puisqu’il concerne les données spatiales. Si vous avez besoin de plus d’infos, RTFM ;)

Création des index

CREATE TABLE nom_table (
    colonne1 description_colonne1,
    [colonne2 description_colonne2,
    colonne3 description_colonne3,
    ...,]
    [PRIMARY KEY (colonne_clé_primaire)],
    [{INDEX|KEY} [nom_index] (colonne1_index [, colonne2_index, ...]],
    [UNIQUE [INDEX|KEY] [nom_index] (colonne1_index [, colonne2_index, ...]]
)
[ENGINE=moteur];

Exemple :

CREATE TABLE ma_table (
    id SMALLINT NOT NULL AUTO_INCREMENT,
    nom char(255) NOT NULL,
    email VARCHAR(100),
    INDEX ind_nom (nom(25)),
    UNIQUE INDEX ind_email (email)
)
ENGINE=INNODB;

On peut aussi créer les index directement dans les colonnes. Il faudra utiliser le mot KEY, ou UNIQUE si l’on veut un index unique. Cette syntaxe est plus limité puisqu’on ne peut définir des index que sur une seule colonne à la fois, ni spécifier une limite de taille dans l’index (x premiers caractères d’un char par ex.).

CREATE TABLE nom_table (
    colonne1 INT KEY, -- Crée un index simple sur colonne1
    colonne2 VARCHAR(40) UNIQUE, -- Crée un index unique sur colonne2
);

Ajout après la création de la table

Parfois, on ne pense pas à tout, tout de suite, et on voudrait ajouter un index sur une table déjà existante. ALTER TABLE vous connaissez ?

ALTER TABLE nom_table
ADD INDEX [nom_index] (colonne_index [, colonne2_index ...]);

Il y a une autre commande, qui permet de faire la même chose : CREATE INDEX.

CREATE [UNIQUE | FULLTEXT] INDEX nom_index
ON nom_table (colonne_index [, colonne2_index ...]);

Personnellement, j’aime moins cette manière de faire car il faut retenir une commande supplémentaire alors que ALTER TABLE le fait très bien. En plus vous verrez par la suite que CREATE INDEX permet moins de choses qu’ALTER TABLE, comme par exemple la suppression de l’index :

ALTER TABLE nom_table
DROP INDEX nom_index;

Facile n’est-ce pas ?!

Insertion ou mise à jour

Les index unique vous empêchent d’insérer une donnée en double. Si une telle insertion est tentée, une erreur est levée. Ils ouvrent toutefois une nouvelle possibilité : l’UPSERT, comprendre UPDATE ou INSERT.

Cette commande permet de n’insérer une donnée que si elle n’existe pas déjà. Dans le cas contraire, on peut choisir de ne rien faire ou de mettre à jour la donnée.

Dans les exemples suivant, nous considérons qu’il y a un index unique sur telephone.

INSERT INTO contacts (telephone, firstname, lastname)
VALUES ('0912345678', 'Antoine', 'Bettinger')
ON DUPLICATE KEY UPDATE id = id;

Dans cet exemple, rien ne change, nous évitons simplement la levée d’une erreur. Nous spécifions en effet, qu’en cas de valeur dupliquée, l’id de l’enregistrement sera égal à l’id, donc aucune modification n’est faite.

Nous pouvons bien entendu définir des valeurs à modifier.

INSERT INTO contacts (telephone, firstname, lastname)
VALUES ('0912345678', 'Antoine', 'Bettinger')
ON DUPLICATE KEY UPDATE telephone = '0667862356', lastname = 'Lalane';

Ici, dans le cas où la valeur n’existe pas, elle est créé, mais si elle existe, les colonnes telephone et lastname sont mises à jour avec des valeurs de notre choix.

Enfin, on peut effectuer les mises à jour de manière dynamique. Cela se fait grâce au mot-clef VALUE (VALUES avant MariaDB 10.3.3). Avec ce mot-clef, la valeur de VALUE(column) équivaut à la valeur telle qu’elle aurait été insérée. En revanche, si vous spécifiez column = column, alors il s’agit de la valeur actuelle. Comme dans le premier exemple, cette possibilité permet de définir une expression après UPDATE.

INSERT INTO contacts (telephone, firstname, lastname)
VALUES ('0912345678', 'Antoine', 'Bettinger')
ON DUPLICATE KEY UPDATE firstname = VALUE(firstname), lastname = VALUE(lastname);

Cette fois ci, si le numéro de téléphone existe déjà, alors on met simplement à jour le nom et le prénom du contact.

La recherche FULLTEXT

Le FULLTEXT est très puissant pour la recherche. Il découpe les chaines de caractères en mots pour les analyser un par un. Un mot est une suite de caractères sans espace (oh ?!) ou deux mots séparés par un underscore “_” ou par une apostrophe. Les mots composés séparés par des traits d’union sont considérés comme deux mots distincts.

De plus, pour faire ces recherches, FULLTEXT tient compte des règles suivantes :

• un mot qui figure dans 50% des lignes est ignoré (par pertinent),
• un mot de moins de quatre lettre est ignoré,
• les mots anglais trop communs sont ignorés. Vous êtes français, pas de chance ! Mais, il est néanmoins possible de spécifier les mots à ignorer.

La recherche FULLTEXT se fait comme ceci :

SELECT colonnes_indexes_en_fulltext
FROM ma_table
WHERE MATCH (colonel [, 2si_je_veux, …])
AGAINST ('la chaine que je cherche');

Il y a 3 types de recherches FULLTEXT :

• La recherche IN NATURAL LANGAGE MODE. C’est celle par défaut.
• La recherche IN BOOLEAN MODE. Cette recherche ignore la règle des 50% abordée plus haut, et elle permet d’opérer des recherches sur des colonnes qui n’ont pas été indexées (cependant, attention au temps d’exécution !). Dernier point avec le boolean mode, il n’y a pas de tri par pertinence dans les résultats.
• La recherche WITH QUERY EXTENSION. C’est une recherche étendue. Ce type de recherche fait en réalité deux recherches en une requête. Elle soumet d’abord la chaine recherchée à une recherche de type NATURAL puis elle en lance une seconde avec comme paramètres, non plus la chaine que vous cherchiez, mais les résultats de la première recherche. Voilà pourquoi c’est une recherche étendue.

Pour préciser le type de recherche :

SELECT colonnes_indexes_en_fulltext
FROM ma_table
WHERE MATCH (colonel [, 2si_je_veux, …])
AGAINST ('la chaine que je cherche' [WITH QUERY EXPANSION | IN BOOLEAN MODE | IN NATURAL LANGUAGE MODE]);

Vous l’aurez compris, les index FULLTEXT ont un grand intérêt lorsqu’il s’agit d’opérer des recherches sur la table. Malheureusement, ce type d’index n’est présent que sur les tables MyISAM. Et vous allez voir par la suite qu’InnoDB possède de nombreux atouts que MyISAM n’a pas. Bref, il faudra faire un choix.

Cefs Primaires

Les clefs sont des contraintes. Nous connaissons les clefs primaires, que nous appliquons en général à la colonne id de nos tables. Une clef primaire ou PRIMARY KEY est en fait un INDEX NOT NULL, elle est donc la somme de deux contraintes. Au cas où vous voudriez mettre un index sur une colonne PRIMARY KEY, c’est inutile puisque KEY est synonyme d’index. Une clef primaire est donc un index.

Cette clef permet de s’assurer que :

• La (ou les) colonne concernée ne contient pas deux fois la même valeur,
• Chaque ligne possède une valeur (NOT NULL).

Pour la créer, je pense que vous savez faire (on l’a déjà vu plusieurs fois) :

CREATE TABLE ma_table (
    id SMALLINT NOT NULL AUTO_INCREMENT,
    nom char(255) NOT NULL,
    PRIMARY KEY (id)
)
ENGINE= MyISAM | INNODB;

On peut bien entendu le préciser directement dans la colonne. Je ne vous remontre pas comment faire. De plus, on peut créer la clé primaire dans un second temps, avec ALTER TABLE, chose qu’on ne peut pas faire avec CREATE INDEX (je vous l’avais bien dit !).

ALTER TABLE ma_table
ADD PRIMARY KEY (id);

Idem pour la supprimer… enfin vous savez faire.

Clefs Étrangères

Les clefs étrangères permettent de croiser les données de plusieurs tables en s’assurant de la bonne correspondance de celles-ci. Je m’explique : vous avez deux tables, la première contient la liste de tous vos films, la seconde la liste des catégories dans lesquelles sont classés les films. Pourquoi ne met-on pas tout dans une seule table ?

Admettons qu’il y ait cinq catégories : thrillers, comédie, animation, documentaires, famille. Si vous répétez ça par le nombre de films que vous avez (disons 500) ça vous fait un paquet de données redondantes à enregistrer, et autant de place perdue.

Vous allez donc mettre ces 5 catégories dans une autre table et placerez l’id correspondant au format dans la table des films. Le but des clefs étrangères est donc que si vous avez 5 formats et que leurs identifiants sont respectivement 1, 2, 3, 4 et 5, elles vous éviteront d’avoir un id 7, qui ne correspondrait à rien. C’est un contrôle d’intégrité en somme.

2, 3 trucs à savoir :

• Pour le moteur de table, ce sera obligatoirement INNODB, MyISAM ne supporte pas les clefs étrangères,
• On peut créer ces clefs sur plusieurs colonnes,
• Dans ce cas, on doit avoir le même nombre de colonnes dans la référence et dans la clef (logique…),
• La création d’une clef ajoute automatiquement un index, on a vu que key et index sont plus ou moins synonymes,
• En revanche, la colonne qui sert de référence (donc pas celle qui reçoit la clef) doit prendre un index,
• La colonne de référence et la clef doivent être de même type.

Maintenant que vous savez ça, you’re good to go ! On doit d’abord créer la table qui sert de référence, sinon MySQL lèvera une erreur et ne pourra pas créer la table qui reçoit les clefs étrangères. Comment voulez vous créer une clef étrangère sur quelque chose qui n’existe pas ?!

CREATE TABLE Categories
(
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
)
ENGINE = INNODB;


-- puis la table films
CREATE TABLE Films
(
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    category SMALLINT UNSIGNED,
    PRIMARY KEY (id),
    CONSTRAINT fk_cats
        FOREIGN KEY (category)
        REFERENCES Categories(id)
)
ENGINE=INNODB;

Le [CONSTRAINT [ma_clef_etrangere]] est utile si on veut définir le nom de la clef. Bien qu’on puisse aussi faire CONSTRAINT FOREIGN KEY sans préciser de nom, il est toujours utile de nommer ses clefs étrangères pour pouvoir les désigner plus tard afin de les modifiers ou de les supprimer.

Comme toujours, on peut l’ajouter après la création de la table :

ALTER TABLE nom_table
ADD CONSTRAINT fk_cats FOREIGN KEY (category) REFERENCES Categories(id);


-- Et pour la supprimer :
ALTER TABLE nom_table
DROP FOREIGN KEY symbole_contrainte

Les options des clefs étrangères

Les clefs étrangères permettent de maintenir une certaine cohérence au sein de nos tables. Il ne faudrait donc pas que l’on puisse supprimer ou modifier une table de référence sans quoi toutes les clefs étrangères n’auraient plus de sens. D’ailleurs, si vous tentez de supprimer une référence alors alors que dans d’autres tables, des clefs lui sont liées, ça lèvera une erreur. Il est justement possible de définir le comportement par défaut pour ce genre de cas.

Nous avons 4 options à notre disposition :

• RESTRICT : comportement par défaut, empêche la suppression ou la modification,
• NO ACTION : similaire à RESTRICT,
• SET NULL : met NULL comme valeur aux champs dont la référence est supprimée,
• CASCADE : supprime les lignes dont la référence est supprimée.

Dans notre exemple précédent. Si je mets un SET NULL à la clef étrangère category, si je supprime la catégorie thrillers dans ma tables Categories, tous les films qui avaient pour catégorie thrillers, auront dorénavant NULL comme catégorie. En revanche, si le réglage était fait sur CASCADE, tous les films ayant pour catégorie thrillers, auraient été supprimés de la table.

ALTER TABLE nom_table
ADD [CONSTRAINT nom_clef_etrangere]
    FOREIGN KEY (colonne)
    REFERENCES table_ref(col_ref)
    ON DELETE | UPDATE {RESTRICT | NO ACTION | SET NULL | CASCADE};

Problème de FOREIGN KEY

On rencontre parfois des problèmes lors de la suppression où de la mise à jour de colonnes dans une table, à cause des clef étrangères. Il est souvent très instructif d’afficher le statut du moteur de table (INNODB) SHOW ENGINE INNODB STATUS et de jeter un œil à la section des dernières erreurs concernant les FOREIGN KEYS (LATEST FOREIGN KEY ERROR).

SHOW ENGINE INNODB STATUS
------------------------
LATEST FOREIGN KEY ERROR
------------------------
141215 22:11:15 Error in foreign key constraint of table buzut/#sql-6b09_41276:
 FOREIGN KEY (articles_id) REFERENCES Articles(id) ON DELETE SET NULL:
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.

Les jointures

Les jointures permettent de rassembler deux tables (ou plus). On a vu avec les clefs étrangères qu’on peut vouloir stocker certaines info dans une seconde table. Si l’on veut récupérer ces infos comme si elles faisaient partie d’une seule et même table, on utilisera les jointures !

Il y a deux sortes de jointures, les internes et les externes. Les jointures internes retournent les résultats pour les colonnes qui ont une correspondance dans chacune des tables, tandis que les jointures externes retournent toutes les colonnes.

Dans notre exemple des catégories, si nous n’avons aucun film qui a pour catégorie vacances (disons qu’on en a rajouté), avec une jointure interne, cette catégorie ne sera pas affichée, car aucun film ne lui correspond. Avec une jointure externe en revanche, on retrouvera vacances dans notre tableau.

Jointure interne

SELECT nom_table.nom_colonne
FROM nom_table
[INNER] JOIN nom_2eme_table
ON colonne1 = colonne2
WHERE ce_quil_te_faut;

Pour le SELECT, on n’est pas obligé de préciser le nom de la table, sauf si celui-ci existe dans les deux tables. Il est ici judicieux de mettre un alias. La jointure est par défaut interne, il est donc inutile de spécifier INNER. ON précise les critères à remplir pour effectuer la jointure, ce sera le plus souvent =, mais les autres opérateurs sont valables aussi.

Si on veut par exemple récupérer tous les films correspondant à la catégorie thrillers :

SELECT title
FROM Films
JOIN Categories
ON category = Categories.id
WHERE Categories.name = 'thrillers';
+--------------------+
| title              |
+--------------------+
| The punisher       |
| Le pacte des loups |
| LIMITLESS          |
| Film down          |
| RAPT               |
| Secret Defense     |
| Michel Vaillant    |
| Largo Winch        |
| Wonderland         |
| Laffaire Farewell  |
| Constantine        |
| Cloverfield        |
+--------------------+
12 rows in set (0.00 sec)

Jointure externe

Il faut savoir que pour les jointure externe il y a deux modes. La jointure par la droite et la jointure par la gauche. C’est à dire que si on fait une jointure par la gauche, nous voulons toutes les données de la table de gauche, même si elle n’a pas de correspondance dans la table de droite, et vice versa.

Quelle est la droite, quelle est la gauche. Easy as abc, baby ! On lit de gauche à droite, donc la table de gauche est la première à être mentionnée. Comme tout ceci n’est pas bien clair, on va prendre un exemple. Disons que je veux afficher tous mes films avec leur catégories correspondantes. Le nom des catégories pas leur id bien entendu… On va donc faire une jointure par la gauche pour afficher aussi les films qui n’ont pas de catégorie.

SELECT title, Categories.name
FROM Films
LEFT JOIN Categories
ON Films.category = Categories.id;
+-------------------------------------------+------------------+
| title                                     | name             |
+-------------------------------------------+------------------+
| The punisher                              | Thrillers        |
| Le pacte des loups                        | Thrillers        |
| LIMITLESS                                 | Thrillers        |
| Paul                                      | NULL             |
| Film down                                 | Thrillers        |
| RAPT                                      | Thrillers        |
| Secret Defense                            | Thrillers        |
| Michel Vaillant                           | Thrillers        |
| Largo Winch                               | Thrillers        |
| Wonderland                                | Thrillers        |
| Laffaire Farewell                         | Thrillers        |
| Constantine                               | Thrillers        |
| Yan Arthus Bertrand                       | NULL             |
| Cloverfield                               | Thrillers        |
| Wild Wild West                            | NULL             |
| GRAN TORINO                               | NULL             |
| Scrubs S03 E10                            | Scrubs           |
| Scrubs S03 E06                            | Scrubs           |
| Scrubs S03 E09                            | Scrubs           |
| Scrubs S03 E08                            | Scrubs           |
+-------------------------------------------+------------------+
21 rows in set (0.01 sec)

Une jointure interne ne nous aurait ici pas retourné les films n’ayant pas de catégorie. De la même manière, nous pourrions afficher toutes les catégories. Je pense que vous avez compris le principe. C’est exactement la même chose pour les jointures par la droite donc je ne redonne pas d’exemple.

Syntaxes alternatives

Il est possible d’omettre la clause JOIN pour les jointures internes, dans ce cas, la clause ON est remplacée par WHERE. Cependant, il devient difficile de faire la différence entre les conditions de jointure (normalement précisées dans le ON) et les conditions normales (WHERE). À vous de voir…

SELECT *
FROM films, formats
WHERE films.formats = formats.id

Une pratique qui simplifie la vie et qui cette fois peut-être utilisée sans problème, le NATURAL JOIN. Cette jointure interne évite de préciser la clause ON si les champs des deux tables sur lesquelles on veux opérer la jointure portent le même nom.

SELECT *
FROM table
NATURAL JOIN table_de_jointure

Sous requêtes

Ce sont en quelques sortes des requêtes imbriquées. Elle permettent de faire en une requête ce qui qu’il nous aurait fallu faire en plusieurs. Parmi mes films, je voudrais sélectionner le plus récent des thrillers :

SELECT title
FROM (
    SELECT max(Films.id), title
    FROM Films
    JOIN Categories
    ON category = Categories.id
    WHERE Categories.name = 'thrillers'
)
AS newest_film;

Il est évident que les parenthèse servent à différencier la sous-requête de la requête principale, sans elles, vous aurez donc une erreur. Il est aussi obligatoire de nommer un alias pour la requête intermédiaire, sans cela, une erreur sera levée aussi.

Comparaisons

Il est possible d’utiliser d’autres clauses que le FROM dans les sous-requêtes, cependant, il faut que la sous requête renvoie soit une seule valeur (donc une seule ligne et une seule colonne), soit une seule ligne, soit une seule colonne.

Les opérateurs logiques

Dans le cas où la réponse retournée par la sous-requête est une unique valeur, on peut utiliser tous les opérateurs logiques habituels en lieu et place du from (<, >, =…).

Si la réponse retourne une ligne, on peut utiliser les opérateurs = et !=.

IN et NOT IN

Ces opérateurs signifient “se trouve” ou “ne se trouve pas” dans la sous requête. Ils sont utilisable avec les requêtes qui renvoient une valeur ou une colonne.

ANY, SOME et ALL

ANY signifie “au moins une des valeurs” tandis que ALL signifie “toutes les valeurs”. SOME quant à lui est synonyme de ANY. Ces opérateurs sont spécifiques aux sous-requêtes, vous ne les trouverez pas ailleurs dans MySQL. Ils s’appliquent dans les mêmes conditions que IN et NOT IN, c’est à dire lorsque la requête renvoie une unique valeur ou une colonne.

Je n’ai pas d’exemple à vous donner concernant les comparaisons, cependant, ils s’utilisent de la même manière qu’avec le FROM.

Corrélation de requêtes

Un peu comme avec les jointures, on peut faire référence dans une sous requête à une table qui n’est pas définie dans sa clause FROM. Je vais donner un petit exemple. La requête que nous allons faire ne rime pas à grand chose, mais c’est pas grave. On va sélectionner tous les films dont les id correspondent à l’id d’une catégorie. Je sais c’est un peu WTF, mais si je vous disais un peu plus haut ne pas avoir d’exemple, ce n’est pas pour rien !

SELECT id, title, category
FROM Films
WHERE category IN (
    SELECT id
    FROM Categories
    WHERE Categories.id = Films.id
);
+----+-------------------------------------------+----------+
| id | title                                     | category |
+----+-------------------------------------------+----------+
|  1 | Qu'est-ce que c'est être étudiant ?       |        1 |
|  2 | VTT pedralta                              |        2 |
| 10 | Futurama S01 E03                          |       10 |
+----+-------------------------------------------+----------+
3 rows in set (0.00 sec)

Attention cependant, ce fonctionnement marche en remontant d’un ou plusieurs niveau. Ce qui veut dire qu’une sous sous requête pour aller chercher la table correspondante deux niveaux au-dessus (ou plus), mais pas dans une autre sous requête de même niveau ou de niveau inférieur.

UNION

UNION permet de rassembler plusieurs SELECT dans une même requête :

SELECT *
FROM table1
UNION
SELECT *
FROM table2

Les requêtes doivent avoir le même nombre de colonnes, il faut aussi que les colonnes correspondantes soient de même type (bien que MySQL ne lève pas d’erreur pour ça, c’est bien pour la cohérence).

UNION élimine automatiquement les doublons. Si vous voulez les afficher, il faut faire UNION ALL.

Les cas LIMIT et ORDER BY

Il est possible d’opérer un LIMIT sur une requête ou sur l’ensemble des deux requêtes :

-- Ne sélectionne que les 3 premiers résultats de la première requête
SELECT *
FROM table1
LIMIT 3
UNION
SELECT *
FROM table2;

-- Ne sélectionne que les 3 premiers résultats de la totalité des requêtes
SELECT *
FROM table1
UNION
SELECT *
FROM table2
LIMIT 3;

Pour faire un LIMIT sur la seconde (ou plus) requête, utilisez les parenthèses :

SELECT *
FROM table1
UNION
(SELECT *
FROM table2
LIMIT 3);

Pour le ORDER BY, il n’est possible de l’utiliser que pour le résultat d’ensemble, pas pour des requêtes individuelles.

SELECT *
FROM table1
UNION
SELECT *
FROM table2
ORDER BY ID

Sauf, dans le cas où on lui adjoint un LIMIT !

SELECT *
FROM table1
ORDER BY ID DESC
LIMIT 8
UNION
SELECT *
FROM table2