Для чего нужен inner join sql

Объяснение SQL объединений JOIN: LEFT/RIGHT/INNER/OUTER

Разберем пример. Имеем две таблицы: пользователи и отделы.

В результате отсутствуют:

Внутреннее объединение INNER JOIN (синоним JOIN, ключевое слово INNER можно опустить).

Выбираются только совпадающие данные из объединяемых таблиц.

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

Такое объединение вернет данные из обеих таблиц (совпадающие по условию объединения) ПЛЮС дополнит выборку оставшимися данными из внешней таблицы, которые по условию не подходят, заполнив недостающие данные значением NULL.

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

в выборке останется только 3#Александр, так как у него не назначен департамент.

рис. Left outer join с фильтрацией по полю

RIGHT OUTER JOIN вернет полный список департаментов (правая таблица) и сопоставленных пользователей.

Дополнительно можно отфильтровать данные, проверяя их на NULL.

В нашем примере указав WHERE u.id IS null, мы выберем департаменты, в которых не числятся пользователи. (3#Финансы)

Все примеры вы можете протестировать здесь:

Cross/Full Join

FULL JOIN возвращает `объединение` объединений LEFT и RIGHT таблиц, комбинируя результат двух запросов.

CROSS JOIN возвращает перекрестное (декартово) объединение двух таблиц. Результатом будет выборка всех записей первой таблицы объединенная с каждой строкой второй таблицы. Важным моментом является то, что для кросса не нужно указывать условие объединения.

Дублирование строк при использовании JOIN

При использовании объединения новички часто забывают что результирующая выборка может содержать дублирующиеся данные!
Если вам нужна одна запись, делайте объединение с подзапросом

Self Join

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

Рассмотрим задачку от яндекса:

Есть таблица товаров.

Она содержит следующие значения.

Напишите запрос, выбирающий уникальные пары `id` товаров с одинаковыми `name`, например:

При решении задачи необходимо учесть, что пары (x,y) и (y,x) — одинаковы.

— или без группировки (быстрее)

Объединяем таблицы ya_goods по одинаковому полю `name`, группируем по уникальным idентификаторам и получаем результат.

Множественное объединение multi join

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

Пример: выбрать товары,
добавленные после 17/01/2009 в следующих вариантах:
— вес=310, объем=300
— вес=35, объем=15
— вес=45, объем=25
— вес=200, объем=250

INNER JOIN product2options p2o1 ON p.id = p2o1.product_id
INNER JOIN product_options po1 ON po1.id = p2o1.option_id

INNER JOIN product2options p2o2 ON p.id = p2o2.product_id
INNER JOIN product_options po2 ON po2.id = p2o2.option_id

id title created_at P1 value P2 value
2 Ложка 2009-01-18 20:00:00 Вес 35 Объем 15
3 Тарелка 2009-01-19 20:00:00 Вес 310 Объем 300
2 Ложка 2009-01-18 20:00:00 Вес 45 Объем 25

— не попадает по дате
1 Кружка 2009-01-17 20:00:00 Объем 250 Вес 200

UPDATE и JOIN

Объединение можно использовать совместно с UPDATE.
Например, имеем таблицу houses (id, title, area). Нужно выбрать title, если в нем встречается `число м2`, заменить поле area, если оно меньше. Т.к. в mysql отстутсутствует поддержка регулярных выражений, нужно немного поколдовать с locate и substr.
В подзапросе выбираем интересующие нас данные, и в финальной стадии осуществляем обновление данных подходящий по критерию (p5 > area).

FROM ga_pageviews
WHERE title like ‘ % м2 % ‘
) calc USING ( `id` )
SET base. area = calc.p5
WHERE base. area calc.p5

DELETE и JOIN

Рассмотрим пример с удалением дубликатов. Есть таблица tableWithDups (id, email). Нужно удалить строки с одинаковыми email:

Последние два примера не совместимы с ANSI SQL, но работают в mySQL.

За бортом статьи остались смежные объединениям (а также специфичные для определенных базданных темы):
SELF JOIN, FULL OUTER JOIN, CROSS JOIN (CROSS [OUTER] APPLY), операции над множествами UNION [ALL], INTERSECT, EXCEPT и т.д.

