Использование JOIN в MySQL

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

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

Вы можете использовать JOIN в MySQL в операторах SELECT, UPDATE и DELETE для выполнения запросов к нескольким таблицам.

JOIN можно условно разделить на следующие три категории по функциональности:

  • INNER JOIN (внутреннее соединение или эквисоединение):Извлекает записи с совпадающими отношениями полей в двух таблицах.
  • LEFT JOIN (левое соединение):Извлекает все записи из левой таблицы, даже если в правой таблице нет соответствующих совпадающих записей.
  • RIGHT JOIN (правое соединение):Противоположность LEFT JOIN, используется для извлечения всех записей из правой таблицы, даже если в левой таблице нет соответствующих совпадающих записей.

Структура базы данных и данные для скачивания, используемые в этой главе:example-mysql-join-test.sql。

INNER JOIN

INNER JOIN возвращает совпадающие строки, которые удовлетворяют условию соединения в обеих таблицах. Ниже приведен базовый синтаксис оператора INNER JOIN:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Описание параметра:

  • column1, column2, ... — это имена столбцов, которые вы хотите выбрать. Если вы используете*означает выбор всех столбцов.
  • table1, table2— это имена двух таблиц, которые нужно соединить.
  • table1.column_name = table2.column_name— это условие соединения, определяющее столбцы, используемые для сопоставления в двух таблицах.

Простой INNER JOIN:

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Приведённый выше SQL-запрос выберет ID заказа и имя клиента из таблиц orders и customers, удовлетворяющих условию соединения.

2. Использование псевдонимов таблиц:

SELECT o.order_id, c.customer_name
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id;

Приведённый выше SQL-запрос использует o и c в качестве псевдонимов таблиц orders и customers.

3. INNER JOIN с несколькими таблицами:

SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;

Приведённый выше SQL-запрос включает соединение четырёх таблиц: orders, customers, order_items и products. Он выбирает ID заказа, имя клиента и название продукта и соединяет связанные столбцы этих таблиц.

4. Фильтрация с помощью предложения WHERE:

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.order_date >= '2023-01-01';

Приведённый выше SQL-запрос использует предложение WHERE после INNER JOIN для фильтрации заказов с датой заказа не ранее '2023-01-01'.

LEFT JOIN

LEFT JOIN возвращает все строки из левой таблицы и включает совпадающие строки из правой таблицы. Если в правой таблице нет совпадающей строки, будут возвращены значения NULL. Ниже приведён базовый синтаксис оператора LEFT JOIN:

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

1. Простой LEFT JOIN:

SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Приведённая выше инструкция SQL выберет ID клиента и имя клиента из таблицы customer, а также включит все строки из левой таблицы customers и соответствующий ID заказа (если он есть).

2. Использование псевдонимов таблиц:

SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id;

Приведённая выше инструкция SQL использует псевдонимы таблиц c и o для замены имён таблиц customers и orders соответственно.

3. Многотабличное LEFT JOIN:

SELECT customers.customer_id, customers.customer_name, orders.order_id, products.product_name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN order_items ON orders.order_id = order_items.order_id
LEFT JOIN products ON order_items.product_id = products.product_id;

Приведённая выше инструкция SQL соединяет четыре таблицы: customers, orders, order_items и products, и выбирает ID клиента, имя клиента, ID заказа и название продукта. LEFT JOIN гарантирует, что даже при отсутствии совпадающих строк в order_items или products информация о клиенте и заказе всё равно возвращается.

4. Фильтрация с помощью предложения WHERE:

SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_date >= '2023-01-01' OR orders.order_id IS NULL;

Приведённая выше инструкция SQL использует предложение WHERE после LEFT JOIN для фильтрации заказов с датами заказа '2023-01-01' или позже, а также клиентов без соответствующих заказов.

LEFT JOIN — часто используемый тип соединения, особенно когда нужно вернуть все строки из левой таблицы. Когда в правой таблице нет совпадающей строки, связанные столбцы отображаются как NULL. При использовании LEFT JOIN убедитесь, что вы понимаете условие соединения, и при необходимости фильтруйте результаты.

