MySQL
Doet mee

Syntaxis

• INNER en OUTER worden genegeerd.

• FULL is niet geïmplementeerd in MySQL.

• "commajoin" ( FROM a,b WHERE ax=by ) wordt afgekeurd; gebruik in plaats daarvan FROM a JOIN b ON ax=by .

• UIT een JOIN b ON ax = bevat rijen die overeenkomen in beide tabellen.

• VANAF EEN LINKS JOIN b ON ax = omvat alle rijen van a , plus overeenkomende gegevens van b , of NULLs als er geen overeenkomende rij is.

Voorbeelden van verbindingen

Vraag om een tabel op DB te maken

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;

Omdat we InnoDB-tabellen gebruiken en weten dat user.course en course.id aan elkaar gerelateerd zijn, kunnen we een externe sleutelrelatie opgeven:

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

Join Query (Inner Join)

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

JOIN met subquery (tabel "Afgeleide")

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

Hiermee wordt de subquery geëvalueerd in een tijdelijke tabel en wordt deze vervolgens JOIN met tbl .

Voorafgaand aan 5.6 kon er geen index op de tijdelijke tabel staan. Dus dit was potentieel zeer inefficiënt:

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

Met 5.6 komt de optimizer de beste index te weten en maakt deze meteen aan. (Dit heeft wat overhead, dus het is nog steeds niet 'perfect'.)

Een ander veel voorkomend paradigma is om een subquery te hebben om iets te initialiseren:

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

(Opmerking: dit is technisch gezien een CROSS JOIN (Cartesiaans product), zoals aangegeven door het ontbreken van ON . Het is echter efficiënt omdat de subquery slechts één rij retourneert die moet worden vergeleken met de n rijen in the_real_table .)

Haal klanten op met bestellingen - variaties op een thema

Hiermee krijgt u alle bestellingen voor alle klanten:

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;

Dit telt het aantal bestellingen voor elke klant:

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;

Telt ook, maar waarschijnlijk sneller:

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

Vermeld alleen de klant met bestellingen.

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

Volledige deelname aan de buitenwereld

MySQL biedt geen ondersteuning voor FULL OUTER JOIN , maar er zijn manieren om er een na te doen.

Gegevens instellen

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

Wat willen we zien?

We willen een lijst krijgen, waarin we zien wie welke tools bezit en welke tools mogelijk geen eigenaar hebben.

De vragen

Om dit te bereiken, kunnen we twee query's combineren met behulp van UNION . In deze eerste vraag voegen we de tools bij de eigenaren samen met behulp van een LEFT JOIN . Dit zal al onze eigenaars aan onze resultatenset toevoegen, het maakt niet uit of ze daadwerkelijk tools bezitten.

In de tweede vraag gebruiken we een RIGHT JOIN om de tools samen te voegen met de eigenaren. Op deze manier slagen we erin om alle tools in onze resultatenset te krijgen, als ze eigendom zijn van niemand zal hun eigenaarskolom gewoon NULL bevatten. Door een WHERE -clausule toe te voegen die filtert op owners.owner_id IS NULL definiëren we het resultaat als die datasets, die nog niet zijn geretourneerd door de eerste query, omdat we alleen naar de gegevens in de juiste gekoppelde tabel zoeken.

Omdat we UNION ALL de resultatenset van de tweede query gekoppeld aan de resultatenset van de eerste query.

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 voor 3 tafels

laten we aannemen dat we drie tabellen hebben die kunnen worden gebruikt voor een eenvoudige website met tags.

• Vuisttabel is voor berichten.
• Tweede voor tags
• Derde voor tags & berichtrelatie

vuist tafel "videogame"

tabel "tags"

tabel "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

deze code kan alle berichten retourneren die gerelateerd zijn aan die tag "#elizabeth"

Joins gevisualiseerd

Als u een visueel georiënteerde persoon bent, kan dit Venn-diagram u helpen de verschillende soorten JOIN 's te begrijpen die binnen MySQL bestaan.