@tags: sql, mysql, sql server, oracle, sqlite, postgresql

Источник

Операция INNER JOIN (Microsoft Access SQL)

Область применения: Access 2013, Office 2013

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

Синтаксис

FROM таблица1 INNER JOIN таблица2 ON таблица1.поле1 оператор_сравнения таблица2.поле2

Операция INNER JOIN состоит из следующих элементов:

Имена таблиц, содержащих объединяемые записи.

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

Примечания

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

При работе с таблицами «Отделы» и «Сотрудники» операцией INNER JOIN можно воспользоваться для выбора всех сотрудников в каждом отделе. Если же требуется выбрать все отделы (включая те из них, в которых нет сотрудников) или всех сотрудников (в том числе и не закрепленных за отделом), можно при помощи операции LEFT JOIN или RIGHT JOIN создать внешнее соединение.

При попытке связи полей, содержащих данные типа Memo или объекты OLE, возникнет ошибка.

Можно связать любые два числовых поля аналогичных типов. Например, можно связать поля AutoNumber и Long, так как эти типы аналогичны, однако не поля Single и Double.

В следующем примере показано, как можно объединить таблицы Categories и Products по полю CategoryID.

В предыдущем примере CategoryID является объединенным полем, но оно не включается в результаты запроса, поскольку не указано в инструкции SELECT. Чтобы включить объединенное поле в результаты запроса, добавьте его имя в инструкцию SELECT. В данном случае это Categories.CategoryID.

В инструкции JOIN можно также связать несколько предложений ON, используя следующий синтаксис:

Ниже приведен пример синтаксиса, с помощью которого можно составлять вложенные инструкции JOIN.

SELECT поля FROM таблица1 INNER JOIN (таблица2 INNER JOIN [( ]таблица3 [INNER JOIN [( ]таблицаx [INNER JOIN …)] ON таблица3.поле3 оператор_сравнения таблицаx.полеx)] ON таблица2.поле2 оператор_сравнения таблица3.поле3) ON таблица1.поле1 оператор_сравнения таблица2.поле2;

Операции LEFT JOIN и RIGHT JOIN могут быть вложены в операцию INNER JOIN, но операция INNER JOIN не может быть вложена в операцию LEFT JOIN или RIGHT JOIN.

Пример

В этом примере создается два уравнивающих соединения: одно между таблицами Order Details (Сведения о заказах) и Orders (Заказы), а другое между таблицами Orders (Заказы) и Employees (Сотрудники). Это необходимо, так как таблица Employees (Сотрудники) не содержит данные о продажах, а таблица Order Details (Сведения о заказах) не содержит данные сотрудников. Результат запроса представляет собой список сотрудников и их общие объемы продаж.

В этом примере вызывается процедура EnumFields, которую можно найти в примере инструкции SELECT.

Источник

Понимание джойнов сломано. Это точно не пересечение кругов, честно

Так получилось, что я провожу довольно много собеседований на должность веб-программиста. Один из обязательных вопросов, который я задаю — это чем отличается 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. Это и читабельнее, и быстрее.

Выводы

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

Как объяснить на картинке джойны корректно, я, честно говоря, не представляю. Если вы знаете — расскажите, плиз, и киньте в коменты. А мы обсудим это в одном из ближайших выпусков подкаста «Цинковый прод». Не забудьте подписаться.

Источник

INNER JOIN простыми словами

Перевод статьи «SQL INNER JOIN Explained in Simple Words».

Как известно, база данных хранит данные в таблицах. Скорее всего вы уже умеете писать запросы к одной таблице. Но что если требуется поработать с несколькими таблицами? Чтобы комбинировать данные из двух и более таблиц, нужно воспользоваться оператором JOIN. Сегодня на примерах разберемся, как работает INNER JOIN.

JOIN для двух таблиц

Наша тренировочная база данных будет состоять из двух таблиц: TRAVEL_PACK и CITIES:

TRAVEL_PACK

CITIES

Оператор JOIN объединяет записи таблиц по общему полю или колонке (т.е. такая колонка должна быть в каждой из таблиц). В нашем случае у нас есть колонка PACK_NAME в таблице TRAVEL_PACK и точно такая же — в таблице CITIES.

