JOIN - крайне популярная операция в SQL, о которой еще и спрашивают на 99% собеседований на программиста. Но когда начинаешь впервые разбираться с ней, то постоянно путаешься, какие таблицы соединять и когда именно.
В этой статье простыми словами и с великолепной графикой расскажу, что такое JOIN в SQL, что такое Foreign Key, какой тип JOIN когда использовать - INNER или OUTER - и зачем вообще.
Что такое SQL база данных?
JOIN-ы возможны в SQL базах данных, благодаря тому, как эти базы хранят информацию. Быстро разберемся с этим простыми словами, чтобы идеально понять JOIN-ы!
База данных - это просто программа, которая умеет в удобном формате сохранять информацию, что мы ей даем, на жесткий диск компьютера, где запущена. А потом и доставать эти данные обратно, когда потребуется.
Примечание: существуют различные SQL базы данных: PostgreSQL, MySQL, Oracle и т.д. Для обучения вы можете выбрать и установить любую из них. Я рекомендую PostgreSQL, как наиболее популярную.
Но что за формат данных удобен для такого хранения?
Тот, который понятен человеку! Данные - это по сути текст с информацией о чем-либо. Например, мы хотим хранить в базе информацию о пиве принесенном друзьями на вечеринку. И для такой статистики нет ничего лучше таблицы!
Таблицы просты и понятны - мы со школы с ними знакомы. И распределять кусочки информации по разным их колонками тоже супер легко. Поэтому люди решили, что нужно создать программы, которые будут хранить любую информацию на компьютере именно в виде таблиц, чтобы нам было удобно в них ориентироваться.
Именно так и появились SQL базы данных. А SQL они потому, что используют язык SQL для создания таких таблиц, сохранения и получения из них данных. На SQL пользователь, который хочет что-то сохранить, пишет запросы-инструкции для таких программ-баз данных, чтобы они выполнили какое-то действие.
Например, такой запрос создает новую пустую таблицу для приглашенных на вечеринку c названием "участники" в нашей базе данных:
CREATE TABLE participants ( name VARCHAR(64), beer VARCHAR(64), volume REAL );
Такой - добавляет в эту таблицу новую строчку с данными:
INSERT INTO participants (name, beer, volume) VALUES ('Влад', 'Жигулевское', 0.5);
А такой - достает из этой таблицы все строчки, что были ранее сохранены на жесткий диск:
SELECT name, beer, volume FROM participants;
Что значит "реляционные" базы данных?
Но создатели SQL баз данных сделали их очень хитро!
Если создать таблицу со статистикой выпитого пива, то для каждого участника нашей вечеринки мы могли бы добавить по строчке с соответствующими данными.
Например, в нашем новом приложении для вечеринок мы храним информацию о том, что за человек был на мероприятии, какое пиво он принес и сколько именно. Предположим, что я взял "Жигулевское", а мои друзья - Петрович и Дядь Валера - "БагБир". Импортное в последнее время берут почему-то.
Но получается, что название их пива мы написали в этой таблице дважды. Буквально одно и то же! А ведь эти данные будут храниться на жестком диске нашего компьютера. Каждая из этих строчек занимает определенный объем памяти. Кажется, что это мелочь?
Но что если мы собираем информацию со всех тусовок мира, где пьют пиво? Получается, что для каждого участника мы будем постоянно дублировать название, которое уже могло встречаться у других, постоянно отщипывая кусочек памяти на жестком диске на хранение… Того же самого!
В итоге мы просто забиваем память одними и теми же данными и тратим драгоценные ресурсы компьютера на это. А они ведь не бесплатные! Если данных будет много, то нам понадобится и жесткий диск побольше. А он и стоит дороже...
Допустить такое расточительство создатели SQL баз данных не могли и потому придумали простую и гениальную вещь! Они сказали:
“А давайте все дублирующиеся данные выносить в отдельную таблицу и удалять дублирование. А потом каждую строчку из первой таблицы связывать со строчкой в новой таблице, указывая таким образом, какое пиво относится к какому участнику вечеринки".
Этот пример с пивом как раз и использовался при разработке баз данных их создателями. Проблема серьезная - сами понимаете.
И что получилось? Теперь никакие данные не дублируются! Глядя на каждого участника мы сразу можем понять, какое пиво он взял, потому что между двумя таблицами есть связь, которая на это и указывает. А еще нет никакого дублирования данных: и Дядь Валера, и Петрович относятся к единственной строчке во второй таблице - к той же самой!
Слово относиться на английский переводится как relate. И как раз поэтому такие базы данных назвали реляционными. Потому что они позволяют строить вот такие отношения между строчками в разных таблицах. Пиво "Жигулевское" относится к участнику "Влад".
Что такое Foreign Key?
Как это все связано с JOIN? Мы уже совсем близко к разгадке, и вы удивитесь, как легко вы поймете JOIN-ы, после того, что узнали теперь!
Но как сказать нашей базе данных, что конкретная строчка в одной таблице относится к вообще другой строчке в вообще другой таблице? Для этого используются внешние ключи! Или по-английски foreign keys!
По канонам SQL у каждой строчки в любой таблице должен быть уникальный ключ, по которому эту строчку можно быстро найти среди всех остальных. Ключом может быть что угодно - например, число. Каждая строчка в таблице получает уникальный номер, и тогда если вы знаете только его, то всегда можете найти всю строку, что ему соответствует.
Но эти номера делают таблицы еще мощнее! Они-то как раз и позволяют прокладывать связи между разными таблицами!
Если мы возьмем таблицу участников вечеринки, то каждую строчку в ней нам нужно как-то связать с той или иной строчкой в таблице пива. Что если теперь мы добавим в таблицу участников новую колонку, которую назовем, например, beer_id или “номер пива”.
Тогда для каждой строчки в таблице участников в эту колонку мы можем добавить номер того пива, которое он взял. Я взял пиво под номером 1 - "Жигулевское", а мои друзья - под номером 2 - "БагБир", так ведь?
Теперь, если посмотреть на любую строчку из нашей таблицы участников, то можно сразу понять, какое именно пиво они взяли. Буквально берем номер этого пива, а потом в соответствующей таблице находим те данные, что соответствуют этому номеру. Вот мы и проложили связь!
Колонки таблиц, в которых хранятся номера строчек из других таблиц, с которыми есть связь у этой таблицы, как раз и называются внешними ключами или foreign keys по-английски. Это всего навсего колонка-указатель на строчку в другой таблице!
Примечание: да, в этом случае данные все равно дублируются, т.к. мы имеем число 2 в колонке beer_id сразу для двух строк. Тем не менее, это число занимает гораздо меньше памяти, чем все данные о пиве. А потому такое дублирование намного оптимальнее.
Что такое JOIN?
И именно благодаря внешним ключам в SQL возможны JOIN-ы. Вот мы к ним и подошли, сейчас начнется самое интересное! Что такое JOIN и зачем вообще?
Ситуация простая. Представьте, что вам нужно из такой базы данных получить информацию обо всех участниках вечеринки и о том, какие напитки они туда принесли.
Что ж, участники у нас хранятся в первой таблице, поэтому мы могли бы написать простой SELECT запрос на языке SQL, чтобы вытащить оттуда все нужные данные.
SELECT * FROM participants;
Примечание: символ * - это сокращение для перечисления всех колонок таблицы, из которой делается выборка.
Такой запрос выдаст:
Но мы видим, что в полученной таблице нет информации о пиве, только внешний ключ на таблицу с пивом.
А нам нужно, чтобы запрос возвращал все вместе. И информацию об участниках, и их пиво, даже если оно в другой таблице лежит. Нам нужно как-то… Соединить данные из двух таблиц при получении!
Мы хотели бы увидеть такой результат:
Слово соединять на английский переводится, как join. И именно для соединения двух таблиц в одном запросе и применяется это слово в SQL. Как же оно работает?
Когда мы пишем SELECT запрос в одну таблицу, и нам в результате выборки кроме ее колонок нужны еще колонки из другой таблицы, с которой эта первая связана, то в самом запросе мы можем написать слово JOIN, а затем указать имя таблицы, которую хотим присоединить к выборке из первой.
SELECT * FROM participants JOIN beer ...
Но тут есть деталь. В первой таблице у нас сейчас три строчки, а во второй - две. Так как же база данных поймет, какую строчку из первой таблицы соединить с какой строчкой из второй?
А для этого у нас как раз есть внешние ключи! В каждой строке первой таблицы у нас буквально лежит информация о том, с какой строкой из второй она должна быть связана. Но нам нужно явно сказать базе данных о том, что мы будем соединять строки в этом запросе именно по этой колонке!
Поэтому после слова JOIN и имени таблицы пишем специальное слово ON, а уже после него указываем условие, при котором две строчки из разных таблиц соединяются. А что за условие?
У нас строки в таблице участников как соединяются со строками в таблице пива? Если у них значения в колонке beer_id в первой таблице и id во второй таблице совпадают!
SELECT * FROM participants JOIN beer ON participants.beer_id = beer.id;
Это и есть наше условие. После слова ON так и пишем: в таблице participants колонка beer_id должна быть равна колонке id в таблице beer.
Указывать имена колонок для конкретных таблиц можно через точку, где сначала пишем имя таблицы, а потом имя колонки в ней. У нас же две таблицы участвуют в запросе, значит нужно явно указывать, про колонку какой таблицы мы говорим в каждом месте запроса.
Все! Теперь внутри самого SELECT нам осталось лишь прописать, какие колонки из этого соединения двух таблиц мы хотим взять. Например, мы хотим взять номер и имя участника, название пива и количество. Для указания конкретной колонки в нужной таблице мы снова используем имена таблиц и точку. Это явно указывает в запросе, откуда что мы берем.
SELECT participants.id, participants.name, participants.volume, beer.title FROM participants JOIN beer ON participants.beer_id = beer.id;
Если теперь мы выполним этот запрос, то результатом вернется вот такая таблица, где одновременно находятся данные из разных таблиц! А еще обратите внимание, что каждому участнику соответствует именно то самое пиво, для которого beer_id и id совпадают по тому условию, что мы прописали в запросе!
JOIN, который мы использовали в этом запросе, называется INNER JOIN или внутреннее соединение таблиц. Вы можете дописывать к нему слово INNER или пропускать - результат запроса не изменится. Просто JOIN - это сокращенная запись.
SELECT participants.name, beer.title, participants.volume FROM participants INNER JOIN beer ON participants.beer_id = beer.id; -- есть INNER -- то же самое, что SELECT participants.name, beer.title, participants.volume FROM participants JOIN beer ON participants.beer_id = beer.id; -- нет INNER
Как работает INNER JOIN?
Но если есть внутренний JOIN, то, наверно, есть и какой-нибудь внешний JOIN?..
Да, действительно, есть! Но зачем он может понадобиться? Выглядит так, что у нас уже все схвачено!
Но ведь может возникнуть очень неприятная ситуация! Что если на вашу вечеринку пришел еще один участник - Леха с соседнего двора. И как обычно… Он не принес с собой пиво! Вообще никакого!
Это, конечно, ни в какие ворота, и хорошо, что наша электронная система помогает теперь такие ситуации отлавливать и принимать меры. Но что же нам делать с запросом? Получается, что Леха в таблице участников имеет NULL в колонке beer_id, т.е. пустоту!
А как работает наш запрос с внутренним JOIN-ом? Он достает только те строчки, у которых нашлось совпадение по полю beer_id в таблице пива. Но получается, такой JOIN не включит в выборку Леху, ведь не найдено совпадение во второй таблице! Хотя на вечеринке он все же был.
SELECT participants.id, participants.name, participants.volume, beer.title FROM participants JOIN beer ON participants.beer_id = beer.id;
И это очень важное замечание про внутренний JOIN.
Если у вас есть две таблицы с данными, которые вы хотите соединить между собой в запросе, то внутренний
JOINв финальной выборке вернет только те данные, для которых условие совпадения выполнилось, и во второй таблице удалось найти хоть что-то по нему.
Но все остальные данные в финальную выборку не попадут ни из той, ни из другой таблицы. Например, Леха в нашем случае не будет включен в финальный результат, т.к. у него нет совпадающего пива. А если мы еще рассмотрим ситуацию, где во второй таблице есть пиво, которое никто не принес, то оно тоже не попадет в выборку этого внутреннего JOIN-а.
Что такое LEFT JOIN?
И как раз для таких постыдных ситуаций с Лехой и существуют внешние JOIN-ы в SQL. А в частности - LEFT JOIN! Что это такое?
Если мы хотим, чтобы в результате запроса, несмотря на вот такое безответственное отношение к организации мероприятия, тем не менее показывались все участники вечеринки, то можно добавить в наш запрос слово LEFT перед словом JOIN.
SELECT participants.id, participants.name, participants.volume, beer.title FROM participants LEFT JOIN beer ON participants.beer_id = beer.id; -- левый JOIN
И такой запрос будет работать ровно так же, как первый, но с одним важным изменением. Он все так же будет брать строчки из первой таблицы, соединять их со строчками из второй по условию и заполнять результат. Тут никаких перемен.
Но когда в левой таблице относительно слова JOIN вдруг обнаружится строчка, для которой нет связи в правой таблице, то эта строчка тоже будет включена в финальный результат!Именно благодаря вот этому слово LEFT.
Но вы скажете: “Ну и как же она будет включена? Какое же значение она получит в колонке для названия пива, если у нее нет соответствия во второй таблице?!”
Все верно, никакого значения она и не получит в этой колонке. Получит пустоту, так как там и нет ничего! А что в SQL символизирует пустоту? NULL, конечно.
Т.о. вот что делает левый внешний JOIN или по-английски LEFT OUTER JOIN.
При соединении двух таблиц он берет все строчки из двух таблиц, что совпадают по условию, как и внутренний
JOIN, но еще в результат добавляет все строчки из левой таблицы внутри запроса, для которых не нашлось совпадения в правой таблице, а все недостающие колонки заполняет NULL-ами.
Кстати, слово OUTER тоже является необязательным, как и слово INNER в случае с внутренним JOIN.
SELECT participants.id, participants.name, participants.volume, beer.title FROM participants LEFT JOIN beer ON participants.beer_id = beer.id; -- без OUTER -- то же, что SELECT participants.id, participants.name, participants.volume, beer.title FROM participants LEFT OUTER JOIN beer ON participants.beer_id = beer.id; -- а здесь OUTER
Такие JOIN-ы используются не менее часто, чем внутренние. Потому что вообще не всегда для каждой строчки есть соответствие в другой таблице. Как вот у нас в примере с пивом и вечеринкой. Кощунство, конечно, самое настоящее, но такова жизнь…
Что такое RIGHT JOIN?
Но раз уж есть левый JOIN, то, наверно, есть и правый JOIN… Да, действительно есть! И работает он очень похоже, на LEFT JOIN, сейчас все подробно объясню.
Например, у вас в таблице есть пиво, которое никто в этот раз не принес. Но вы, допустим, хотите, чтобы оно тоже попало в выборку в вашем запросе. Тогда можно сделать RIGHT JOIN, и это пиво тоже добавится в результат, хотя у него нет связей в таблице участников. А все недостающие колонки заполнятся NULL-ами.
SELECT participants.id, participants.name, participants.volume, beer.title FROM participants RIGHT JOIN beer ON participants.beer_id = beer.id; -- правый JOIN
Получается,
RIGHT JOINвключает в результат все строчки, для которых условие соединения выполняется, как иINNER JOIN, но еще и все строчки из правой таблицы, для которых не нашлось совпадения в левой таблице по этому условию.
Ну а если вы хотите написать запрос в результат которого будут включены и строки, для которых выполняется условие соединения, и строки из левой таблицы, у которых нет связей, и строки из правой таблицы, у которых нет связей, то вы можете сделать FULL JOIN!
SELECT participants.id, participants.name, participants.volume, beer.title FROM participants FULL JOIN beer ON participants.beer_id = beer.id; -- вообще все!
Это как LEFT JOIN и RIGHT JOIN одновременно! Т.е. туда попадет буквально все с обеих сторон!
Заключение
Какой JOIN когда использовать?
Это вы уже должны решить сами. В зависимости от ситуации и того, что именно вам нужно получить из базы данных и в каком виде, вы можете написать соответствующий SQL-запрос и выбрать правильный JOIN, который достанет все необходимое из ваших таблиц.
Все типы JOIN - это набор инструментов, которые вам доступны. А выбор конкретного из них для наиболее подходящего случая остается за вами. Гвоздям - молоток, а шурупам - отвертка. Но вот крестовая или плоская?
В принятии таких решений и есть работа инженера.
P.S.
Спасибо, что дочитали статью до конца. Надеюсь, она оказалась полезна в обучении!
Пожалуйста, поставьте лайк - это помогает мне писать еще больше подробных разборов с графикой и простыми словами.
