в чем разница между left join и inner join
В чем разница между INNER, LEFT и RIGHT JOIN?
В данной статье я раскрою разницу между SQL-запросами INNER, LEFT и RIGHT JOIN. Здесь описываются базовые случаи, для каждой конкретной платформы (MySQL, MSSQL, Oracle и прочих) могут быть свои нюансы.
INNER JOIN
Возвращаются все записи из таблиц table_01 и table_02, связанные посредством primary/foreign ключей, и соответствующие условию WHERE для таблицы table_01. Если в какой-либо из таблиц отсутствует запись, соответствующая соседней, то в выдачу такая пара включена не будет. Иными словами, выдадутся только те записи, которые есть и в первой, и во второй таблице. То есть выборка идет фактически по связи (ключу), выдадутся только те записи, которые связаны между собой. «Одинокие» записи, для которых нет пары в связи, выданы не будут.
LEFT JOIN
Возвращаются все данные из «левой» таблицы, даже если не найдено соответствий в «правой» таблице («левая» таблица в SQL-запросе стоит левее знака равно, «правая» — правее, то есть обычная логика правой и левой руки). Иными словами, если мы присоединяем к «левой» таблице «правую», то выберутся все записи в соответствии с условиями WHERE для левой таблицы. Если в «правой» таблице не было соответствий по ключам, они будут возвращены как NULL. Таким образом, здесь главной выступает «левая» таблица, и относительно нее идет выдача. В условии ON «левая» таблица прописывается первой по порядку (table_01), а «правая» – второй (table_02):
RIGHT JOIN
Возвращаются все данные из «правой» таблицы, даже если не найдено соответствий в «левой» таблице. То есть примерно также, как и в LEFT JOIN, только NULL вернется для полей «левой» таблицы. Грубо говоря, эта выборка ставит во главу угла правую «таблицу», относительно нее идет выдача. Обратите внимание на WHERE в следующем примере, условие выборки затрагивает «правую» таблицу:
Таким образом, мы разложили по полочкам, в чем отличие INNER, LEFT и RIGHT JOIN. Разумеется, представленная выше информация не нова, но она может быть полезна начинающим программистам, которые часто путаются в типах запросов.
Осмысляем работу джойнов в SQL: от реляционной алгебры до наглядных картинок
Выбираем, какие фильмы посмотреть, с помощью соединения данных в SQL.
Опять эта проблема — выбрать кино на вечер. Благодаря стриминговым сервисам доступны едва ли не все фильмы мира: это бесконечное полотно с постерами и фильтры, фильтры, фильтры…
МОЗГ: Поставлю-ка я фильтр по стране: пусть будет Дания, и добавлю ограничение по жанру — триллер… Ну вот — другое дело, относительно небольшой список.
— Мозг, а знаешь почему? Да потому что здесь только фильмы, которые сняты в Дании И помечены как триллеры.
— Да не знаю я, как задать такие критерии в этом сервисе. Вот если бы можно было писать на SQL — тут бы решение нашлось для любой комбинации признаков.
— Легко! Ещё и картинки будут. У меня и база фильмов уже спарсена — тренируйся не хочу.
Фулстек-разработчик. Любимый стек: Java + Angular, но в хорошей компании готова писать хоть на языке Ада.
Договоримся об обозначениях
Назовём множество датских фильмов — D, а множество триллеров — T. У каждого фильма будет уникальный номер, он же ключ. Раз ключ — пусть зовётся Key.
Заодно вспомним, как на SQL пишется простой запрос для связывания данных из двух таблиц:
INNER JOIN
Если не уточнить тип соединения ( JOIN), то по умолчанию применяется INNER JOIN — как раз тот вариант, который сработал в нашем кинофильтре. Это он выбирает и триллеры, и датские фильмы одновременно.
SQL-запросы. В чем разница между INNER JOIN, RIGHT JOIN, LEFT JOIN?
В рамках сегодняшней статьи я расскажу вам в чем разница между INNER JOIN, RIGHT JOIN и LEFT JOIN, приведу подробные примеры, а так же предоставлю некоторые пояснения.
Без баз данных сегодня не обходится практически ни одно приложение. И в этом нет ничего странного, так как наличие пользовательской информации требует специальных хранилищ. Кто-то пытается реализовывать их собственными силами, используя, например, файлы. А кто-то использует уже готовые решения, по типу MySQL. Если в первом случае все задачи ложатся на плечи их создателей, то во втором авторам программных продуктов предоставляется универсальный язык для построения запросов под названием SQL
Однако, специфика использования SQL запросов может оказаться простой не для всех. Поэтому попробуем поэтапно посмотреть их на практике.
Таблица авторов (author) будет выглядеть следующим образом:
| id | name |
|---|---|
| 1 | Вася |
| 2 | Коля |
| 3 | Петя |
Таблица программ (util) будет выглядеть следующим образом:
| id | author_id | name |
|---|---|---|
| 1 | 1 | Вася Мега продукт |
| 2 | 0 | Крутая, но неизвестная утилита |
Итак, исходные данные у нас есть, теперь, давайте рассмотрим каждый из join по отдельности.
INNER JOIN
Данный тип объединения таблиц позволяет находить все те вариации соединения строк двух таблиц, где точно выполняется условие. В нашем примере, мы ищем всех авторов и их произведения. При этом нужно вывести только тех авторов, у которых есть хотя бы одно произведение.
Примечание: Кстати, в большинстве баз данных не обязательно писать перед join слово inner. Однако, в рамках примера я все же его укажу.
SQL запрос будет выглядеть следующим образом
Результат выполнения такого SQL запроса будет следующим:
| id | name | id | author_id | name |
|---|---|---|---|---|
| 1 | Вася | 1 | 1 | Вася Мега продукт |
Как видите, в нашей базе данных известно, что только автор Вася написал хотя бы одну программу. Коля и Петя ничего не написали, поэтому их в результатах нет. Так же не вывелась безызвестная программа.
Теперь, перейдем к следующему типу
LEFT JOIN
Данные тип позволяет находить как все вариации обычного JOIN-а (inner), так и выводить строки из первой таблицы, для которых не нашлись строки из второй таблицы, удовлетворяющие условиям. В нашем случае, мы выведем всех авторов и, если есть, их программы тоже.
SQL запрос будет выглядеть следующим образом. Он точно такой же, как и в предыдущем случае, только вместо inner join стоит left join.
Результат выполнения такого SQL запроса будет следующим:
| id | name | id | author_id | name |
|---|---|---|---|---|
| 1 | Вася | 1 | 1 | Вася Мега продукт |
| 2 | Коля | NULL | NULL | NULL |
| 3 | Петя | NULL | NULL | NULL |
Как видите, были выведены все авторы. В случае с Васей, строка осталась аналогичной, так как он написал хотя бы одну программу. В случае с Колей и Петей возвращаемые результаты были NULL, так как они еще не написали ни одной программы. Так же стоит отметить, что и программа без автора не попала в результат, так как автор неизвестен.
Теперь, последний тип.
RIGHT JOIN
Воспользуемся тем же SQL запросом, но с правым объединением строк таблиц:
Результат выполнения такого SQL запроса будет следующим:
| id | name | id | author_id | name |
|---|---|---|---|---|
| 1 | Вася | 1 | 1 | Вася Мега продукт |
| NULL | NULL | 2 | 0 | Крутая, но неизвестная утилита |
Как видите, вывелся весь список программ. В случае с Мега продуктом, автор нашелся, так что в левой части результата отображаются данные Васи. В случае же с безызвестной утилитой, автор не нашелся, поэтому в полях автора стоит значение NULL.
Если вы внимательно следили, то могли отметить насколько удобно устроен оператор JOIN, всего одной простой правкой можно легко поменять логику основного запроса.
Теперь, надеюсь, у вас не будет проблем с INNER JOIN, RIGHT JOIN, LEFT JOIN, и вы сможете с легкостью реализовывать свои творения.
JOIN в MySQL: INNER, LEFT, RIGHT, FULL, CROSS. Что это и как использовать
Опубликовано: 20 Июня 2019
Оператор JOIN, формирует результирующую таблицу по заданным условиям, из одной или нескольких уже существующих таблиц SQL. В зависимости от необходимого алгоритма формирования таблицы, к оператору можно подставлять ключевые слова: INNER, CROSS, FULL, LEFT, RIGHT.
Данный оператор полезен для возможности выстраивать более гибкие таблицы в SQL. Чтобы иметь возможность выносить «необязательные» или повторяющиеся данные в отдельные таблицы.
Обобщенный синтаксис оператора выглядит так:
Ниже более подробно разобрано как работает каждый из этих методов, на примере двух таблиц: списка брендов автомобилей и списка возможных цветов.
Таблица со списком брендов:
Таблица со списком цветов:
INNER JOIN / CROSS JOIN
В некоторых SQL базах INNER JOIN не может идти без условия, но в MySQL это возможно, поэтому INNER JOIN и CROSS JOIN в данной SQL системе идентичны, как и JOIN, который является синонимом для INNER JOIN.
Простая выборка, без условий, подставит ко всем вариантам из левой таблицы, все варианты из правой таблицы (перекрестное соединение):
Тот же самый результат можно получить путем следующих записей, которые идентичны:
К выборке можно добавить условие, это актуально как для CROSS, так и для INNER JOIN. Выборку можно производить следующими способами:
В таблице ниже, сопоставилены строки из разных таблиц, но имеющие одинаковый id. В этом случае для BMW и зеленого цвета пары не нашлось, и они не попали в результирующую таблицу:
Ту же самую таблицу можно получить следущими записями:
Если бы столбец id у таблицы с цветами назывался бы color_id, то запись для ON и WHERE была бы следующей:
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. Оба оператора не возможно использовать без какого-либо условия.
Это используется если, к примеру, надо вывести все доступные бренды машин, не зависимо от того указан у них цвет или нет:
Или все возможные цвета, независимо от того есть ли у брендов такой цвет в наличии:
Можно дополнить запрос условием на проверку несуществования соседних данных, и получить список записей, которые не имеют пары, при этом поля, которые необходимо вывести, можно указать, как и при обычном SELECT запросе:
FULL JOIN объединяет в себе LEFT JOIN и RIGHT JOIN.
В MySQL он используется без условий, результат использования этого оператора будет таким:
Но, при добавлении сравнения USING в MySQL, результат будет аналогичен INNER JOIN:
Другие условия с оператором FULL JOIN в MySQL использовать нельзя, по крайней мере на момент написания статьи.
Сложные и многотабличные запросы
В примере для простоты использовалось только 2 простых таблицы, в реальности же количество условий и таблиц может быть значительно больше. При необходимости таблицу так же можно объединять саму с собой, но в этом случае стоит использовать алиас для имени таблицы:
В 1 запросе можно использовать разные типы объединений, и разное количество запросов, но стоит учесть, что это значительно увеличивает время на обработку запроса. В случае если приходиться использовать длинные запросы, возможно стоит подумать над правильностью организации архитектуры таблиц.
Объединения таблиц можно использовать не только в выборке, но и при удалении и изменении таблиц (UPDATE, DELETE).
Понимание джойнов сломано. Это точно не пересечение кругов, честно
Так получилось, что я провожу довольно много собеседований на должность веб-программиста. Один из обязательных вопросов, который я задаю — это чем отличается INNER JOIN от LEFT JOIN.
Чаще всего ответ примерно такой: «inner join — это как бы пересечение множеств, т.е. остается только то, что есть в обеих таблицах, а left join — это когда левая таблица остается без изменений, а от правой добавляется пересечение множеств. Для всех остальных строк добавляется null». Еще, бывает, рисуют пересекающиеся круги.
Я так устал от этих ответов с пересечениями множеств и кругов, что даже перестал поправлять людей.
Дело в том, что этот ответ в общем случае неверен. Ну или, как минимум, не точен.
Давайте рассмотрим почему, и заодно затронем еще парочку тонкостей join-ов.
Во-первых, таблица — это вообще не множество. По математическому определению, во множестве все элементы уникальны, не повторяются, а в таблицах в общем случае это вообще-то не так. Вторая беда, что термин «пересечение» только путает.
(Update. В комментах идут жаркие споры о теории множеств и уникальности. Очень интересно, много нового узнал, спасибо)
INNER JOIN
Давайте сразу пример.
Итак, создадим две одинаковых таблицы с одной колонкой id, в каждой из этих таблиц пусть будет по две строки со значением 1 и еще что-нибудь.
Давайте, их, что ли, поджойним
Если бы это было «пересечение множеств», или хотя бы «пересечение таблиц», то мы бы увидели две строки с единицами.
На практике ответ будет такой:
Для начала рассмотрим, что такое CROSS JOIN. Вдруг кто-то не в курсе.
CROSS JOIN — это просто все возможные комбинации соединения строк двух таблиц. Например, есть две таблицы, в одной из них 3 строки, в другой — 2:
Тогда CROSS JOIN будет порождать 6 строк.
Так вот, вернемся к нашим баранам.
Конструкция
— это, можно сказать, всего лишь синтаксический сахар к
Небольшой disclaimer: хотя inner join логически эквивалентен cross join с фильтром, это не значит, что база будет делать именно так, в тупую: генерить все комбинации и фильтровать. На самом деле там более интересные алгоритмы.
LEFT JOIN
Если вы считаете, что левая таблица всегда остается неизменной, а к ней присоединяется или значение из правой таблицы или null, то это в общем случае не так, а именно в случае когда есть повторы данных.
Опять же, создадим две таблицы:
Теперь сделаем LEFT JOIN:
Результат будет содержать 5 строк, а не по количеству строк в левой таблице, как думают очень многие.
Так что, LEFT JOIN — это тоже самое что и INNER JOIN (т.е. все комбинации соединений строк, отфильтрованных по какому-то условию), и плюс еще записи из левой таблицы, для которых в правой по этому фильтру ничего не совпало.
LEFT JOIN можно переформулировать так:
Сложноватое объяснение, но что поделать, зато оно правдивее, чем круги с пересечениями и т.д.
Условие ON
Удивительно, но по моим ощущениям 99% разработчиков считают, что в условии ON должен быть id из одной таблицы и id из второй. На самом деле там любое булево выражение.
Например, есть таблица со статистикой юзеров users_stats, и таблица с ip адресами городов.
Тогда к статистике можно прибавить город
где && — оператор пересечения (см. расширение посгреса ip4r)
Если в условии ON поставить true, то это будет полный аналог CROSS JOIN
Производительность
Есть люди, которые боятся join-ов как огня. Потому что «они тормозят». Знаю таких, где есть полный запрет join-ов по проекту. Т.е. люди скачивают две-три таблицы себе в код и джойнят вручную в каком-нибудь php.
Это, прямо скажем, странно.
Если джойнов немного, и правильно сделаны индексы, то всё будет работать быстро. Проблемы будут возникать скорее всего лишь тогда, когда у вас таблиц будет с десяток в одном запросе. Дело в том, что планировщику нужно определить, в какой последовательности осуществлять джойны, как выгоднее это сделать.
Сложность этой задачи O(n!), где n — количество объединяемых таблиц. Поэтому для большого количества таблиц, потратив некоторое время на поиски оптимальной последовательности, планировщик прекращает эти поиски и делает такой план, какой успел придумать. В этом случае иногда бывает выгодно вынести часть запроса в подзапрос CTE; например, если вы точно знаете, что, поджойнив две таблицы, мы получим очень мало записей, и остальные джойны будут стоить копейки.
Кстати, Еще маленький совет по производительности. Если нужно просто найти элементы в таблице, которых нет в другой таблице, то лучше использовать не ‘LEFT JOIN… WHERE… IS NULL’, а конструкцию EXISTS. Это и читабельнее, и быстрее.
Выводы
Как мне кажется, не стоит использовать диаграммы Венна для объяснения джойнов. Также, похоже, нужно избегать термина «пересечение».
Как объяснить на картинке джойны корректно, я, честно говоря, не представляю. Если вы знаете — расскажите, плиз, и киньте в коменты. А мы обсудим это в одном из ближайших выпусков подкаста «Цинковый прод». Не забудьте подписаться.