Что такое left join в sql

Язык SQL – объединение JOIN

Продолжаем изучать основы SQL, и пришло время поговорить о простых объединениях JOIN. И сегодня мы рассмотрим, как объединяются данные по средствам операторов LEFT JOIN, RIGHT JOIN, CROSS JOIN и INNER JOIN, другими словами, научимся писать запросы, которые объединяют данные, и как обычно изучать все это будем на примерах.

Объединения JOIN очень важны в SQL, так как без умения писать запросы с объединением данных разных объектов, просто не обойтись программисту SQL, да и просто админу который время от времени выгружает какие-то данные из базы данных, поэтому это относится к основам SQL и каждый человек, который имеет дело с SQL, должен иметь представление, что это такое.

Примечание! Все примеры будем писать в Management Studio SQL Server 2008.

Мы с Вами уже давно изучаем основы SQL, и если вспомнить начинали мы с оператора select, и вообще было уже много материала на этом сайте по SQL, например:

И много другого, даже уже рассматривали объединения union и union all, но, так или иначе, более подробно именно об объединениях join мы с Вами не разговаривали, поэтому сегодня мы восполним этот пробел в наших знаниях.

И начнем мы как обычно с небольшой теории.

Объединения JOIN — это объединение двух или более объектов базы данных по средствам определенного ключа или ключей или в случае cross join и вовсе без ключа. Под объектами здесь подразумевается различные таблицы, представления (views), табличные функции или просто подзапросы sql, т.е. все, что возвращает табличные данные.

Объединение SQL LEFT и RIGHT JOIN

LEFT JOIN – это объединение данных по левому ключу, т.е. допустим, мы объединяем две таблицы по left join, и это значит что все данные из второй таблицы подтянутся к первой, а в случае отсутствия ключа выведется NULL значения, другими словами выведутся все данные из левой таблицы и все данные по ключу из правой таблицы.

RIGHT JOIN – это такое же объединение как и Left join только будут выводиться все данные из правой таблицы и только те данные из левой таблицы в которых есть ключ объединения.

Теперь давайте рассматривать примеры, и для начала создадим две таблицы:

Вот такие простенькие таблицы, И я для примера заполнил их вот такими данными:

Теперь давайте напишем запрос с объединением этих таблиц по ключу number, для начала по LEFT:

Как видите, здесь данные из таблицы t1 вывелись все, а данные из таблицы t2 не все, так как строки с number = 4 там нет, поэтому и вывелись NULL значения.

А что будет, если бы мы объединяли по средствам right join, а было бы вот это:

Другими словами, вывелись все строки из таблицы t2 и соответствующие записи из таблицы t1, так как все те ключи, которые есть в таблице t2, есть и в таблице t1, и поэтому у нас нет NULL значений.

Объединение SQL INNER JOIN

Inner join – это объединение когда выводятся все записи из одной таблицы и все соответствующие записи из другой таблице, а те записи которых нет в одной или в другой таблице выводиться не будут, т.е. только те записи которые соответствуют ключу. Кстати сразу скажу, что inner join это то же самое, что и просто join без Inner. Пример:

А теперь давайте попробуем объединить наши таблицы по двум ключам, для этого немного вспомним, как добавлять колонку в таблицу и как обновить данные через update, так как в наших таблицах всего две колонки, и объединять по текстовому полю как-то не хорошо. Для этого добавим колонки:

Обновим наши данные, просто проставим в колонку number2 значение 1:

И давайте напишем запрос с объединением по двум ключам:

И результат будет таким же, как и в предыдущем примере:

Но если мы, допустим во второй таблице в одной строке изменим, поле number2 на значение скажем 2, то результат будет уже совсем другой.

Запрос тот же самый, а вот результат:

Как видите, по второму ключу у нас одна строка не вывелась.

Объединение SQL CROSS JOIN

CROSS JOIN – это объединение SQL по которым каждая строка одной таблицы объединяется с каждой строкой другой таблицы. Лично у меня это объединение редко требуется, но все равно иногда требуется, поэтому Вы также должны уметь его использовать. Например, в нашем случае получится, конечно, не понятно что, но все равно давайте попробуем, тем более синтаксис немного отличается:

