JOIN в MySQL: INNER, LEFT, RIGHT, FULL, CROSS. Что это и как использовать

Опубликовано: 20 Июня 2019

Оператор JOIN, формирует результирующую таблицу по заданным условиям, из одной или нескольких уже существующих таблиц SQL. В зависимости от необходимого алгоритма формирования таблицы, к оператору можно подставлять ключевые слова: INNER, CROSS, FULL, LEFT, RIGHT.

• INNER JOIN (JOIN) — каждая строка из первой (левой) таблицы, сопоставляется с каждой строкой из второй (правой) таблицы, после чего, происходит проверка условия. В MySQL условие не обязательно, поэтому INNER JOIN стал аналогом CROSS JOIN. Если условия нет, или оно истинно, то строки попадают в результирующую таблицу. 
• LEFT JOIN (LEFT OUTER JOIN) — важен порядок следования таблиц. Сначала происходит формирование таблицы соединением INNER JOIN. Затем, в результат добавляются записи левой таблицы, не вошедшие в результат после INNER JOIN. Для них, соответствующие записи из правой таблицы заполняются значениями NULL.
• RIGHT JOIN (RIGHT OUTER JOIN) — важен порядок следования таблиц. Аналогично LEFT JOIN, но во главе вторая таблица. Сначала происходит формирование таблицы соединением INNER JOIN. Затем, в результат добавляются записи правой таблицы, не вошедшие в результат после INNER JOIN. Для них, соответствующие записи из левой таблицы заполняются значениями NULL.
• FULL JOIN (FULL OUTER JOIN) — оператор FULL JOIN можно воспринимать как сочетание операторов INNER JOIN + LEFT JOIN + RIGHT JOIN. Сначала происходит формирование таблицы соединением INNER JOIN. Затем, в результат добавляются записи левой таблицы, не вошедшие прежде в результат. Для них, соответствующие записи из правой таблицы заполняются значениями NULL. Наконец, в таблицу добавляются значения не вошедшие в результат формирования из правой таблицы. Для них, соответствующие записи из левой таблицы заполняются значениями NULL.
• CROSS JOIN — каждая строка левой таблицы сопоставляется с каждой строкой правой таблицы. В результате получается таблица со всеми возможными сочетаниями строк обеих таблиц (декартово произведение). 

Данный оператор полезен для возможности выстраивать более гибкие таблицы в SQL. Чтобы иметь возможность выносить «необязательные» или повторяющиеся данные в отдельные таблицы.

Обобщенный синтаксис оператора выглядит так:

SELECT field1, field1, [... n]
  FROM  Table1 t1
  {INNER | {LEFT | RIGHT | FULL} OUTER | CROSS } JOIN
  Table2 {ON <condition> | USING (field_name [,... n])}

Ниже более подробно разобрано как работает каждый из этих методов, на примере двух таблиц: списка брендов автомобилей и списка возможных цветов.

Таблица со списком брендов:

mysql> SELECT * FROM brands;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Ford      |
|  2 | BMW       |
|  3 | Audi      |
+----+-----------+

Таблица со списком цветов:

mysql> SELECT * FROM colors;
+----+---------------------+
| id | colors              |
+----+---------------------+
|  1 | Синий               |
|  3 | Красный             |
|  5 | Зелёный             |
+----+---------------------+

INNER JOIN / CROSS JOIN

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

Простая выборка, без условий, подставит ко всем вариантам из левой таблицы, все варианты из правой таблицы (перекрестное соединение):

mysql> SELECT * FROM brands CROSS JOIN colors;
и
mysql> SELECT * FROM brands INNER JOIN colors;
+----+-----------+----+---------------------+
| id | brands    | id | colors              |
+----+-----------+----+---------------------+
|  1 | Ford      |  1 | Синий               |
|  2 | BMW       |  1 | Синий               |
|  3 | Audi      |  1 | Синий               |
|  1 | Ford      |  3 | Красный             |
|  2 | BMW       |  3 | Красный             |
|  3 | Audi      |  3 | Красный             |
|  1 | Ford      |  5 | Зеленый             |
|  2 | BMW       |  5 | Зеленый             |
|  3 | Audi      |  5 | Зеленый             |
+----+-----------+----+---------------------+

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

SELECT * FROM brands INNER JOIN colors;
SELECT * FROM brands CROSS JOIN colors;
SELECT * FROM brands JOIN colors;
SELECT * FROM brands, colors;

К выборке можно добавить условие, это актуально как для CROSS, так и для INNER JOIN. Выборку можно производить следующими способами:

• USING — если в условии участвуют столбцы с одинаковым названием. Не возможно использовать при перечислении таблиц через запятую.
• ON — если сопоставляются столбцы с разным названием. Фильтрация этой командой происходит до того как сопостовляются строки таблицы. Не возможно использовать при перечислении таблиц через запятую.
• WHERE — если сопоставляются столбцы с разным названием. Фильтрация этой командой происходит после того как сопостовляются строки таблицы. Можно использовать при перечислении через запятую. Список возможных условий.

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

mysql> SELECT * FROM brands CROSS JOIN colors using(id);
и
mysql> SELECT * FROM brands INNER JOIN colors using(id);
+----+----------+---------------------+
| id | brands   | colors              |
+----+----------+---------------------+
|  1 | Ford     | Синий               |
|  3 | Audi     | Красный             |
+----+----------+---------------------+

