MySQL
Joint

Syntaxe

• INNER et OUTER sont ignorés.

• FULL n'est pas implémenté dans MySQL.

• "commajoin" ( FROM a,b WHERE ax=by ) est mal vu; utilisez FROM a JOIN b ON ax=by place.

• FROM a JOIN b ON ax = by inclut les lignes qui correspondent dans les deux tables.

• FROM a LEFT JOIN b ON ax = by inclut toutes les lignes de a , plus les données correspondantes de b ou NULLs s'il n'y a pas de ligne correspondante.

Exemples de jointure

Requête pour créer une table sur la base de données

CREATE TABLE `user` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`course` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `course` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Puisque nous utilisons les tables InnoDB et que nous savons que user.course et course.id sont liés, nous pouvons spécifier une relation de clé étrangère:

ALTER TABLE `user`
ADD CONSTRAINT `FK_course`
FOREIGN KEY (`course`) REFERENCES `course` (`id`)
ON UPDATE CASCADE;

Rejoindre Query (Inner Join)

SELECT user.name, course.name
FROM `user`
INNER JOIN `course` on user.course = course.id;

JOIN avec sous-requête (table "Derived")

SELECT x, ...
    FROM ( SELECT y, ... FROM ... ) AS a
    JOIN tbl  ON tbl.x = a.y
    WHERE ...

Cela évaluera la sous-requête dans une table temporaire, puis JOIN ce à tbl .

Avant 5.6, il ne pouvait y avoir d'index sur la table temporaire. Donc, c'était potentiellement très inefficace:

SELECT ...
    FROM ( SELECT y, ... FROM ... ) AS a
    JOIN ( SELECT x, ... FROM ... ) AS b  ON b.x = a.y
    WHERE ...

Avec 5.6, l'optimiseur calcule le meilleur index et le crée à la volée. (Cela a un peu de frais, alors ce n'est pas encore parfait.)

Un autre paradigme commun est d'avoir une sous-requête pour initialiser quelque chose:

SELECT 
        @n := @n + 1,
        ...
    FROM ( SELECT @n := 0 ) AS initialize
    JOIN the_real_table
    ORDER BY ...

(Remarque: il s’agit techniquement d’un produit CROSS JOIN (produit cartésien), comme l’indique l’absence de la valeur ON . Toutefois, elle est efficace car la sous-requête ne renvoie qu’une ligne qui doit correspondre aux n lignes de the_real_table .)

Récupérer les clients avec les commandes - variations sur un thème

Cela permettra d'obtenir toutes les commandes pour tous les clients:

SELECT c.CustomerName, o.OrderID
    FROM Customers AS c
    INNER JOIN Orders AS o
        ON c.CustomerID = o.CustomerID
    ORDER BY c.CustomerName, o.OrderID;

Cela comptera le nombre de commandes pour chaque client:

SELECT c.CustomerName, COUNT(*) AS 'Order Count'
    FROM Customers AS c
    INNER JOIN Orders AS o
        ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerID;
    ORDER BY c.CustomerName;

Aussi, compte, mais probablement plus vite:

SELECT  c.CustomerName,
        ( SELECT COUNT(*) FROM Orders WHERE CustomerID = c.CustomerID ) AS 'Order Count'
    FROM Customers AS c
    ORDER BY c.CustomerName;

N'indiquez que le client avec les commandes.

SELECT  c.CustomerName,
    FROM Customers AS c
    WHERE EXISTS ( SELECT * FROM Orders WHERE CustomerID = c.CustomerID )
    ORDER BY c.CustomerName;

Full Outer Join

MySQL ne prend pas en charge FULL OUTER JOIN , mais il existe des moyens pour en émuler un.

Configuration des données