Здесь у нас каждой строке таблицы test_table соответствует каждая строка из таблицы test_table_2, т.е. в таблице test_table у нас 4 строки, а в таблице test_table_2 3 строки 4 умножить 3 и будет 12, как и у нас вывелось 12 строк.

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

Как видите, я здесь объединяю и по left и по right и по inner просто, для того чтобы это было наглядно.

С объединениями я думаю достаточно, тем более ничего сложного в них нет. Но на этом изучение SQL не закончено в следующих статьях мы продолжим, а пока тренируйтесь и пишите свои запросы. Удачи!

Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.

Источник

Понимание джойнов сломано. Продолжение. Попытка альтернативной визуализации

Многие из вас читали предыдущую статью про то, как неправильная визуализация для объяснения работы JOIN-ов в некоторых случаях может запутать. Круги Венна не могут полноценно проиллюстрировать некоторые моменты, например, если значения в таблице повторяются.

При подготовке к записи шестого выпуска подкаста «Цинковый прод» (где мы договорились обсудить статью) кажется удалось нащупать один интересный вариант визуализации. Кроме того, в комментариях к изначальной статье тоже предлагали похожий вариант.

Все желающие приглашаются под кат

Итак, визуализация. Как мы выяснили в комментах к предыдущей статье, join — это скорее декартово произведение, чем пересечение. Если посмотреть, как иллюстрируют декартово произведение, то можно заметить, что зачастую это прямоугольная таблица, где по одной оси идет первое отношение, а по другой — второе. Таким образом элементы таблицы будут представлять собой все комбинации всего.

Сложно абстрактно это нарисовать, поэтому придется на примере.

Допустим, у нас есть две таблицы. В одной из них

Сразу disclaimer: я назвал поле словом «id» просто для краткости. Многие в прошлой статье возмущались, как это так — id повторяются, безобразие. Не стоит сильно переживать, ну
представьте, например, что это таблица с ежедневной статистикой, где для каждого дня и каждого юзера есть данные по посещению какого-нибудь сайта. В общем, не суть.

Итак, мы хотим узнать, что же получится при различных джойнах таблиц. Начнем с CROSS JOIN:

CROSS JOIN

CROSS JOIN — это все все возможные комбинации, которые можно получить из двух таблиц.

Визуализировать это можно так: по оси x — одна таблица, по оси y — другая, все клеточки внутри (выделены оранжевым) — это результат

INNER JOIN

INNER JOIN (или просто JOIN) — это тот же самый CROSS JOIN, у которого оставлены только те элементы, которые удовлетворяют условию, записанному в конструкции «ON». Обратите внимание на ситуацию, когда записи дублируются — результатов с единичками будет четыре штуки.

LEFT JOIN

LEFT OUTER JOIN (или просто LEFT JOIN) — это тоже самое, что и INNER JOIN, но дополнительно мы добавляем null для строк из первой таблицы, для которой ничего не нашлось во второй

RIGHT JOIN

RIGHT OUTER JOIN ( или RIGHT JOIN) — это тоже самое, что и LEFT JOIN, только наоборот. Т.е. это INNER JOIN + null для строк из второй таблицы, для которой ничего не нашлось в первой

→ Поиграть с запросами можно здесь

Выводы

Вроде бы получилась простая визуализация. Хотя в ней есть ограничения: здесь показан случай, когда в ON записано равенство, а не что-то хитрое (любое булево выражение). Кроме того не рассмотрен случай, когда среди значений таблицы есть null. Т.е. это всё равно некоторое упрощение, но вроде бы получилось лучше и точнее, чем круги Венна.

Подписывайтесь на наш подкаст «Цинковый прод», там мы обсуждаем базы данных, разработку софта и прочие интересные штуки.

Источник

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

SQL оператор JOINS

В этом учебном материале вы узнаете, как использовать SQL JOINS с синтаксисом и примерами.

Описание

SQL JOINS используются для извлечения данных из нескольких таблиц. SQL JOIN выполняется всякий раз, когда две или более таблицы перечислены в операторе SQL.

Существует 4 различных типа соединений SQL:

Итак, давайте обсудим синтаксис SQL JOIN, рассмотрим наглядные иллюстрации SQL JOINS и рассмотрим несколько примеров.

SQL INNER JOIN (простое соединение)

