MySQL
Łączy się

Składnia

• INNER i OUTER są ignorowane.

• FULL nie jest zaimplementowany w MySQL.

• „commajoin” ( FROM a,b WHERE ax=by ) jest skrzywiony; zamiast tego użyj FROM a JOIN b ON ax=by .

• OD JOIN b ON ax = by zawiera wiersze, które pasują do obu tabel.

• OD LEWEGO DOŁĄCZENIA b ON ax = by obejmuje wszystkie wiersze z a , plus pasujące dane z b lub NULLs jeśli nie ma pasującego wiersza.

Łączenie przykładów

Zapytanie o utworzenie tabeli na 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;

Ponieważ używamy tabel InnoDB i wiemy, że user.course i course.id są powiązane, możemy określić relację klucza obcego:

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

Dołącz do zapytania (dołączenie wewnętrzne)

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

DOŁĄCZ z podzapytaniem (tabela „Pochodna”)

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

Spowoduje to przetworzenie podzapytania w tabelę tymczasową, a następnie JOIN do tbl .

Przed wersją 5.6 nie było indeksu w tabeli tymczasowej. Było to potencjalnie bardzo nieefektywne:

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

W przypadku wersji 5.6 optymalizator oblicza najlepszy indeks i tworzy go w locie. (Ma to pewne koszty ogólne, więc nadal nie jest „idealne”).

Innym powszechnym paradygmatem jest posiadanie podzapytania w celu zainicjowania czegoś:

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

(Uwaga: technicznie jest to CROSS JOIN (produkt kartezjański), na co wskazuje brak ON . Jest jednak skuteczny, ponieważ podkwerenda zwraca tylko jeden wiersz, który musi być dopasowany do n wierszy w the_real_table .)

Odzyskaj klientów za pomocą zamówień - wariacje na temat

Otrzymasz wszystkie zamówienia dla wszystkich klientów:

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;

Policzy to liczbę zamówień dla każdego klienta:

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;

Liczy się, ale prawdopodobnie szybciej:

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

Lista tylko klienta z zamówieniami.

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

Pełne połączenie zewnętrzne

MySQL nie obsługuje FULL OUTER JOIN , ale istnieją sposoby na jego emulację.

Konfigurowanie danych

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

Co chcemy zobaczyć

Chcemy uzyskać listę, w której widzimy, kto jest właścicielem które narzędzia, a które narzędzia mogą nie mieć właściciela.

Zapytania

Aby to osiągnąć, możemy połączyć dwa zapytania za pomocą UNION . W tym pierwszym zapytaniu łączymy narzędzia właścicieli za pomocą LEFT JOIN . Spowoduje to dodanie wszystkich naszych właścicieli do naszego zestawu wyników, bez względu na to, czy faktycznie posiadają narzędzia.

W drugim zapytaniu używamy RIGHT JOIN aby połączyć narzędzia z właścicielami. W ten sposób udaje nam się uzyskać wszystkie narzędzia z naszego zestawu wyników, jeśli nikt nie jest ich właścicielem, kolumna właściciela będzie po prostu zawierać NULL . Dodając klauzulę WHERE która filtruje według owners.owner_id IS NULL , definiujemy wynik jako te owners.owner_id IS NULL danych, które nie zostały jeszcze zwrócone przez pierwsze zapytanie, ponieważ szukamy tylko danych w prawej połączonej tabeli.

Ponieważ używamy UNION ALL zestaw wyników drugiego zapytania zostanie dołączony do zestawu wyników pierwszych zapytań.

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)

Łączenie wewnętrzne dla 3 stolików

załóżmy, że mamy trzy tabele, których można użyć do prostej witryny z tagami.

• Tabela pięści jest dla postów.
• Drugi w przypadku tagów
• Trzecia relacja tagów i postów

tabela pięści „gra wideo”

tabela „tagi”

Tabela „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

ten kod może zwrócić wszystkie posty związane z tym tagiem „#elizabeth”

Połączenia wizualizowane

Jeśli jesteś osobą zorientowaną wizualnie, ten diagram Venna może pomóc ci zrozumieć różne rodzaje JOIN które istnieją w MySQL.