Ту же самую таблицу можно получить следущими записями:

mysql> SELECT * FROM brands {INNER | CROSS | FULL} JOIN colors USING(id);
mysql> SELECT * FROM brands {INNER | CROSS} JOIN colors ON brands.id = colors.id;
mysql> SELECT * FROM brands, colors WHERE brands.id = colors.id;

Если бы столбец id у таблицы с цветами назывался бы color_id, то запись для ON и WHERE была бы следующей:

brands.id = colors.color_id;

LEFT JOIN / RIGHT JOIN / FULL JOIN

LEFT JOIN, RIGHT JOIN и FULL JOIN считаются внешними соединениями (OUTER JOIN), поэтому у них также есть синонимы: LEFT OUTER  JOIN, RIGHT OUTER JOIN и FULL OUTER JOIN.

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

Это используется если, к примеру, надо вывести все доступные бренды машин, не зависимо от того указан у них цвет или нет:

mysql> SELECT * FROM brands LEFT JOIN colors ON brands.id = colors.id;
и
mysql> SELECT * FROM brands LEFT JOIN colors USING(id);
+----+-----------+---------------------+
| id | brands    | colors              |
+----+-----------+---------------------+
|  1 | Ford      | Синий               |
|  2 | BMW       | NULL                |
|  3 | Audi      | Красный             |
+----+-----------+---------------------+

Или все возможные цвета, независимо от того есть ли у брендов такой цвет в наличии:

mysql> SELECT * FROM brands RIGHT JOIN colors ON brands.id = colors.id;
и
mysql> SELECT * FROM brands RIGHT JOIN colors USING(id);
+----+-----------+---------------------+
| id | brands    | colors              |
+----+-----------+---------------------+
|  1 | Ford      | Синий               |
|  3 | Audi      | Красный             |
|  5 | NULL      | Зеленый             |
+----+-----------+---------------------+

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

mysql> SELECT id, brands FROM brands LEFT JOIN colors USING(id) WHERE colors IS NULL;
+----+-----------+
| id | brands    |
+----+-----------+
|  2 | BMW       |
+----+-----------+

FULL JOIN объединяет в себе LEFT JOIN и RIGHT JOIN.

В MySQL он используется без условий, результат использования этого оператора будет таким: 

mysql> SELECT * FROM brands FULL JOIN colors;
+----+-----------+---------------------+
| id | brands    | colors              |
+----+-----------+---------------------+
|  1 | Ford      | Синий               |
|  2 | BMW       | NULL                |
|  3 | Audi      | Красный             |
|  5 | NULL      | Зеленый             |
+----+-----------+---------------------+

Но, при добавлении сравнения USING в MySQL, результат будет аналогичен INNER JOIN:

mysql> SELECT * FROM brands FULL JOIN colors using(id);
+----+----------+---------------------+
| id | brands   | colors              |
+----+----------+---------------------+
|  1 | Ford     | Синий               |
|  3 | Audi     | Красный             |
+----+----------+---------------------+

Другие условия с оператором FULL JOIN в MySQL использовать нельзя, по крайней мере на момент написания статьи. 

Сложные и многотабличные запросы

В примере для простоты использовалось только 2 простых таблицы, в реальности же количество условий и таблиц может быть значительно больше. При необходимости таблицу так же можно объединять саму с собой, но в этом случае стоит использовать алиас для имени таблицы:

mysql> SELECT * FROM brands JOIN brands AS t2;
+----+-----------+----+-----------+
| id | name      | id | name      |
+----+-----------+----+-----------+
|  1 | Ford      |  1 | Ford      |
|  2 | BMW       |  1 | Ford      |
|  3 | Audi      |  1 | Ford      |
|  1 | Ford      |  2 | BMW       |
|  2 | BMW       |  2 | BMW       |
|  3 | Audi      |  2 | BMW       |
|  1 | Ford      |  3 | Audi      |
|  2 | BMW       |  3 | Audi      |
|  3 | Audi      |  3 | Audi      |
+----+-----------+----+-----------+

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

mysql> SELECT * FROM brands AS t1 JOIN brands AS t2 LEFT JOIN brands AS t3 
ON t1.id = t3.id AND t2.id = t1.id;
+----+-----------+----+-----------+------+-----------+
| id | brands    | id | brands    | id   | brands    |
+----+-----------+----+-----------+------+-----------+
|  1 | Ford      |  1 | Ford      |    1 | Ford      |
|  2 | BMW       |  1 | Ford      | NULL | NULL      |
|  3 | Audi      |  1 | Ford      | NULL | NULL      |
|  1 | Ford      |  2 | BMW       | NULL | NULL      |
|  2 | BMW       |  2 | BMW       |    2 | BMW       |
|  3 | Audi      |  2 | BMW       | NULL | NULL      |
|  1 | Ford      |  3 | Audi      | NULL | NULL      |
|  2 | BMW       |  3 | Audi      | NULL | NULL      |
|  3 | Audi      |  3 | Audi      |    3 | Audi      |
+----+-----------+----+-----------+------+-----------+

Объединения таблиц можно использовать не только в выборке, но и при удалении и изменении таблиц (UPDATE, DELETE).