Легко заметить, что в обеих таблицах эта колонка называется одинаково. Но это не обязательно, и в других базах данных может быть иначе. Другими словами, при использовании оператора INNER JOIN названия общей колонки в таблицах могут совпадать, но могут и отличаться.

Первый JOIN — найдем путевки по приморским городам

Залог хорошего SQL-запроса — четкое понимание того, где хранятся нужные данные. В нашем случае очевидно, что для поиска названий путёвок понадобится TRAVEL_PACK, а проверить, находится ли город у моря, можно в таблице CITIES.

Комбинировать данные из двух таблиц можно с помощью конструкции JOIN. Она создает связь между таблицами и помогает отобразить данные из них единовременно. Условно говоря, из двух строк отдельных таблиц JOIN создает одну целую. Такой тип соединения называется INNER JOIN (впрочем, он является синонимом для JOIN). Наряду с этим видом часто используют LEFT, RIGHT, OUTER и много других типов JOIN.

Вот как JOIN создаёт соединённые строки с данными:

Синтаксис запроса с JOIN:

Подставим в эту схему названия таблиц и колонок из тренировочной базы данных:

После секции FROM с INNER JOIN можно переходить к написанию SELECT с WHERE :

Обратите внимание, что обращение к колонке PACK_NAME сделано с добавлением названия таблицы (TRAVEL_PACK). Это хорошая практика для случаев, когда в запросе задействовано больше одной таблицы.

При помощи оператора WHERE отфильтруем результаты, чтобы остались только приморские города:

В результате должен получится вот такой запрос:

А результатом его выполнения будет такой датасет:

По образу и подобию предыдущего запроса можно обратиться к двум известным таблицам CITIES и TRAVEL_PACK и соединить их с помощью INNER JOIN (далее JOIN и INNER JOIN будут использоваться взаимозаменяемо).

В фильтрации WHERE ограничим набор данных по стоимости:

Полностью запрос будет выглядеть так:

А вернёт он такие данные:

Глубокое погружение: INNER JOIN для трёх таблиц

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

Обратимся к практическому примеру. Добавим в базу данных новую таблицу STATES, в которой будем хранить данные о государствах, регионах и провинциях разных стран.

TRAVEL_PACK

STATES

CITIES

Запросим все туристические города из регионов, говорящих на испанском и португальском. Понятно, что нужно соединить таблицы CITIES и STATES, а затем приджоинить к ним TRAVEL_PACK. Приступим к решению задачи и используем знания из предыдущих частей этой статьи.

Во-первых, сделаем JOIN для таблиц CITIES и STATES по колонкам CITIES.STATE и STATE.NAME :

Сделаем JOIN с третьей таблицей TRAVEL_PACK:

Отполируем запрос с помощью SELECT и WHERE :

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

От редакции Techrocks: Если хотите изучить не только INNER JOIN, но и другие виды объединений, обратите внимание на статью “SQL JOIN: руководство по объединению таблиц”.

Источник

Соединение таблиц – операция JOIN и ее виды

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

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

Придумаем 2 таблицы, на которых будем тренироваться.

Таблица «Сотрудники», содержит поля:

Таблица «Отделы», содержит поля:

Давайте уже быстрее что-нибудь покодим.

INNER JOIN

Самый простой вид соединения INNER JOIN – внутреннее соединение. Этот вид джойна выведет только те строки, если условие соединения выполняется (является истинным, т.е. TRUE). В запросах необязательно прописывать INNER – если написать только JOIN, то СУБД по умолчанию выполнить именно внутреннее соединение.

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

Получим следующий результат:

Из результатов пропал сотрудник Алексей (id = 3), потому что условие «Сотрудники.Отдел = Отделы.id» не будет истинно для этой сроки из таблицы «Сотрудники» с каждой строкой из таблицы «Отделы». По той же логике в результате нет отдела «Администрация». Попробую это визуализировать (зеленные линии – условие TRUE, иначе линия красная):

Если не углубляться в то, как внутреннее соединение работает под капотом СУБД, то происходит примерно следующее:

