В этом учебном пособии вы узнаете, как использовать JOINS (INNER и OUTER) в MySQL с синтаксисом, рисунками и примерами.
Описание
MySQL JOINS используются для извлечения данных из нескольких таблиц. JOIN выполняется всякий раз, когда две или более таблиц объединяются в SQL предложении.
Существуют различные типы соединений MySQL:
- MySQL INNER JOIN (или иногда называют простое соединение)
- MySQL LEFT OUTER JOIN (или иногда называют LEFT JOIN)
- MySQL RIGHT OUTER JOIN (или иногда называют RIGHT JOIN)
Рассмотрим синтаксис MySQL JOIN, а также изучим примеры MySQL JOIN.
INNER JOIN (простое соединение)
Скорее всего, вы уже писали запросы в которых используются MySQL INNER JOIN. Это наиболее распространенный тип соединения. MySQL INNER JOINS возвращает все строки из нескольких таблиц, где выполняется условия соединения.
Синтаксис
Синтаксис INNER JOIN в MySQL:
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
В этом рисунке, MySQL INNER JOIN возвращает затененную область:
MySQL INNER JOIN будет возвращать записи, где table1 и table2 будут пересекаться.
Пример
Ниже приведен пример MySQL INNER JOIN:
|
1 2 3 4 |
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Этот пример MySQL INNER JOIN возвращает все строки из таблиц suppliers и orders, где имеются соответствующие значение поля supplier_id в обоих таблицах.
Рассмотрим некоторые данные, чтобы понять, как работает INNER JOIN:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
У нас есть еще одна таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 05.05.2015 |
| 500126 | 10001 | 08.02.2016 |
| 500127 | 10004 | 06.01.2017 |
Если мы выполним MySQL оператор SELECT (который содержит INNER JOIN) ниже:
|
1 2 3 4 |
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Наш результирующий набор будет выглядеть следующим образом:
| supplier_id | name | order_date |
|---|---|---|
| 10000 | IBM | 05.05.2015 |
| 10001 | Hewlett Packard | 08.02.2016 |
Строки для Microsoft и NVIDIA из таблицы suppliers будут опущены, так как значения supplier_id 10002 и 10003 не существует в обеих таблицах. Строка order_id 500127 из таблицы orders будет опущена, так как supplier_id 10004 не существует в таблице suppliers.
Старый Синтаксис
В качестве последнего примечания, стоит отметить, что приведенный выше пример MySQL INNER JOIN можно переписать, используя старый неявный синтаксис следующим образом (но рекомендуется использовать синтаксис INNER JOIN):
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;
LEFT OUTER JOIN
Другой тип соединения называется MySQL LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.
Синтаксис
Синтаксис для LEFT OUTER JOIN в MySQL:
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
В некоторых базах данных LEFT OUTER JOIN заменяется на LEFT JOIN.
На этом рисунке, MySQL LEFT OUTER JOIN возвращает затененную область:
MySQL LEFT OUTER JOIN возвратит все записи из table1 и только те записи из table2, которые пересекаются с table1.
Пример
|
1 2 3 4 |
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Этот пример LEFT OUTER JOIN возвратит все строки из таблицы suppliers, и только те строки из таблицы orders, где объединяемые поля равны.
Если значение supplier_id в таблице suppliers не существует в таблице orders, все поля таблицы orders будут отображаться в результирующем наборе как NULL.
Рассмотрим некоторые данные, чтобы понять, как работает LEFT OUTER JOIN:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
У нас есть еще одна таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 05.05.2015 |
| 500126 | 10001 | 08.02.2016 |
Если мы выполним MySQL оператор SELECT (который содержит LEFT OUTER JOIN) ниже:
|
1 2 3 4 |
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Результирующий набор будет выглядеть следующим образом:
| supplier_id | name | order_date |
|---|---|---|
| 10000 | IBM | 05.05.2015 |
| 10001 | Hewlett Packard | 08.02.2016 |
| 10002 | Microsoft | null |
| 10003 | NVIDIA | null |
Строки для Microsoft и NVIDIA будут включены, так как был использован LEFT OUTER JOIN. Тем не менее, вы заметите, что поле order_date для этих записей содержит значение NULL.
RIGHT OUTER JOIN
Другой тип соединения называется MySQL RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.
Синтаксис
СинтаксиRIGHT OUTER JOIN в MySQL:
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
В некоторых базах данных, RIGHT OUTER JOIN заменяется на RIGHT JOIN.
На этом рисунке, MySQL RIGHT OUTER JOIN возвращает затененную область:
MySQL RIGHT OUTER JOIN возвратит все записи из table2 и только те записи из table1, которые пересекаются с table2.
Пример
Ниже приведен пример MySQL RIGHT OUTER JOIN:
|
1 2 3 4 |
SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Этот пример RIGHT OUTER JOIN возвращает все строки из таблицы orders и только те строки из таблицы suppliers, где объединяемые поля равны.
Если значение supplier_id в таблице orders не существует в таблице suppliers, все поля в таблице suppliers будут отображаться в результирующем наборе как NULL.
Рассмотрим некоторые данные, чтобы понять, как работает RIGHT OUTER JOIN:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:
| supplier_id | supplier_name |
|---|---|
| 10000 | Apple |
| 10001 |
У нас есть вторая таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 12.05.2016 |
| 500126 | 10001 | 14.05.2016 |
| 500127 | 10002 | 18.05.2016 |
Если мы выполним MySQL оператор SELECT (который содержит RIGHT OUTER JOIN) ниже:
|
1 2 3 4 |
SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id; |
Результирующий набор будет выглядеть следующим образом:
| order_id | order_date | supplier_name |
|---|---|---|
| 500125 | 12.05.2016 | Apple |
| 500126 | 14.05.2016 | |
| 500127 | 18.05.2016 | null |
Строка для order_id 500127 будет включена, так как был использован RIGHT OUTER JOINS. Тем не менее, вы заметите, что поле supplier_name для этой записи содержит значение NULL.