Какая разница между правым и левым JOIN’ом?
Этот вопрос любят задавать на собеседованиях на позицию джуниора в IT-компаниях. Это, если хотите, классика жанра. Что же, давайте разбираться, в чём разница между SQL-запросами RIGHT и LEFT JOIN. Заодно, вспомним и запрос INNER JOIN.
Примечание: в статье описываются базовые случаи для общего понимания. В зависимости от конкретной базы данных возможны нюансы.
Внутреннее соединение INNER JOIN
С помощью этого запроса вы возвратите все записи из таблиц table_1 и table_2, которые связаны с помощью первичного (primary) и внешнего (foreign) ключей, а также отвечающие условию WHERE для таблицы table_1.
Если в какой-нибудь из вышеописанных таблиц отсутствует запись, которая соответствует соседней, эта пара не будет включена в общую выдачу. Таким образом, мы получим лишь те записи, которые существует как в первой, так и во второй таблицах. По сути, выборка осуществляется по наличию связи (ключу), то есть выдаются лишь записи, связанные между собой. Если у вас есть «одинокие» записи (записи без пары), то они выданы не будут.
SELECT * FROM table_1 INNER JOIN table_2 ON table_1.primary_key = table_2.foreign_key WHERE table_1.column_1 = ‘value’Внешнее соединение LEFT JOIN
С помощью этого запроса вы вернёте все данные из «левой» таблицы даже в том случае, если не будет найдено соответствий в «правой» таблице. Подразумевается, что «левая» таблица в запросе находится левее знака равно, а «правая», соответственно, правее (стандартная логика правой и левой руки).
Говоря иначе, когда мы присоединяем «правую» таблицу к «левой», происходит выборка всех записей согласно условиям WHERE для «левой» таблицы. Если в «правой» таблице у нас отсутствуют соответствующие записи по ключам, они вернутся как NULL. В результате главной выступает именно «левая» таблица, и именно относительно неё осуществляется выдача. При этом в условии ON «левая» таблица прописывается первой по порядку (table_1), а «правая» – второй (table_2):
SELECT * FROM table_1 LEFT JOIN table_2 ON table_1.primary_key = table_2.foreign_key WHERE table_1.column_1 = ‘value’Внешнее соединение RIGHT JOIN
Используя этот запрос, вы вернёте все данные из «правой» таблицы даже в том случае, если не будут найдены соответствия в «левой» таблице. То есть всё происходит по аналогии с LEFT JOIN, однако NULL вернётся для полей «левой» таблицы. Иными словами, главной выступает именно правая «таблица» и выдача осуществляется относительно неё. Также обратите внимание на WHERE, т. к. условие выборки теперь затрагивает «правую» таблицу:
SELECT * FROM table_1 RIGHT JOIN table_2 ON table_1.primary_key = table_2.foreign_key WHERE table_2.column_1 = ‘value’В двух словах:
- LEFT JOIN — это абсолютно всё из левой таблицы, плюс то, что нашлось в правой (то, что удовлетворяет выражению ON). Если не нашлось в правой, то напротив записи из левой будет NULL;
- RIGHT JOIN — наоборот;
- INNER JOIN — только те записи из левой и правой таблиц, которые удовлетворяют выражению ON (в обеих таблицах NULL недопустим);
- FULL JOIN — всё вместе.
Совет
Лучше всего разбираться с этими запросами на практике. Для этого создайте соответствующую базу данных и таблицы в ней. Это не займёт много времени. К примеру, вы можете попрактиковаться с помощью этого видеоурока. Только «потрогав» всё руками, вы действительно поймёте, какова разница между правым и левым JOIN’ом.
В чем различия между INNER JOIN и OUTER JOIN?
Также вы можете воспользоваться данной схемой для понимания соединения таблиц:
Отслеживать
ответ дан 12 апр 2016 в 8:08
8,910 11 11 золотых знаков 31 31 серебряный знак 55 55 бронзовых знаков
Эм.. Я что-то не понимаю, или в чём гениальность идеи делать join, а потом выкидывать все данные из другой таблицы?
12 апр 2016 в 9:10
@Qwertiy убирается пересечение таблиц, тут же все наглядно видно.
12 апр 2016 в 10:43
@Qwertiy в контексте вопросно-ответного сайта вроде SO так, скажем, можно искать вопросы без ответов: соединить вопросы с ответами и отфильтровать только те, у которых совпадений с ответами не оказалось. Случаи не так уж редки.
– user181100
12 апр 2016 в 13:06
Это даже не перевод.
14 апр 2016 в 17:34
@Qwertiy Если рассмотреть вариант с left join, то такой запрос нужен когда необходимо найти в таблице А записи у которых каким-то образом заполнено поле key (не null) но отсутствует соответствующая запись в таблице B.
11 мая 2016 в 8:42
Предположим, вы хотите выполнить соединение по столбцам без дубликатов, что встречается довольно часто:
- Внутреннее соединение 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 Понимание джойнов сломано. Это точно не пересечение кругов, честно
Так получилось, что я провожу довольно много собеседований на должность веб-программиста. Один из обязательных вопросов, который я задаю — это чем отличается INNER JOIN от LEFT JOIN.
Чаще всего ответ примерно такой: "inner join — это как бы пересечение множеств, т.е. остается только то, что есть в обеих таблицах, а left join — это когда левая таблица остается без изменений, а от правой добавляется пересечение множеств. Для всех остальных строк добавляется null". Еще, бывает, рисуют пересекающиеся круги.
Я так устал от этих ответов с пересечениями множеств и кругов, что даже перестал поправлять людей.
Дело в том, что этот ответ в общем случае неверен. Ну или, как минимум, не точен.
Давайте рассмотрим почему, и заодно затронем еще парочку тонкостей join-ов.
Во-первых, таблица — это вообще не множество. По математическому определению, во множестве все элементы уникальны, не повторяются, а в таблицах в общем случае это вообще-то не так. Вторая беда, что термин "пересечение" только путает.
(Update. В комментах идут жаркие споры о теории множеств и уникальности. Очень интересно, много нового узнал, спасибо)
INNER JOIN
Давайте сразу пример.
Итак, создадим две одинаковых таблицы с одной колонкой id, в каждой из этих таблиц пусть будет по две строки со значением 1 и еще что-нибудь.
INSERT INTO table1 (id) VALUES (1), (1) (3); INSERT INTO table2 (id) VALUES (1), (1), (2);Давайте, их, что ли, поджойним
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;Если бы это было "пересечение множеств", или хотя бы "пересечение таблиц", то мы бы увидели две строки с единицами.
На практике ответ будет такой:
| id | id | | --- | --- | | 1 | 1 | | 1 | 1 | | 1 | 1 | | 1 | 1 |
Для начала рассмотрим, что такое CROSS JOIN. Вдруг кто-то не в курсе.
CROSS JOIN — это просто все возможные комбинации соединения строк двух таблиц. Например, есть две таблицы, в одной из них 3 строки, в другой — 2:
select * from t1;id ---- 1 2 3
select * from t2;id ---- 4 5
Тогда CROSS JOIN будет порождать 6 строк.
select * from t1 cross join t2; id | id ----+---- 1 | 4 1 | 5 2 | 4 2 | 5 3 | 4 3 | 5
Так вот, вернемся к нашим баранам.
Конструкция
t1 INNER JOIN t2 ON condition— это, можно сказать, всего лишь синтаксический сахар к
t1 CROSS JOIN t2 WHERE conditionТ.е. по сути INNER JOIN — это все комбинации соединений строк с неким фильтром condition . В общем-то, можно это представлять по разному, кому как удобнее, но точно не как пересечение каких-то там кругов.
Небольшой disclaimer: хотя inner join логически эквивалентен cross join с фильтром, это не значит, что база будет делать именно так, в тупую: генерить все комбинации и фильтровать. На самом деле там более интересные алгоритмы.
LEFT JOIN
Если вы считаете, что левая таблица всегда остается неизменной, а к ней присоединяется или значение из правой таблицы или null, то это в общем случае не так, а именно в случае когда есть повторы данных.
Опять же, создадим две таблицы:
insert into t1 (id) values (1), (1), (3); insert into t2 (id) values (1), (1), (4), (5);Теперь сделаем LEFT JOIN:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id;Результат будет содержать 5 строк, а не по количеству строк в левой таблице, как думают очень многие.
| id | id | | --- | --- | | 1 | 1 | | 1 | 1 | | 1 | 1 | | 1 | 1 | | 3 | |
Так что, LEFT JOIN — это тоже самое что и INNER JOIN (т.е. все комбинации соединений строк, отфильтрованных по какому-то условию), и плюс еще записи из левой таблицы, для которых в правой по этому фильтру ничего не совпало.
LEFT JOIN можно переформулировать так:
SELECT * FROM t1 CROSS JOIN t2 WHERE t1.id = t2.id UNION ALL SELECT t1.id, null FROM t1 WHERE NOT EXISTS ( SELECT FROM t2 WHERE t2.id = t1.id )Сложноватое объяснение, но что поделать, зато оно правдивее, чем круги с пересечениями и т.д.
Условие ON
Удивительно, но по моим ощущениям 99% разработчиков считают, что в условии ON должен быть id из одной таблицы и id из второй. На самом деле там любое булево выражение.
Например, есть таблица со статистикой юзеров users_stats, и таблица с ip адресами городов.
Тогда к статистике можно прибавить город
SELECT s.id, c.city FROM users_stats AS s JOIN cities_ip_ranges AS c ON c.ip_range && s.ipгде && — оператор пересечения (см. расширение посгреса ip4r)
Если в условии ON поставить true, то это будет полный аналог CROSS JOIN
"table1 JOIN table2 ON true" == "table1 CROSS JOIN table2"Производительность
Есть люди, которые боятся join-ов как огня. Потому что "они тормозят". Знаю таких, где есть полный запрет join-ов по проекту. Т.е. люди скачивают две-три таблицы себе в код и джойнят вручную в каком-нибудь php.
Это, прямо скажем, странно.
Если джойнов немного, и правильно сделаны индексы, то всё будет работать быстро. Проблемы будут возникать скорее всего лишь тогда, когда у вас таблиц будет с десяток в одном запросе. Дело в том, что планировщику нужно определить, в какой последовательности осуществлять джойны, как выгоднее это сделать.
Сложность этой задачи O(n!), где n — количество объединяемых таблиц. Поэтому для большого количества таблиц, потратив некоторое время на поиски оптимальной последовательности, планировщик прекращает эти поиски и делает такой план, какой успел придумать. В этом случае иногда бывает выгодно вынести часть запроса в подзапрос CTE; например, если вы точно знаете, что, поджойнив две таблицы, мы получим очень мало записей, и остальные джойны будут стоить копейки.
Кстати, Еще маленький совет по производительности. Если нужно просто найти элементы в таблице, которых нет в другой таблице, то лучше использовать не 'LEFT JOIN… WHERE… IS NULL', а конструкцию EXISTS. Это и читабельнее, и быстрее.
Выводы
Как мне кажется, не стоит использовать диаграммы Венна для объяснения джойнов. Также, похоже, нужно избегать термина "пересечение".
Как объяснить на картинке джойны корректно, я, честно говоря, не представляю. Если вы знаете — расскажите, плиз, и киньте в коменты.
Update В этом видео я наглядно объясняю, как правильно визуализировать джойны (English):
Update2 Продолжение статьи здесь: https://habr.com/ru/post/450528/
Больше полезного можно найти на telegram-канале о разработке "Cross 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 месяца