Чем отличается join от inner join

#7 — Объединение данных. Join (Inner, Left, Right)

Для объединения данных из нескольких таблиц используются Joint’ы. В ходе урока вы изучите несколько способов объединения данных: Inner Join, Left Join и Right Join.

Видеоурок

Для объединения данных с нескольких таблиц используются Join’ы. Существует несколько форматов объединения данных: Inner, Left, Right и Outer. Принцип их работы представлен на фото ниже:

Наиболее часто используется формат Inner Join, рассмотрим его:

SELECT поле(или же поля) FROM таблички(table1) INNER JOIN объединение_с_табличкой(table2) ON table1.поле = table2.поле;

Исправление ошибки

Если при работе с джоинтами возникает ошибка, то в таком случае вам нужно создать таблицу orders как показано на фото ниже:

Здесь главное указать в качестве движка таблицы значение InnoDb.

Задание к уроку

Необходимо оформить подписку на проект, чтобы получить доступ ко всем домашним заданиям

Большое задание по курсу

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

В чем различия между INNER JOIN и OUTER JOIN?

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

• Внутреннее соединение A и B: A пересекает B, т.е. внутренняя часть пересечения диаграммы Венна.
• Внешнее соединение A и B: A соединяется с B, т.е. внешняя часть соединения на диаграмме Венна.

Примеры

Предположим, у вас есть две таблицы. Каждая состоит из одного столбца, со следующими значениями:

A B - - 1 3 2 4 3 5 4 6 

Обратите внимание, что (1,2) уникальны для A, (3,4) – общие элементы, а (5,6) уникальны для B.

Внутреннее соединение

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

select * from a INNER JOIN b on a.a = b.b; select a.*, b.* from a, b where a.a = b.b; a | b --+-- 3 | 3 4 | 4 

Левое внешнее соединение

Результат левого внешнего соединения – все строки таблицы A плюс все строки таблицы B, имеющие совпадение со строками таблицы А.

select * from a LEFT OUTER JOIN b on a.a = b.b; select a.*, b.* from a, b where a.a = b.b(+); a | b --+----- 1 | null 2 | null 3 | 3 4 | 4 

Правое внешнее соединение

Результат правого внешнего соединения – все строки таблицы В плюс все строки таблицы А, имеющие совпадение со строками таблицы В.

select * from a RIGHT OUTER JOIN b on a.a = b.b; select a.*,b.* from a,b where a.a(+) = b.b; a | b -----+---- 3 | 3 4 | 4 null | 5 null | 6 

Полное внешнее соединение

Результат полного внешнего соединения – соединение таблиц A и B, т.е. все строки A и все строки B. Если какой-либо элемент таблицы A не имеет соответствия в таблице B, эта часть B пуста, и наоборот.

select * from a FULL OUTER JOIN b on a.a = b.b; a | b -----+----- 1 | null 2 | null 3 | 3 4 | 4 null | 6 null | 5 

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

Оператор 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(RIGHTOUTER JOIN) — важен порядок следования таблиц. Аналогично LEFT JOIN, но во главе вторая таблица. Сначала происходит формирование таблицы соединением INNER JOIN. Затем, в результат добавляются записи правой таблицы, не вошедшие в результат после INNER JOIN. Для них, соответствующие записи из левой таблицы заполняются значениями NULL.
• FULL JOIN(FULLOUTER JOIN) — оператор FULL JOIN можно воспринимать как сочетание операторов INNER JOIN + LEFT JOIN + RIGHT JOIN. Сначала происходит формирование таблицы соединением INNER JOIN. Затем, в результат добавляются записи левой таблицы, не вошедшие прежде в результат. Для них, соответствующие записи из правой таблицы заполняются значениями NULL. Наконец, в таблицу добавляются значения не вошедшие в результат формирования из правой таблицы. Для них, соответствующие записи из левой таблицы заполняются значениями NULL.
• CROSS JOIN — каждая строка левой таблицы сопоставляется с каждой строкой правой таблицы. В результате получается таблица со всеми возможными сочетаниями строк обеих таблиц (декартово произведение).

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

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

SELECT field1, field1, [. n] FROM Table1 t1 OUTER | CROSS > JOIN Table2 | 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  CROSS  JOIN colors USING(id); mysql> SELECT * FROM brands INNER  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).

За идею примеров спасибо Антону Прибора

Команды JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN

Команды JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN используются для связывания таблиц по определенным полям связи.

Синтаксис

SELECT поля FROM имя_таблицы LEFT JOIN имя_связанной_таблицы ON условие_связи WHERE условие_выборки

Примеры

Все примеры будут по таблицам countries и cities , если не сказано иное.

id айди	name имя
1	Беларусь
2	Россия
3	Украина

id айди	name имя	country_id айди страны
1	Минск	1
2	Витебск	1
3	Москва	2
4	Питер	2
5	Лондон	0

Пример . LEFT JOIN

В данном примере .

SELECT cities.id as city_id, cities.name as city_name, cities.country_id as city_country_id, countries.id as country_id, countries.name as country_name FROM cities LEFT JOIN countries ON countries.id=cities.country_id

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

city_id айди города	city_name название города	city_country_id айди страны	country_id айди страны	country_name название страны
1	Минск	1	1	Беларусь
2	Витебск	1	1	Беларусь
3	Москва	2	2	Россия
4	Питер	2	2	Россия
5	Лондон	0	NULL

Пример . RIGHT JOIN

В данном примере . Лондон не выберется, а Украина наоборот

SELECT cities.id as city_id, cities.name as city_name, cities.country_id as city_country_id, countries.id as country_id, countries.name as country_name FROM cities RIGHT JOIN countries ON countries.id=cities.country_id

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

city_id айди города	city_name название города	city_country_id айди страны	country_id айди страны	country_name название страны
1	Минск	1	1	Беларусь
2	Витебск	1	1	Беларусь
3	Москва	2	2	Россия
4	Питер	2	2	Россия
NULL	NULL	NULL	3	Украина

Пример . INNER JOIN

В данном примере . Лондон и Украина не выберется

SELECT cities.id as city_id, cities.name as city_name, cities.country_id as city_country_id, countries.id as country_id, countries.name as country_name FROM cities INNER JOIN countries ON countries.id=cities.country_id

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

city_id айди города	city_name название города	city_country_id айди страны	country_id айди страны	country_name название страны
1	Минск	1	1	Беларусь
2	Витебск	1	1	Беларусь
3	Москва	2	2	Россия
4	Питер	2	2	Россия