Если для одной или нескольких срок из левой таблицы (в рассмотренном примере левой таблицей является «Сотрудники», а правой «Отделы») истинным условием соединения будут являться одна или несколько срок из правой таблицы, то строки умножат друг друга (повторятся). В нашем примере так произошло для отдела с поэтому строка из таблицы «Отделы» повторилась дважды для Федора и Светланы.
Перемножение таблиц проще ощутить на таком примере, где условие соединения будет всегда возвращать TRUE, например 1=1:

В результате получится 12 строк (4 сотрудника * 3 отдела), где для каждого сотрудника подтянется каждый отдел.

Также хочу сразу отметить, что в соединении может участвовать сколько угодно таблиц, можно таблицу соединить даже саму с собой (в аналитических задачах это не редкость). Какая из таблиц будет правой или левой не имеется значения для INNER JOIN (для внешних соединений типа LEFT JOIN или RIGHT JOIN это важно. Читайте далее). Пример соединения 4-х таблиц:

Как видите, все просто, прописываем новый джойн после завершения условий предыдущего соединения. Обратите внимание, что для Table_3 указано несколько условий соединения с двумя разными таблицами, а также Table_1 соединяется сама с собой по условию с использованием сложения.
Строки, которые выведутся запросом, должны совпасть по всем условиям. Например:

На этом про внутреннее соединение и логику соединения таблиц в SQL – всё. Если остались неясности, то спрашивайте в комментариях.
Далее рассмотрим отличия остальных видов джойнов.

LEFT JOIN и RIGHT JOIN

Левое и правое соединения еще называют внешними. Главное их отличие от внутреннего соединения в том, что строка из левой (для LEFT JOIN) или из правой таблицы (для RIGHT JOIN) попадет в результаты в любом случае. Давайте до конца определимся с тем, какая таблица левая, а какая правая.
Левая таблица та, которая идет перед написанием ключевых слов [LEFT | RIGHT| INNER] JOIN, правая таблица – после них:

Теперь изменим наш SQL-запрос из самого первого примера так, чтобы ответить на вопрос «В каких отделах работают сотрудники, а также показать тех, кто не распределен ни в один отдел?»:

Результат запроса будет следующим:

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

Это мы рассмотрели пример для левого внешнего соединения. Для RIGHT JOIN будет все тоже самое, только вернутся все строки из таблицы «Отделы»:

Алексей «потерялся», Администрация «нашлась».

Вопрос для Вас. Что надо изменить в последнем приведенном SQL-запросе, чтобы результат остался тем же, но вместо LEFT JOIN, использовался RIGHT JOIN?

Ответ. Нужно поменять таблицы местами:

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

FULL JOIN

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

Давайте посмотрим всех сотрудников и все отделы из наших тестовых таблиц:

Теперь мы видим все, даже Алексея без отдела и Администрацию без сотрудников.

Вместо заключения

Помните о порядке выполнения соединений и порядке таблиц, если используете несколько соединений и используете внешние соединения. Можно выполнять LEFT JOIN для сохранения всех строк из самой первой таблицы, а последним внутренним соединением потерять часть данных. На маленьких таблицах косяк заметить легко, на огромных очень тяжело, поэтому будьте внимательны.
Рассмотрим последний пример и введем еще одну таблицу «Банки», в которой обслуживаются наши придуманные сотрудники:

В таблицу «Сотрудники» добавим столбец «Банк»:

Теперь выполним такой запрос:

В результате потеряли информацию о Светлане, т.к. для нее не нашлось банка с (такое происходит из-за неправильной проектировки БД):

Хочу обратить внимание на то, что любое сравнение с неизвестным значением никогда не будет истинным (даже NULL = NULL). Эту грубую ошибку часто допускают начинающие специалисты. Подробнее читайте в статье про значение NULL в SQL.

Пройдите мой тест на знание основ SQL. В нем есть задания на соединения таблиц, которые помогут закрепить материал.

Дополнить Ваше понимание соединений в SQL могут схемы, изображенные с помощью кругов Эйлера. В интернете много примеров в виде картинок.

Если какие нюансы джойнов остались не раскрытыми, или что-то описано не совсем понятно, что-то надо дополнить, то пишите в комментариях. Буду только рад вопросам и предложениям.

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

Источник