• Агентство интернет маркетинга Оптиваль
  • +7(920)111-5690
  • info@optiwahl.ru
  1. Optiwahl
  2.  
  3. Блог
  4.  
  5. Программирование
  6.  MySQL LEFT JOIN в примерах

MySQL LEFT JOIN в примерах

от Раздел Программирование

Объединения позволяют извлекать данные из нескольких таблиц базы данных без создания временных таблиц и за один запрос.

JOIN, в переводе на великий и могучий, означает "объединять", то есть собирать из нескольких кусочков единое целое. В базе данных MySQL такими "кусочками" служат столбцы таблиц, которые можно объединять при выборке.

Объединения позволяют извлекать данные из нескольких таблиц базы данных без создания временных таблиц и за один запрос.

LEFT JOIN - Левостороннее внешнее объединенея

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

К примеру, чтобы получить полный список наименований товаров вместе с их описанием, нужно выполнить следующий запрос:

Код - внешнее левостороннее объединение
mysql> SELECT * FROM nomenclature LEFT JOIN description USING(id);
+----+-----------+---------------------+
| id | name      | description         |
+----+-----------+---------------------+
|  1 | Книга     | Замечательная книга |
|  2 | Табуретка | NULL                |
|  3 | Карандаш  | Красный карандаш    |
+----+-----------+---------------------+
3 rows in set (0.01 sec)

Поскольку для наименования Табуретка в таблице описаний нет подходящей записи, то в поле description подставился NULL. Это справедливо для всех записей, у которых нет подходящей пары.

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

Код - выборка наименований без описаний
mysql> SELECT id, name FROM nomenclature LEFT JOIN description USING(id) WHERE description IS NULL;
+----+-----------+
| id | name      |
+----+-----------+
|  2 | Табуретка |
+----+-----------+
1 row in set (0.00 sec)

По сути это и есть основное назначение внешних запросов - показывать расхождение данных двух таблиц.

Кроме того, при таком объединении обязательным является условие, которое задаётся через ON или USING. Без него запрос будет выдавать ошибку.

Многотабличные запросы

Испольузя JOIN, можно объединять не только две таблицы, как было описано выше, но и гораздо больше. В MySQL 5.0 на сегодняшний день можно объединить вплоть до 61 таблицы. Помимо объединений разных таблиц, MySQL позволяет объединять таблицу саму с собой. Однако, в любом случае необходимо следить за именами столбцов и таблиц, если они будут неоднозначны, то запрос не будет выполнен.

Так, если таблицу просто объединить саму на себя, то возникнет конфликт имён и запрос не выполнится.

Код - Объединение таблицы саму на себя
mysql> SELECT * FROM nomenclature JOIN nomenclature;
ERROR 1066 (42000): Not unique table/alias: 'nomenclature'

Обойти конфликт имён позволяет использование синонимов (alias) для имён таблиц и столбцов. В следующем примере внутреннее объединение будет работать успешнее:

Код - Объединение таблицы саму на себя
mysql> SELECT * FROM nomenclature JOIN nomenclature AS t2;
+----+-----------+----+-----------+
| id | name      | id | name      |
+----+-----------+----+-----------+
|  1 | Книга     |  1 | Книга     |
|  2 | Табуретка |  1 | Книга     |
|  3 | Карандаш  |  1 | Книга     |
|  1 | Книга     |  2 | Табуретка |
|  2 | Табуретка |  2 | Табуретка |
|  3 | Карандаш  |  2 | Табуретка |
|  1 | Книга     |  3 | Карандаш  |
|  2 | Табуретка |  3 | Карандаш  |
|  3 | Карандаш  |  3 | Карандаш  |
+----+-----------+----+-----------+
9 rows in set (0.00 sec)

MySQL не накладывает ограничений на использование разных типов объединений в одном запросе, поэтому можно формировать довольно сложные конструкции:

Код - Пример сложного объединения таблиц
mysql> SELECT * FROM nomenclature AS t1 JOIN nomenclature AS t2 LEFT JOIN nomenclature AS t3 ON t1.id = t3.id AND t2.id = t1.id;
+----+-----------+----+-----------+------+-----------+
| id | name      | id | name      | id   | name      |
+----+-----------+----+-----------+------+-----------+
|  1 | Книга     |  1 | Книга     |    1 | Книга     |
|  2 | Табуретка |  1 | Книга     | NULL | NULL      |
|  3 | Карандаш  |  1 | Книга     | NULL | NULL      |
|  1 | Книга     |  2 | Табуретка | NULL | NULL      |
|  2 | Табуретка |  2 | Табуретка |    2 | Табуретка |
|  3 | Карандаш  |  2 | Табуретка | NULL | NULL      |
|  1 | Книга     |  3 | Карандаш  | NULL | NULL      |
|  2 | Табуретка |  3 | Карандаш  | NULL | NULL      |
|  3 | Карандаш  |  3 | Карандаш  |    3 | Карандаш  |
+----+-----------+----+-----------+------+-----------+
9 rows in set (0.00 sec)

Помимо выборок использовать объединения можно также и в запросах UPDATE и DELETE

Так, следующие три запроса проделывают одинаковую работу:

Код - Многотаблицные обновления
mysql> UPDATE nomenclature AS t1, nomenclature AS t2 SET t1.id = t2.id WHERE t1.id = t2.id;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> UPDATE nomenclature AS t1 JOIN nomenclature AS t2 SET t1.id = t2.id WHERE t1.id = t2.id;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> UPDATE nomenclature AS t1 JOIN nomenclature AS t2 USING(id) SET t1.id = t2.id;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

Таким же образом работают и многтабличные удаления

Код - Многотабличные удаления
mysql> DELETE t1 FROM nomenclature AS t1 JOIN nomenclature AS t2 USING(id) WHERE t2.id > 10;
Query OK, 0 rows affected (0.02 sec)

Следует помнить, что при использовании многотабличных запросов на удаление или обновление данных, нельзя включать в запрос конструкции ORDER BY и LIMIT. Впрочем, это ограничение очень эффективно обходится при помощи временных таблиц, просто, надо это учитывать при модификации однотабличных запросов.

Примеры запросов

Подсчет числа сопутствующих товаров для всех товаров интернет магазина на JoomShoping (Joomla)

Код - Количество привязанных товаров JoomShoping
mysql> SELECT t1.product_id, COUNT(t2.id) AS total FROM _jshopping_products AS t1
LEFT JOIN _jshopping_products_relations AS t2
ON t1.product_id = t2.product_id
GROUP BY t1.product_id;

;

Полезно для выявления узких мест при SEO перелинковке товаров

Пример: Установка значений поля справочника по фильтру значений другого поля таблицы (тип = Metiz для товаров с наименованием, содержащим 'гвозд').

UPDATE `db_name`.`t1` SET `type`='metiz' WHERE `t1`.`name` LIKE '%гвозд%';

Поделиться

Комментарии

Вы пишете как гость.