Скорее всего, вы уже писали SQL запрос, который использует SQL INNER JOIN. Это наиболее распространенный тип соединения SQL. INNER JOIN возвращает все строки из нескольких таблиц, где выполняется условие соединения.

Синтаксис

Синтаксис INNER JOIN в SQL:

Рисунок.

Пример

Давайте рассмотрим пример использования INNER JOIN в запросе.

В этом примере у нас есть таблица customer и следующими данными:

И таблица orders со следующими данными:

Выполним следующий SQL оператор:

Будет выбрано 4 записи. Вот результаты, которые вы должны получить:

SQL LEFT OUTER JOIN

Другой тип соединения называется LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие соединения).

Синтаксис

Синтаксис для LEFT OUTER JOIN в SQL:

В некоторых базах данных ключевое слово OUTER опущено и записывается просто как LEFT JOIN.

Рисунок

Пример

Теперь давайте рассмотрим пример, который показывает, как использовать LEFT OUTER JOIN в операторе SELECT.

И таблицу orders со следующими данными:

Введите следующий SQL оператор:

Будет выбрано 6 записей. Вот результаты, которые вы получите:

SQL RIGHT OUTER JOIN JOIN

Другой тип соединения называется SQL RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие соединения).

Синтаксис

Синтаксис для RIGHT OUTER JOIN в SQL:

В некоторых базах данных ключевое слово OUTER опущено и записывается просто как RIGHT JOIN.

Рисунок

На этом рисунке SQL RIGHT OUTER JOIN возвращает затененную область:

Пример

Теперь давайте рассмотрим пример, который показывает, как использовать RIGHT OUTER JOIN в операторе SELECT.

И таблицу orders со следующими данными:

Введите следующий SQL оператор:

Будет выбрано 5 записей. Вот результаты, которые вы должны получить:

SQL FULL OUTER JOIN

Другой тип объединения называется SQL FULL OUTER JOIN. Этот тип объединения возвращает все строки из LEFT таблицы и RIGHT таблицы со значениями NULL в месте, где условие соединения не выполняется.

Синтаксис

Синтаксис для SQL FULL OUTER JOIN:

В некоторых базах данных ключевое слово OUTER опускается и записывается просто как FULL JOIN.

Рисунок

Пример

Давайте рассмотрим пример, который показывает, как использовать FULL OUTER JOIN в операторе SELECT.

И таблицу orders со следующими данными:

Введите следующий SQL оператор:
SELECT customers.customer_id,
orders.order_id,
orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

Будет выбрано 7 записей. Вот результаты, которые вы получите:

Как видите, строки, где customer_id равен 6000 и 9000, будут включены, но поля order_id и order_date для этих записей содержат значение NULL. Строка, где order_id равен 5, также будет включена, но поле customer_id для этой записи имеет значение NULL.

Источник

SQL JOIN: руководство по объединению таблиц. Часть 2

Перевод второй части статьи «SQL Joins Tutorial: Cross Join, Full Outer Join, Inner Join, Left Join, and Right Join».

Продолжаем разбираться с видами JOIN в SQL.

В первой части статьи мы рассмотрели:

LEFT JOIN / RIGHT JOIN (левое и правое объединение)

Следующие два вида объединений используют модификатор (LEFT или RIGHT), который определяет, какие данные таблицы включаются в результирующий набор.

Примечание: LEFT JOIN и RIGHT JOIN также называются LEFT OUTER JOIN и RIGHT OUTER JOIN (соответственно левое и правое внешнее объединение).

Эти виды объединений используются в запросах, когда мы хотим вернуть все данные из одной таблицы и добавить к ним связанные данные из другой таблицы (если таковые есть).

Если связанных данных во второй таблице нет, мы получаем в выводе все данные только из «первичной» таблицы.

То есть, это запрос информации о конкретной вещи с дополнительными сведениями, если они есть.

Понять это будет проще на примере. Давайте найдем все фильмы с их режиссерами, но не будем зацикливаться на последних: если режиссер есть, это дополнительная информация, а нет — так нет.

Этот запрос практически идентичен тому, что мы писали ранее. Единственная разница в том, что мы указали тип объединения LEFT JOIN.

В нашем примере таблица movies является «левой».

Если написать запрос в одну строчку, «левизну» будет проще заметить:

LEFT JOIN возвращает все записи из «левой» таблицы. Из «правой» возвращаются только те, что соответствуют условию объединения. Строки «правой» таблицы, не соответствующие условию объединения, возвращаются как NULL.

На этом примере мы видим, что запрос LEFT JOIN полезен для случаев, когда нам нужно получить «все из этого и, если есть, что-нибудь из того».

RIGHT JOIN

Правое объединение работает точно так же, как и левое, за исключением того, что таблицы как бы меняются местами.

При правом объединении возвращаются все строки «правой» таблицы. А строки «левой» добавляются, только если отвечают заданному условию.

Давайте используем предыдущий запрос, в котором заменим LEFT JOIN на RIGHT JOIN:

В результирующем наборе мы видим строки со всеми режиссерами, к которым добавлены строки с их фильмами (если есть).

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

LEFT JOIN / RIGHT JOIN в реальной жизни

В приложениях, поступающих в производство, я всегда использую только LEFT JOIN и никогда RIGHT JOIN.

Я так делаю, потому что с LEFT JOIN запрос легче читать и проще понять.

Составляя запросы, я предпочитаю начинать с «базового» набора результатов, в нашем примере это все фильмы, а затем добавлять что-то в этот набор (или убирать из него).

Для такого подхода лучше при менять LEFT JOIN. Я хочу получать все строки из «левой» таблицы, а из правой — только соответствующие условиям.

На практике я, кажется, ни разу не видел использование RIGHT JOIN в приложении, идущем в производство. В правом объединении нет ничего плохого, просто лично я считаю, что такой запрос хуже читается.

Переписывание RIGHT JOIN

Если мы хотим «перевернуть» наш сценарий и вернуть строки со всеми режиссерами, добавив к ним фильмы (если есть), мы с легкостью можем превратить RIGHT JOIN в LEFT JOIN.

Все что нужно сделать — изменить порядок таблиц в запросе и поменять RIGHT на LEFT:

Примечание: я предпочитаю в условии сначала указывать таблицу, данные из которой являются добавочными («правую» таблицу, в примере — movies) — ( ON movies.director_id = … ), но это мои личные предпочтения.

Фильтрация с использованием LEFT JOIN

LEFT JOIN (или RIGHT JOIN) имеют два сценария использования.

Первый мы уже рассмотрели: вывод всех строк из одной таблицы и вывод некоторых (соответствующих условию) из второй.

Второй сценарий использования — вывод тех строк из первой таблицы, для которых во второй таблице нет соответствий.

Если говорить о нашем примере, то этот сценарий звучит так: «найти всех режиссеров, которые не связаны с имеющимися фильмами».

Чтобы это сделать, мы начинаем с LEFT JOIN, а наша таблица directors будет первичной («левой»):

Для режиссеров, не имеющих фильмов, столбцы из таблицы movies будут заполнены значениями NULL:

В нашем примере режиссеры с ID 3, 4 и 5 не имеют фильмов.

Чтобы отфильтровать результаты и получить только эти строки, мы можем добавить оборот WHERE. Благодаря ему будут возвращены только те строки, где данные о фильмах — NULL:

А это как раз три наших режиссера без фильмов!

Для фильтрации часто используется именно столбец id (WHERE movies.id IS NULL), но на самом деле в нашем случае все столбцы из таблицы movies имеют значение NULL, так что в обороте WHERE мог быть указан любой из них.

(Поскольку мы знаем, что все столбцы из таблицы movies будут NULL, то чтобы вернуть всю информацию о режиссерах, в запросе мы могли бы написать SELECT directors.*, а не SELECT *).

Использование LEFT JOIN для поиска соответствий

В нашем предыдущем примере мы искали режиссеров, у которых нет фильмов.

Используя ту же структуру, мы могли бы найти режиссеров, у которых фильмы есть. Для этого нужно изменить условие WHERE и искать строки, где данные о фильмах не NULL:

Это может казаться удобным, но по факту мы всего лишь заново реализовали INNER JOIN!

Множественные объединения

Мы рассмотрели, как можно объединить строки двух таблиц, а как насчет множественных объединений в одной строке?

Собственно, тут все достаточно просто, но чтобы это проиллюстрировать, нам понадобится третья таблица: tickets (билеты).

Эта таблица будет представлять проданные билеты по каждому фильму:

