Как выводить данные inner join
Понимание джойнов сломано. Это точно не пересечение кругов, честно
Так получилось, что я провожу довольно много собеседований на должность веб-программиста. Один из обязательных вопросов, который я задаю — это чем отличается 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. Это и читабельнее, и быстрее.
Выводы
Как мне кажется, не стоит использовать диаграммы Венна для объяснения джойнов. Также, похоже, нужно избегать термина «пересечение».
Как объяснить на картинке джойны корректно, я, честно говоря, не представляю. Если вы знаете — расскажите, плиз, и киньте в коменты. А мы обсудим это в одном из ближайших выпусков подкаста «Цинковый прод». Не забудьте подписаться.
Соединение таблиц – операция JOIN и ее виды
Говоря про соединение таблиц в SQL, обычно подразумевают один из видов операции JOIN. Не стоит путать с объединением таблиц через операцию UNION. В этой статье я постараюсь простыми словами рассказать именно про соединение, чтобы после ее прочтения Вы могли использовать джойны в работе и не допускать грубых ошибок.
Соединение – это операция, когда таблицы сравниваются между собой построчно и появляется возможность вывода столбцов из всех таблиц, участвующих в соединении.
Придумаем 2 таблицы, на которых будем тренироваться.
Таблица «Сотрудники», содержит поля:
Таблица «Отделы», содержит поля:
Давайте уже быстрее что-нибудь покодим.
INNER JOIN
Самый простой вид соединения INNER JOIN – внутреннее соединение. Этот вид джойна выведет только те строки, если условие соединения выполняется (является истинным, т.е. TRUE). В запросах необязательно прописывать INNER – если написать только JOIN, то СУБД по умолчанию выполнить именно внутреннее соединение.
Давайте соединим таблицы из нашего примера, чтобы ответить на вопрос, в каких отделах работают сотрудники (читайте комментарии в запросе для понимания синтаксиса).
Получим следующий результат:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 4 | Светлана | Бар |
Из результатов пропал сотрудник Алексей (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-запрос из самого первого примера так, чтобы ответить на вопрос «В каких отделах работают сотрудники, а также показать тех, кто не распределен ни в один отдел?»:
Результат запроса будет следующим:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 3 | Алексей | NULL |
| 4 | Светлана | Бар |
Как видите, запрос вернул все строки из левой таблицы «Сотрудники», дополнив их значениями из правой таблицы «Отделы». А вот строка для отдела «Администрация» не показана, т.к. для нее не нашлось совпадений слева.
Это мы рассмотрели пример для левого внешнего соединения. Для RIGHT JOIN будет все тоже самое, только вернутся все строки из таблицы «Отделы»:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 4 | Светлана | Бар |
| NULL | NULL | Администрация |
Алексей «потерялся», Администрация «нашлась».
Вопрос для Вас. Что надо изменить в последнем приведенном SQL-запросе, чтобы результат остался тем же, но вместо LEFT JOIN, использовался RIGHT JOIN?
Ответ. Нужно поменять таблицы местами:
В одном запросе можно применять и внутренние соединения, и внешние одновременно, главное соблюдать порядок таблиц, чтобы не потерять часть записей (строк).
FULL JOIN
Еще один вид соединения, который осталось рассмотреть – полное внешнее соединение.
Этот вид джойна вернет все строки из всех таблиц, участвующих в соединении, соединив между собой те, которые подошли под условие ON.
Давайте посмотрим всех сотрудников и все отделы из наших тестовых таблиц:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 3 | Алексей | NULL |
| 4 | Светлана | Бар |
| NULL | NULL | Администрация |
Теперь мы видим все, даже Алексея без отдела и Администрацию без сотрудников.
Вместо заключения
Помните о порядке выполнения соединений и порядке таблиц, если используете несколько соединений и используете внешние соединения. Можно выполнять LEFT JOIN для сохранения всех строк из самой первой таблицы, а последним внутренним соединением потерять часть данных. На маленьких таблицах косяк заметить легко, на огромных очень тяжело, поэтому будьте внимательны.
Рассмотрим последний пример и введем еще одну таблицу «Банки», в которой обслуживаются наши придуманные сотрудники:
| id | Наименование |
|---|---|
| 1 | Банк №1 |
| 2 | Лучший банк |
| 3 | Банк Лидер |
В таблицу «Сотрудники» добавим столбец «Банк»:
| id | Имя | Отдел | Банк |
|---|---|---|---|
| 1 | Юлия | 1 | 2 |
| 2 | Федор | 2 | 2 |
| 3 | Алексей | NULL | 3 |
| 4 | Светлана | 2 | 4 |
Теперь выполним такой запрос:
В результате потеряли информацию о Светлане, т.к. для нее не нашлось банка с (такое происходит из-за неправильной проектировки БД):
| id | Имя | Отдел | Банк |
|---|---|---|---|
| 1 | Юлия | Кухня | Лучший банк |
| 2 | Федор | Бар | Лучший банк |
| 3 | Алексей | NULL | Банк Лидер |
Хочу обратить внимание на то, что любое сравнение с неизвестным значением никогда не будет истинным (даже NULL = NULL). Эту грубую ошибку часто допускают начинающие специалисты. Подробнее читайте в статье про значение NULL в SQL.
Пройдите мой тест на знание основ SQL. В нем есть задания на соединения таблиц, которые помогут закрепить материал.
Дополнить Ваше понимание соединений в SQL могут схемы, изображенные с помощью кругов Эйлера. В интернете много примеров в виде картинок.
Если какие нюансы джойнов остались не раскрытыми, или что-то описано не совсем понятно, что-то надо дополнить, то пишите в комментариях. Буду только рад вопросам и предложениям.
Привожу простыню запросов, чтобы Вы могли попрактиковаться на легких примерах, рассмотренных в статье:
SQL урок 3. Запросы sql INNER JOIN (объединение таблиц)
Выборка из нескольких таблиц (неявная операция соединения)
В sql выборка из нескольких таблиц или неявная операция соединения допускается в предложении FROM, но при этом перечисление таблиц, как правило, сопровождается условием соединения записей из разных таблиц.
Рассмотрим пример неявной операции соединения:
SELECT DISTINCT pc.Номер, Производитель FROM pc, product WHERE pc.Номер = product.Номер AND Цена
SELECT DISTINCT A.Номер AS модель1, B.Номер AS модель2 FROM pc AS A, pc AS B WHERE A.Цена = B.Цена AND A.Номер A.Номер используется для того, чтобы не выводились одинаковые пары, отличающиеся только перестановкой номера: 
Результат:
Запросы sql INNER JOIN
В предложении FROM может использоваться явная операция соединения двух и более таблиц.
Разберем пример. Имеем две таблицы: teachers (учителя) и lessons (уроки):
SELECT t.name,t.code,l.course FROM teachers t INNER JOIN lessons l ON t.id=l.tid
В запросе буквы l и t являются псевдонимами таблиц lessons (l) и teachers (t).
При внутреннем объединении выбираются только совпадающие данные из объединяемых таблиц.
Запросы sql OUTER JOIN
При использовании внутреннего объединения inner join выбираются только совпадающие данные из объединяемых таблиц. Для того чтобы получить данные, которые подходят по условию частично, необходимо использовать внешнее объединение.
Рисунок относится к объединению типа Left Outer Join: 
SELECT t.name, t.code, l.course FROM teachers t LEFT OUTER JOIN lessons l ON t.id = l.tid
С тем же примером (выбрать имена учителей и курсы, которые они ведут) фильтрация по RIGHT OUTER JOIN вернет полный список уроков по курсам (правая таблица) и сопоставленных учителей. Но так как нет таких уроков, которые бы не соответствовали определенным учителям, то выборка будет состоять только из двух строк:
SELECT t.name, t.code, l.course FROM teachers t RIGHT OUTER JOIN lessons l ON t.id = l.tid
Выбрать фамилии студентов и их оценки по Word. В случае отсутствия оценки, все равно выводить фамилию.
В приведенных примерах можно вводить фильтры для более точной фильтрации:
SELECT t.name, t.code, l.course FROM teachers t LEFT OUTER JOIN lessons l ON t.id = l.tid WHERE l.tid IS NULL
Объединение с подзапросом
При использовании объединения часто бывает необходимо, чтобы результирующая выборка содержала данные только по одной конкретной строке
SELECT t1.*, t2.* from left_table t1 left join (select * from right_table where some_column = 1 limit 1) t2 ON t1.id = t2.join_id
SELECT t1.*, t2.* from left_table t1 inner join (select * from right_table where some_column = 1 limit 1) t2 ON t1.id = t2.join_id
SELECT t1.*, t2.* from teachers t1 inner join (select * from lessons where course = «php» limit 1) t2 ON t1.id = t2.tid
Разберем еще один пример:
Так как в таблице product находятся данные не только по компьютерам, то мы использовали подзапрос, при этом сохранив внутреннее соединение таблиц.
Результат: 
Операция INNER JOIN
Объединяет записи из двух таблиц, если в связующих полях этих таблиц содержатся одинаковые значения.
Синтаксис
FROM таблица1 INNER JOIN таблица2 ON таблица1. поле1 оператор_сравнения таблица2. поле2
Операция INNER JOIN состоит из следующих элементов:
Имена таблиц, содержащих объединяемые записи.
Имена полей, которые соединены. Если они не являются числами, поля должны быть одного тип данных и содержать данные одного типа, но не должны иметь одинаковых имен.
Замечания
Операцию INNER JOIN можно использовать в любом предложении FROM. Это самый распространенный тип объединения. С его помощью происходит объединение записей из двух таблиц по связующему полю, если оно содержит одинаковые значения в обеих таблицах.
При работе с таблицами «Отделы» и «Сотрудники» операцией INNER JOIN можно воспользоваться для выбора всех сотрудников в каждом отделе. Если же требуется выбрать все отделы (включая те из них, в которых нет сотрудников) или всех сотрудников (в том числе и не закрепленных за отделом), можно при помощи операции LEFT JOIN или RIGHT JOIN создать внешнее соединение.
При попытке связи полей, содержащих данные типа Memo или объекты OLE, возникнет ошибка.
Вы можете объединить любые два числовых поля похожих типов. Например, можно объединить поля с типом данных «Автонобере» и «Длинный». Однако нельзя соединить поля Single и Double.
В следующем примере показано, как можно объединить таблицы Categories и Products по полю CategoryID.
В инструкции JOIN можно также связать несколько предложений ON, используя следующий синтаксис:
SELECT поля
FROM таблица1 INNER JOIN таблица2
ON таблица1. таблица1 оператор_сравнения таблица2. поле1 AND
ON таблица1. поле2 оператор_сравнения таблица2. поле2) OR
ON таблица1. поле3 оператор_сравнения таблица2. поле3)];
Ниже приведен пример синтаксиса, с помощью которого можно составлять вложенные инструкции JOIN.
Операции LEFT JOIN и RIGHT JOIN могут быть вложены в операцию INNER JOIN, но операция INNER JOIN не может быть вложена в операцию LEFT JOIN или RIGHT JOIN.
INNER JOIN. Внутреннее объединение таблиц в MySQL
Команда INNER JOIN
INNER JOIN позволяет объединить несколько таблиц, которые имеют одинаковые данные в одной или нескольких колонках.
Диаграмма выбора данных из таблиц через INNER JOIN
Синтаксис INNER JOIN
Например, у нас есть база данных книжного магазина Bookstore и ее связанные таблицы customers и orders. Выведем их содержимое и увидим, что колонка customer_id в таблице orders, использует значения, которые полностью совпадают со значениями колонки id в таблице customers.
mysql> SELECT id, first_name, last_name
-> FROM customers;
+—-+—————-+—————-+
| id | first_name | last_name |
+—-+—————-+—————-+
| 1 | Олег | Пальшин |
| 2 | Jane | Doherty |
| 3 | Евгений | Серов |
| 4 | София | Молина |
| 5 | John | Doe |
+—-+—————-+—————-+
5 rows in set (0.00 sec)
mysql> SELECT id, customer_id, employer_id, status, order_date
-> FROM orders;
+—-+————-+————-+——————+———————+
| id | customer_id | employer_id | status | order_date |
+—-+————-+————-+——————+———————+
| 3 | 1 | 3 | Готов к отправке | 2019-01-05 04:55:58 |
| 6 | 3 | 3 | Готов к отправке | 2019-01-15 14:56:12 |
| 7 | 2 | 2 | Завершен | 2019-01-11 20:59:40 |
| 9 | 4 | 3 | Обрабатывается | 2018-12-22 21:16:16 |
| 10 | 2 | 3 | Готов к отправке | 2018-12-24 04:28:54 |
+—-+————-+————-+——————+———————+
5 rows in set (0.00 sec)
Теперь, зная что у нас есть совпадения значений колонок customers.id и orders.customer_id выполним их сведение, в котором подставим имя и фамилию клиента вместо его идентификатора.
mysql> SELECT orders.id, customers.first_name, customers.last_name, orders.status, orders.order_date
-> FROM orders
-> INNER JOIN customers ON orders.customer_id = customers.id;
+—-+————+————+——————+———————+
| id | first_name | last_name | status | order_date |
+—-+————+————+——————+———————+
| 3 | Олег | Пальшин | Готов к отправке | 2019-01-05 04:55:58 |
| 6 | Евгений | Серов | Готов к отправке | 2019-01-15 14:56:12 |
| 7 | Jane | Doherty | Завершен | 2019-01-11 20:59:40 |
| 9 | София | Молина | Обрабатывается | 2018-12-22 21:16:16 |
| 10 | Jane | Doherty | Готов к отправке | 2018-12-24 04:28:54 |
+—-+————+————+——————+———————+
Внутреннее объединение для более двух таблиц
Объединение более двух таблиц работает работает также как и с двумя, главное чтобы были одинаковые значения хотя бы между двумя таблицами.
К таблицам customers и orders добавим еще одну таблицу employers, в которой хранятся записи о сотрудниках интернет магазина.
mysql> SELECT id, first_name, last_name, position FROM employers;
+—-+————+———— +——————-+
| id | first_name | last_name | position |
+—-+————+———— +——————-+
| 1 | Абросим | Сумароков | Ген. Директор |
| 2 | Александр | Суматохин | Старший продавец |
| 3 | Петр | Стропин | Продавец |
| 4 | Фёдор | Телецкий | Кладовщик |
+—-+————+———— +——————-+
4 rows in set (0.00 sec)
Сведем все три таблицы, чтобы получить более полную информацию о заказах. Для порядка добавим алиасы для некоторых колонок. Подробнее об алиасах вы можете узнать здесь.
mysql> SELECT orders.id, CONCAT (customers.first_name, ‘ ‘, customers.last_name) AS ‘Имя клиента’,
-> CONCAT (employers.first_name, ‘ ‘, employers.last_name, ‘ (‘, employers.position, ‘)’) AS ‘Заказ принял’,
-> orders.status, orders.order_date
-> FROM ((orders
-> INNER JOIN customers ON orders.customer_id = customers.id)
-> INNER JOIN employers ON orders.employer_id = employers.id) ORDER BY id;
+—-+—————+—————————————-+——————-+———————+
| id | Имя клиента | Заказ принял | status | order_date |
+—-+—————+—————————————-+——————-+———————+
| 3 | Олег Пальшин | Петр Стропин (Продавец) | Готов к отправке | 2019-01-05 04:55:58 |
| 6 | Евгений Серов | Александр Суматохин (Старший продавец) | Готов к отправке | 2019-01-15 14:56:12 |
| 7 | Jane Doherty | Александр Суматохин (Старший продавец) | Завершен | 2019-01-11 20:59:40 |
| 9 | София Молина | Петр Стропин (Продавец) | Обрабатывается | 2018-12-22 21:16:16 |
| 10 | Jane Doherty | Петр Стропин (Продавец) | Готов к отправке | 2018-12-24 04:28:54 |
+—-+—————+—————————————-+——————-+———————+
5 rows in set (0.00 sec)
Разница между INNER JOIN и WHERE
mysql> SELECT orders.id, customers.first_name, customers.last_name, orders.status, orders.order_date
-> FROM customers, orders
-> WHERE customers.id = orders.customer_id;
+—-+————+————+——————+———————+
| id | first_name | last_name | status | order_date |
+—-+————+————+——————+———————+
| 3 | Олег | Пальшин | Готов к отправке | 2019-01-05 04:55:58 |
| 6 | Евгений | Серов | Готов к отправке | 2019-01-15 14:56:12 |
| 7 | Jane | Doherty | Завершен | 2019-01-11 20:59:40 |
| 9 | София | Молина | Обрабатывается | 2018-12-22 21:16:16 |
| 10 | Jane | Doherty | Готов к отправке | 2018-12-24 04:28:54 |
+—-+————+————+——————+———————+
5 rows in set (0.00 sec)
Как видим в результатах вывода, по сравнению с первым примером, нет никакой разницы.
В плане производительности особых различий также не наблюдается.
Понравилась статья? Расскажите о ней друзьям!