MySQL
присоединяется

Синтаксис

• INNER и OUTER игнорируются.

• FULL не реализован в MySQL.

• «commajoin» ( FROM a,b WHERE ax=by ) нахмурился; используйте FROM a JOIN b ON ax=by вместо.

• FROM JOIN b ON ax = by включает строки, которые соответствуют в обеих таблицах.

• FROM LEFT JOIN b ON ax = by включает все строки из a , плюс данные сопоставления из b или NULLs если нет соответствующей строки.

Примеры подключения

Запрос на создание таблицы на 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;

Поскольку мы используем таблицы InnoDB и знаем, что user.course и course.id связаны, мы можем указать отношение внешнего ключа:

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

Присоединиться к запросу (Inner Join)

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

JOIN с подзапросом (таблица «Производные»)

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

Это будет оценивать подзапрос в временную таблицу, затем JOIN к tbl .

До 5.6 не может быть указателя в таблице temp. Таким образом, это было потенциально очень неэффективно:

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

С 5.6 оптимизатор определяет лучший индекс и создает его на лету. (У этого есть некоторые накладные расходы, поэтому он все еще не «совершенен».)

Еще одна распространенная парадигма заключается в том, чтобы иметь подзапрос, чтобы инициализировать что-то:

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

(Примечание: это технически CROSS JOIN (декартово произведение), о чем свидетельствует отсутствие ON . Однако он эффективен, потому что в подзапросе возвращается только одна строка, которая должна быть сопоставлена ​​с n строками в the_real_table .)

Извлечение клиентов с заказами - вариации на тему

Это получит все заказы для всех клиентов:

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;

Это будет подсчитывать количество заказов для каждого клиента:

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;

Также, считается, но, вероятно, быстрее:

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

Перечислите только заказчиков с заказами.

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

Полная внешняя связь

MySQL не поддерживает FULL OUTER JOIN , но есть способы их эмулировать.

Настройка данных

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

Что мы хотим видеть?

Мы хотим получить список, в котором мы видим, кто владеет теми инструментами и какие инструменты могут не иметь владельца.

Запросы

Для этого мы можем объединить два запроса с помощью UNION . В этом первом запросе мы присоединяемся к инструментам владельцев, используя LEFT JOIN . Это добавит всех наших владельцев в наш набор результатов, неважно, действительно ли они владеют инструментами.

Во втором запросе мы используем RIGHT JOIN для присоединения инструментов к владельцам. Таким образом, нам удастся получить все инструменты в нашем наборе результатов, если они не принадлежат никому, их столбец владельца просто будет содержать NULL . Добавляя WHERE -clause, который фильтрует owners.owner_id IS NULL мы определяем результат как те наборы данных, которые еще не были возвращены первым запросом, поскольку мы только ищем данные в правой объединенной таблице.

Поскольку мы используем UNION ALL набор результатов второго запроса будет привязан к первому запросу.

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)

Внутреннее соединение для 3 таблиц

предположим, у нас есть три таблицы, которые можно использовать для простого сайта с тегами.

• Кулачный стол предназначен для сообщений.
• Второе для тегов
• Третий для отношений Tags & Post

кулачный стол "видеоигра"

таблица «тегов»

Таблица "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

этот код может возвращать все сообщения, связанные с этим тегом "#elizabeth"

Соединения визуализируются

Если вы визуально ориентированный человек, эта диаграмма Венна может помочь вам понять разные типы JOIN которые существуют в MySQL.