Таблица tickets содержит только id и ссылку на фильм: movie_id.

Мы также добавили сведения о том, что на фильм с ID 1 было продано два билета, а на фильм с ID 3 — один билет.

Теперь давайте объединим directors с movies, а movies — с tickets!

Поскольку это внутренние объединения, порядок написания JOIN-ов не имеет значения. Мы могли бы начать с tickets, добавить movies, а потом добавить directors.

Опять же, все зависит от того, что именно вы хотите запросить. На основе этого вы и формируете свой запрос так, чтобы он был максимально понятным.

В нашем результирующем наборе мы увидим, что мы еще больше сузили поиск и получили меньше строк:

Это логично, потому что мы добавили еще одно внутреннее объединение. В результате в запросе появился дополнительное условие «AND».

В нашем запросе говорится: «Верни всех режиссеров, связанных с фильмами, по которым были продажи билетов».

Если бы мы хотели найти режиссеров, у которых есть фильмы, но по этим фильмам не обязательно уже были проданы билеты, мы могли бы заменить наш последний INNER JOIN на LEFT JOIN:

Мы видим, что теперь в результаты попал и Movie 2:

По этому фильму ни одного билета не продано, поэтому в предыдущей выборке, когда применялся INNER JOIN, его не было.

Теперь вопрос: как нам найти режиссеров, связанных с фильмами, по которыми продаж не было?

Порядок выполнения объединений

Наконец, нам совершенно безразлично, в каком порядке выполняются операции объединения.

Одно из ключевых отличий SQL от современных языков программирования состоит в том, что SQL это декларативный язык.

Это означает, что мы определяем, какой итог мы хотим получить, но не определяем детали выполнения. Этими деталями занимается планировщик запросов базы данных. Мы указываем нужные нам объединения и их условия, а планировщик запросов занимается всем остальным.

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

(Примечание: это несколько упрощенное объяснение).

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

Операции объединения с дополнительными условиями

Последняя тема, которую мы рассмотрим, это объединение с дополнительными условиями.

Так же, как раньше мы добавляли оборот WHERE, мы можем добавлять сколько угодно условий.

Например, если мы хотим найти фильмы с режиссерами, которых зовут не John Smith, мы можем добавить дополнительное условие при помощи AND:

Мы можем использовать любые операторы, которые могли бы поставить в оборот WHERE.

Собственно, мы могли бы получить такой же результат, если бы поместили условия внутрь оборота WHERE:

Тут есть некоторая разница в происходящем под капотом, но для целей нашей статьи результат можем считать одинаковым.

Правда жизни о написании запросов с объединениями

В реальной жизни я сталкивался с операциями объединения в трех разных видах:

INNER JOIN

Первый случай использования — записи с существующими отношениями между таблицами. Здесь применяется INNER JOIN.

Это ситуации вроде поиска «фильмов, для которых указаны режиссеры» или «пользователей, у которых есть посты».

LEFT JOIN

Второй случай использования — получение записей из одной таблицы и, если отношения с записями в другой таблице существуют, — записей из второй таблицы. Здесь применяется LEFT JOIN.

Это ситуации вроде «вывести фильмы и имена режиссеров, если для этих фильмов режиссеры указаны» или «вывести пользователей, а если у них есть посты, то и посты тоже вывести».

LEFT JOIN для исключения

Третий самый распространенный случай использования — это еще один случай для использования LEFT JOIN: поиск в первой таблице записей, для которых нет отношений во второй таблице.

Это ситуации вроде «фильмы без режиссеров» или «пользователи без постов».

Два очень полезных вида JOIN-ов

Мне кажется, я вообще ни разу не использовал FULL OUTER JOIN или RIGHT JOIN в приложениях, идущих в продакшен. Либо подходящие случаи встречаются редко, либо такой запрос можно написать более понятно (это касается, в частности, RIGHT JOIN).

Иногда мне случается использовать CROSS JOIN для таких вещей как распределение записей по диапазону дат (мы рассматривали пример в первой части статьи), но этот сценарий возникает не слишком часто.

В общем, хорошие новости! На самом деле есть только два вида JOIN-ов, в которых нужно разобраться и которые покрывают 99,9% use cases: INNER JOIN и LEFT JOIN!

Источник