RIGHT JOIN

RIGHT JOIN возвращает все строки из правой таблицы и включает совпадающие строки из левой таблицы. Если в левой таблице нет совпадающей строки, будут возвращены значения NULL. Ниже приведён базовый синтаксис инструкции RIGHT JOIN:

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Ниже приведён простой пример RIGHT JOIN:

SELECT customers.customer_id, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

Приведённая выше инструкция SQL выберет все ID заказов из правой таблицы orders и включит совпадающие ID клиентов из левой таблицы customers. Если в таблице customers нет совпадающего ID клиента, связанные столбцы будут отображаться как NULL.

Во время разработки RIGHT JOIN используется нечасто, поскольку того же эффекта можно добиться с помощью LEFT JOIN и смены порядка таблиц. Например, приведённый выше запрос можно переписать с использованием LEFT JOIN так:

SELECT customers.customer_id, orders.order_id
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

Приведенная выше инструкция SQL возвращает тот же результат, поскольку LEFT JOIN и RIGHT JOIN симметричны. На практике вы можете выбрать, какую форму использовать, исходя из личных предпочтений или стандартов организации.


Использование INNER JOIN в командной строке

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

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

Пример

У нас есть две таблицы, tcount_tbl и example_tbl, в базе данных EXAMPLE.

Попробуйте следующий пример:

Тестовые данные для примера

mysql> use EXAMPLE; База данных изменена mysql> SELECT * FROM tcount_tbl; +---------------+--------------+ | example_author | example_count| +---------------+--------------+ | Пример руководства |10 | | EXAMPLE.COM | 20 | | Google | 22 | +---------------+--------------+ 3 строк в наборе (0.01 sec) mysql> SELECT * from example_tbl; +-----------+---------------+---------------+-----------------+ | example_id | example_title | example_author | submission_date | +-----------+---------------+---------------+-----------------+ | 1| ОбучениеPHP| Пример руководства |2017-04-12 | | 2| ОбучениеMySQL| Пример руководства |2017-04-12 | | 3| ОбучениеJava | EXAMPLE.COM | 2015-05-01 | | 4| ОбучениеPython | EXAMPLE.COM | 2016-03-06 | | 5| ОбучениеC | FK | 2017-04-05 | +-----------+---------------+---------------+-----------------+ 5 строк in set (0.01 sec)

Далее мы используемINNER JOIN из MySQL (можно также опустить INNER и использовать JOIN с тем же эффектом)чтобы соединить две вышеуказанные таблицы и прочитать значения поля example_count в таблице tcount_tbl, соответствующие всем полям example_author в таблице example_tbl:

INNER JOIN

mysql> SELECT a.example_id, a.example_author, b.example_count FROM example_tbl a INNER JOIN tcount_tbl b ON a.example_author = b.example_author; +-------------+-----------------+----------------+ | a.example_id | a.example_author | b.example_count | +-------------+-----------------+----------------+ | 1| Пример руководства |10 | | 2| Пример руководства |10 | | 3 | EXAMPLE.COM | 20 | | 4 | EXAMPLE.COM | 20 | +-------------+-----------------+----------------+ 4 строк in set (0.00 sec)

Приведённый выше оператор SQL эквивалентен:

Предложение WHERE

mysql> SELECT a.example_id, a.example_author, b.example_count FROM example_tbl a, tcount_tbl b WHERE a.example_author = b.example_author; +-------------+-----------------+----------------+ | a.example_id | a.example_author | b.example_count | +-------------+-----------------+----------------+ | 1| Пример руководства |10 | | 2| Пример руководства |10 | | 3 | EXAMPLE.COM | 20 | | 4 | EXAMPLE.COM | 20 | +-------------+-----------------+----------------+ 4 строк в наборе (0.01 sec)


MySQL LEFT JOIN

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

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

При использовании LEFT JOIN убедитесь, что вы понимаете условие соединения, и при необходимости фильтруйте результаты.

