MySQL
Fogar

Syntax

• INNER och OUTER ignoreras.

• FULL är inte implementerad i MySQL.

• "commajoin" ( FROM a,b WHERE ax=by ) är rynkade på; använd FROM a JOIN b ON ax=by istället.

• FRÅN en JOIN b ON ax = av innehåller rader som matchar i båda tabellerna.

• FRÅN VÄNSTER JOIN b ON ax = av inkluderar alla rader från a , plus matchande data från b eller NULLs om det inte finns någon matchande rad.

Gå med i exempel

Fråga för att skapa tabell på 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;

Eftersom vi använder InnoDB-tabeller och vet att user.course och course.id är relaterade kan vi ange ett utländskt nyckelförhållande:

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

Gå med i fråga (Inre medlem)

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

GÅ MED med subfråga ("Derived" tabell)

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

Detta kommer att utvärdera delfrågan till en temptabell och sedan JOIN det till tbl .

Före 5.6 kunde det inte finnas ett index på temp-tabellen. Så detta var potentiellt mycket ineffektivt:

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

Med 5.6 räknar optimeringsprogrammet ut det bästa indexet och skapar det på språng. (Detta har en del omkostnader, så det är fortfarande inte "perfekt".)

Ett annat vanligt paradigm är att ha en undersökning för att initialisera något:

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

(Observera: detta är tekniskt en CROSS JOIN (kartesisk produkt), vilket indikeras av bristen på ON . Det är emellertid effektivt eftersom underkursen bara returnerar en rad som måste matchas till de n raderna the_real_table .)

Hämta kunder med beställningar - variationer på ett tema

Detta får alla beställningar för alla kunder:

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;

Detta räknar antalet beställningar för varje kund:

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;

Räknar också, men förmodligen snabbare:

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

Lista endast kunden med beställningar.

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

Full Yuter Join

MySQL stöder inte FULL OUTER JOIN , men det finns sätt att emulera en.

Ställa in data

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

Vad vill vi se?

Vi vill få en lista där vi ser vem som äger vilka verktyg och vilka verktyg som kanske inte har någon ägare.

Frågorna

För att uppnå detta kan vi kombinera två frågor med hjälp av UNION . I den här första frågan går vi samman med ägarnas verktyg genom att använda ett LEFT JOIN . Detta kommer att lägga till alla våra ägare till vårt resultatsats, spelar ingen roll om de verkligen äger verktyg.

I den andra frågan använder vi RIGHT JOIN att gå med verktygen på ägarna. På så sätt lyckas vi få alla verktyg i vårt resultatsats, om de ägs av ingen kommer deras ägarkolumn helt enkelt att innehålla NULL . Genom att lägga till en WHERE -clause som filtrerar genom owners.owner_id IS NULL vi definierar resultatet som de datamängder som inte redan returneras av den första frågan, eftersom vi bara söker efter data i rätt kopplade tabellen.

Eftersom vi använder UNION ALL resultaten av den andra frågan att bifogas till den första frågeställningen.

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)

Inre sammanfogning för 3 bord

låt oss anta att vi har tre tabeller som kan användas för en enkel webbplats med taggar.

• Nävebordet är för inlägg.
• För det andra för taggar
• Tredje för Taggar & Post-relation

knytnävebord "videospel"

"taggar" -tabell

"tags_meta" -tabell

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

den här koden kan returnera alla inlägg som är relaterade till taggen "#elizabeth"

Går visualiserade

Om du är en visuellt orienterad person kan detta Venn-diagram hjälpa dig att förstå de olika typerna av JOIN som finns inom MySQL.