MySQL
Si unisce

Sintassi

• INNER e OUTER sono ignorati.

• FULL non è implementato in MySQL.

• "commajoin" ( FROM a,b WHERE ax=by ) è corrugato; usa FROM a JOIN b ON ax=by invece.

• FROM a JOIN b ON ax = include le righe che corrispondono in entrambe le tabelle.

• DA UN GIUNTO SINISTRO b ON ax = include tutte le righe da a , più i dati corrispondenti da b , o NULLs se non esiste una riga corrispondente.

Unire esempi

Query per creare una tabella su 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;

Poiché utilizziamo le tabelle InnoDB e sappiamo che user.course e course.id sono correlati, possiamo specificare una relazione di chiave esterna:

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;

Iscriviti con sottoquery (tabella "Derivata")

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

Questo valuterà la sottoquery in una tabella temporanea, quindi JOIN a tbl .

Prima di 5.6, non poteva esserci un indice sulla tabella temporanea. Quindi, questo era potenzialmente molto inefficiente:

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

Con 5.6, l'ottimizzatore calcola l'indice migliore e lo crea al volo. (Questo ha un sovraccarico, quindi non è ancora 'perfetto'.)

Un altro paradigma comune è avere una sottoquery per inizializzare qualcosa:

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

(Nota: questo è tecnicamente un CROSS JOIN (prodotto cartesiano), come indicato dalla mancanza di ON . Tuttavia è efficiente perché la sottoquery restituisce solo una riga che deve essere abbinata alle n righe in the_real_table .)

Recupera i clienti con ordini: variazioni su un tema

Ciò otterrà tutti gli ordini per tutti i clienti:

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;

Questo conterà il numero di ordini per ogni cliente:

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;

Inoltre, conta, ma probabilmente più veloce:

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

Elencare solo il cliente con gli ordini.

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

Full Outer Join

MySQL non supporta FULL OUTER JOIN , ma ci sono modi per emularne uno.

Impostazione dei dati

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

Cosa vogliamo vedere?

Vogliamo ottenere una lista, in cui vediamo chi possiede quali strumenti e quali strumenti potrebbero non avere un proprietario.

Le domande

Per fare ciò, possiamo combinare due query usando UNION . In questa prima query stiamo unendo gli strumenti sui proprietari usando un LEFT JOIN . Ciò aggiungerà tutti i nostri proprietari al nostro gruppo di risultati, non importa se possiedono effettivamente strumenti.

Nella seconda query usiamo un RIGHT JOIN per unire gli strumenti ai proprietari. In questo modo riusciamo a ottenere tutti gli strumenti nel nostro set di risultati, se non sono di proprietà di nessuno, la colonna del proprietario conterrà semplicemente NULL . Aggiungendo un WHERE -clause che sta filtrando da owners.owner_id IS NULL stiamo definendo il risultato come quei set di dati, che non sono ancora stati restituiti dalla prima query, poiché stiamo cercando solo i dati nella giusta tabella unita.

Poiché utilizziamo UNION ALL il set di risultati della seconda query verrà collegato al primo set di risultati delle 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 per 3 tavoli

supponiamo di avere tre tabelle che possono essere utilizzate per un semplice sito Web con tag.

• La tabella del pugno è per i post.
• Secondo per i tag
• Terzo per tag e posta

pugno tavolo "videogame"

tabella "tags"

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

questo codice può restituire tutti i post relativi a quel tag "#elizabeth"

Join visualizzati

Se sei una persona con un orientamento visivo, questo diagramma di Venn può aiutarti a capire i diversi tipi di JOIN che esistono all'interno di MySQL.