-- ----------------------------
-- Table structure for `owners`
-- ----------------------------
DROP TABLE IF EXISTS `owners`;
CREATE TABLE `owners` (
`owner_id` int(11) NOT NULL AUTO_INCREMENT,
`owner` varchar(30) DEFAULT NULL,
PRIMARY KEY (`owner_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of owners
-- ----------------------------
INSERT INTO `owners` VALUES ('1', 'Ben');
INSERT INTO `owners` VALUES ('2', 'Jim');
INSERT INTO `owners` VALUES ('3', 'Harry');
INSERT INTO `owners` VALUES ('6', 'John');
INSERT INTO `owners` VALUES ('9', 'Ellie');
-- ----------------------------
-- Table structure for `tools`
-- ----------------------------
DROP TABLE IF EXISTS `tools`;
CREATE TABLE `tools` (
`tool_id` int(11) NOT NULL AUTO_INCREMENT,
`tool` varchar(30) DEFAULT NULL,
`owner_id` int(11) DEFAULT NULL,
PRIMARY KEY (`tool_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of tools
-- ----------------------------
INSERT INTO `tools` VALUES ('1', 'Hammer', '9');
INSERT INTO `tools` VALUES ('2', 'Pliers', '1');
INSERT INTO `tools` VALUES ('3', 'Knife', '1');
INSERT INTO `tools` VALUES ('4', 'Chisel', '2');
INSERT INTO `tools` VALUES ('5', 'Hacksaw', '1');
INSERT INTO `tools` VALUES ('6', 'Level', null);
INSERT INTO `tools` VALUES ('7', 'Wrench', null);
INSERT INTO `tools` VALUES ('8', 'Tape Measure', '9');
INSERT INTO `tools` VALUES ('9', 'Screwdriver', null);
INSERT INTO `tools` VALUES ('10', 'Clamp', null);

Que voulons-nous voir?

Nous voulons obtenir une liste dans laquelle nous voyons qui possède quels outils et quels outils peuvent ne pas avoir de propriétaire.

Les requêtes

Pour ce faire, nous pouvons combiner deux requêtes en utilisant UNION . Dans cette première requête, nous rejoignons les outils sur les propriétaires en utilisant un LEFT JOIN . Cela va ajouter tous nos propriétaires à notre jeu de résultats, peu importe s'ils possèdent réellement des outils.

Dans la deuxième requête, nous utilisons RIGHT JOIN pour joindre les outils aux propriétaires. De cette manière, nous parvenons à obtenir tous les outils de notre jeu de résultats. Si leur propriétaire ne leur appartient pas, leur colonne propriétaire contiendra simplement NULL . En ajoutant une WHERE WHERE qui filtre par owners.owner_id IS NULL nous définissons le résultat comme les jeux de données, qui n'ont pas déjà été renvoyés par la première requête, car nous ne recherchons que les données dans la table jointe droite.

Puisque nous utilisons UNION ALL le jeu de résultats de la deuxième requête sera attaché au premier ensemble de résultats des requêtes.

SELECT `owners`.`owner`, tools.tool
FROM `owners`
LEFT JOIN `tools` ON `owners`.`owner_id` = `tools`.`owner_id`
UNION ALL
SELECT `owners`.`owner`, tools.tool
FROM `owners`
RIGHT JOIN `tools` ON `owners`.`owner_id` = `tools`.`owner_id`
WHERE `owners`.`owner_id` IS NULL;

+-------+--------------+
| owner | tool         |
+-------+--------------+
| Ben   | Pliers       |
| Ben   | Knife        |
| Ben   | Hacksaw      |
| Jim   | Chisel       |
| Harry | NULL         |
| John  | NULL         |
| Ellie | Hammer       |
| Ellie | Tape Measure |
| NULL  | Level        |
| NULL  | Wrench       |
| NULL  | Screwdriver  |
| NULL  | Clamp        |
+-------+--------------+
12 rows in set (0.00 sec)

Inner-join pour 3 tables

Supposons que nous ayons trois tables qui peuvent être utilisées pour des sites Web simples avec des balises.

• La table de poing est pour les postes.
• Deuxième pour les balises
• Troisième pour la relation Tags & Post

table de poing "jeu vidéo"

table "tags"

Table "tags_meta"

SELECT videogame.id,
    videogame.title,
    videogame.reg_date,
    tags.name,
    tags_meta.post_id
FROM tags_meta
INNER JOIN videogame ON videogame.id = tags_meta.post_id
INNER JOIN tags ON tags.id = tags_meta.tag_id
WHERE tags.name = "elizabeth"
ORDER BY videogame.reg_date

ce code peut renvoyer tous les messages relatifs à cette balise "#elizabeth"

Jointure visualisée

Si vous êtes une personne visuellement orientée, ce diagramme de Venn peut vous aider à comprendre les différents types de JOIN qui existent dans MySQL.