MySQL LEFT JOIN отличается от JOIN; LEFT JOIN читает все данные из левой таблицы данных, даже если в правой таблице нет соответствующих данных.

Пример

Попробуйте следующий пример, используяexample_tblв качестве левой таблицы,tcount_tblв качестве правой таблицы, чтобы понять применение MySQL LEFT JOIN:

LEFT JOIN

mysql> SELECT a.example_id, a.example_author, b.example_count FROM example_tbl a LEFT JOIN tcount_tbl b ON a.example_author = b.example_author; +-------------+-----------------+----------------+ | a.example_id | a.example_author | b.example_count | +-------------+-----------------+----------------+ | 1| Пример руководства |10 | | 2| Пример руководства |10 | | 3 | EXAMPLE.COM | 20 | | 4 | EXAMPLE.COM | 20 | | 5 | FK | NULL | +-------------+-----------------+----------------+ 5 строк в наборе (0.01 sec)

В приведенном выше примере используется LEFT JOIN, который читает все данные выбранных полей из левой таблицы данных example_tbl, даже если в правой таблице tcount_tbl нет соответствующего значения поля example_author.


MySQL RIGHT JOIN

MySQL RIGHT JOIN читает все данные из правой таблицы данных, даже если в левой таблице нет соответствующих данных.

В реальной разработке RIGHT JOIN используется нечасто, потому что того же эффекта можно добиться с помощью LEFT JOIN и смены порядка таблиц.

Пример

Попробуйте следующий пример, используяexample_tblв качестве левой таблицы,tcount_tblв качестве правой таблицы, чтобы понять применение MySQL RIGHT JOIN:

RIGHT JOIN

mysql> SELECT a.example_id, a.example_author, b.example_count FROM example_tbl a RIGHT JOIN tcount_tbl b ON a.example_author = b.example_author; +-------------+-----------------+----------------+ | a.example_id | a.example_author | b.example_count | +-------------+-----------------+----------------+ | 1| Пример руководства |10 | | 2| Пример руководства |10 | | 3 | EXAMPLE.COM | 20 | | 4 | EXAMPLE.COM | 20 | | NULL | NULL | 22 | +-------------+-----------------+----------------+ 5 строк in set (0.01 sec)

В приведенном выше примере используется RIGHT JOIN, который читает все данные выбранных полей из правой таблицы данных tcount_tbl, даже если в левой таблице example_tbl нет соответствующего значения поля example_author.


Использование JOIN в PHP-скриптах

В PHP используйте функцию mysqli_query() для выполнения SQL-запросов. Вы можете использовать те же SQL-запросы, что выше, в качестве параметров функции mysqli_query().

Попробуйте следующий пример:

Тест MySQL ORDER BY:

<?php $dbhost = 'localhost'; //адрес хоста сервера MySQL $dbuser = 'root'; //имя пользователя MySQL $dbpass = '123456'; //пароль пользователя MySQL $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Ошибка подключения:' . mysqli_error($conn)); } //Установите кодировку, чтобы предотвратить появление искажённых китайских символов mysqli_query($conn , "set names utf8"); $sql = 'SELECT a.example_id, a.example_author, b.example_count FROM example_tbl a INNER JOIN tcount_tbl b ON a.example_author = b.example_author'; mysqli_select_db( $conn, 'EXAMPLE' ); $retval = mysqli_query( $conn, $sql ); if(! $retval ) { die('Не удалось прочитать данные:' . mysqli_error($conn)); } echo '<h2>Пример руководства MySQL JOIN Test<h2>'; echo '<table border="1"><tr><td>ID руководства</td><td>Автор</td><td>Количество входов</td></tr>'; while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC)) { echo "<tr><td> {$row['example_id']}</td> ". "<td>{$row['example_author']} </td> ". "<td>{$row['example_count']} </td> ". "</tr>"; } echo '</table>'; mysqli_close($conn); ?>

Результат вывода показан на рисунке ниже:

Другие расширения