MySQL
Schließt sich an

Syntax

• INNER und OUTER werden ignoriert.

• FULL ist nicht in MySQL implementiert.

• "commajoin" ( FROM a,b WHERE ax=by ) ist verpönt; Verwenden Sie stattdessen FROM a JOIN b ON ax=by .

• FROM a JOIN b ON ax = by fügt Zeilen ein, die in beiden Tabellen übereinstimmen.

• FROM a LEFT JOIN b ON ax = by schließt alle Zeilen von a a , plus übereinstimmende Daten von b oder NULLs wenn keine übereinstimmende Zeile vorhanden ist.

Beispiele verbinden

Abfrage zum Erstellen einer Tabelle auf DB

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;

Da wir InnoDB-Tabellen verwenden und wissen, dass user.course und course.id verwandt sind, können wir eine Fremdschlüsselbeziehung angeben:

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

Query beitreten (Inner Join)

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

JOIN mit Unterabfrage (Tabelle "Abgeleitete")

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

Dadurch wird die Unterabfrage in eine temporäre Tabelle bewerten, dann JOIN dass tbl .

Vor 5.6 konnte es keinen Index für die temporäre Tabelle geben. Das war also möglicherweise sehr ineffizient:

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

Mit 5.6 ermittelt der Optimierer den besten Index und erstellt ihn im laufenden Betrieb. (Dies hat einen gewissen Aufwand, ist also immer noch nicht "perfekt".)

Ein weiteres allgemeines Paradigma besteht darin, eine Unterabfrage zu haben, um etwas zu initialisieren:

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

(Hinweis: Dies ist technisch gesehen ein CROSS JOIN (kartesisches Produkt), was durch das Fehlen von ON angezeigt wird. Dies ist jedoch effizient, da die Unterabfrage nur eine Zeile zurückgibt, die mit den n Zeilen in der the_real_table .)

Kunden mit Bestellungen abrufen - Variationen eines Themas

Dies wird alle Bestellungen für alle Kunden erhalten:

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;

Dadurch wird die Anzahl der Bestellungen für jeden Kunden gezählt:

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;

Auch zählt, aber wahrscheinlich schneller:

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

Listen Sie nur den Kunden mit Bestellungen auf.

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

Voller äußerer Join

MySQL unterstützt den FULL OUTER JOIN , es gibt jedoch Möglichkeiten, einen zu emulieren.

Einrichten der Daten

-- ----------------------------
-- 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);

Was wollen wir sehen?

Wir möchten eine Liste erhalten, in der wir sehen, wer welche Tools besitzt und welche Tools möglicherweise keinen Eigentümer haben.

Die Anfragen

Um dies zu erreichen, können wir zwei Abfragen mit UNION kombinieren. In dieser ersten Abfrage verbinden wir die Tools der Eigentümer mit einem LEFT JOIN . Dadurch werden alle unsere Besitzer zu unserem Resultset hinzugefügt. Dabei spielt es keine Rolle, ob sie tatsächlich Tools besitzen.

In der zweiten Abfrage verwenden wir eine RIGHT JOIN , um die Werkzeuge mit den Besitzern zu verbinden. Auf diese Weise erhalten wir alle Werkzeuge aus unserem Resultset. Wenn sie niemandem gehören, enthält ihre Eigentümerspalte einfach NULL . Durch das Hinzufügen einer WHERE -clause, die nach owners.owner_id IS NULL , definieren wir das Ergebnis als die Datasets, die nicht bereits von der ersten Abfrage zurückgegeben wurden, da wir nur nach den Daten in der rechts verbundenen Tabelle suchen.

Da wir UNION ALL das Resultset der zweiten Abfrage an das erste Resultset der Abfragen angehängt.

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)

Innenverbindung für 3 Tische

Angenommen, wir haben drei Tabellen, die für einfache Websites mit Tags verwendet werden können.

• Faust Tisch ist für Beiträge.
• Zweiter für Tags
• Dritter für Tags & Post-Relation

Faust Tisch "Videospiel"

"Tags" -Tabelle

"tags_meta" -Tabelle

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

Dieser Code kann alle Beiträge zurückgeben, die sich auf dieses Tag beziehen.

Joins visualisiert

Wenn Sie eine visuell orientierte Person sind, kann dieses Venn-Diagramm Ihnen helfen, die verschiedenen Arten von JOIN verstehen, die in MySQL existieren.