Привет, Хабр! Меня зовут Александр Сушков, я аналитик данных, эксперт по SQL, автор, преподаватель и наставник курсов «SQL для работы с данными и аналитики» и «Аналитик данных».
![](https://habrastorage.org/getpro/habr/upload_files/b22/246/747/b22246747e3155f2ca152fcfff7e475c.png)
Один из самых распространённых операторов в SQL — это JOIN. В статье расскажу об особенностях этого оператора: как использовать другие операторы в JOIN после ON, кроме «равно», и зачем это может быть нужно.
Предлагаю следовать плану:
Принцип работы JOIN и классический пример с ним
Эксперимент 1: заменить «равно» на «неравно»
Эксперимент 2: определить ещё один оператор AND или OR
Эксперимент 3: добавить условия по сумме товаров
Пример тестового задания
Принцип работы JOIN и классический пример с ним
JOIN — важный инструмент в реляционной базе, ведь без присоединения таблиц не собрать нужные данные. Не будем сегодня обсуждать всевозможные LEFT, FULL или CROSS JOIN-ы, а поговорим о том, что находится после ON.
Новички в SQL часто ограничиваются ON table_1. user_id = table_2. user_id. Однако возможностей у части запроса после ON намного больше. Кто давно работает с SQL, наверняка знает об этом.
Рассмотрим таблицу из базы данных, где содержатся идентификаторы, фамилии клиентов и продукты, которые они купили:
![](https://habrastorage.org/getpro/habr/upload_files/509/679/834/50967983428c24537370b719c8c64bfd.png)
Вторая таблица содержит идентификаторы, продукты и их цены:
![](https://habrastorage.org/getpro/habr/upload_files/aa4/113/28b/aa411328bd15e4f7ac638bfb1c99ee55.png)
Теперь ответим на вопрос, сколько денег потратил каждый покупатель. И тут достаточно просто присоединить одну таблицу к другой.
SELECT *
FROM orders o JOIN prices p ON p.product = o.product;
Здесь мы использовали INNER JOIN
, потому две последние фамилии не попадут в итоговую таблицу — для них нет совпадений в таблице с ценами.
Вспомним, как работает присоединение.
Программа берёт первую строку таблицы orders
и смотрит данные в таблице prices. Если находит совпадение по product
, то такую строку присоединяет к первой.
![](https://habrastorage.org/getpro/habr/upload_files/502/2ff/d52/5022ffd5283e7ed850fc0876d25be2c9.png)
Затем во второй строке происходит то же самое.
![](https://habrastorage.org/getpro/habr/upload_files/687/491/b5b/687491b5ba96ec36fde70c87e332dc4d.png)
И так далее, пока сборка всей таблицы не будет закончена. Обратите внимание: тут описан только принцип, а не работа «под капотом».
Финальная таблица будет выглядеть так:
![](https://habrastorage.org/getpro/habr/upload_files/140/690/d6a/140690d6a8ee6631a451165098d8687f.png)
По сути, это и есть ответ на вопрос — напротив каждой фамилии появилась стоимость товара.
Эксперимент 1: заменить «равно» на «неравно»
Задача: найти все товары, которые никогда не покупали Иванов и Коршун.
Кажется, что для решения достаточно просто написать в WHERE
условие:
Фамилии покупателей не должны равняться «Иванов» и «Коршун».
Отобрать уникальные значения товаров и получить список тех, которые Иванов и Коршун никогда не покупали.
Однако ON
может использовать не только «равно», но и другие математические и логические операторы.
Проведём эксперимент — для каждой фамилии определим товары, которые никогда не покупали клиенты. Для этого в коде поменяем «равно» на «неравно»:
SELECT *
FROM orders o JOIN prices p ON p.product <> o.product;
На этот раз присоединяться к первой таблице будут те строки, для которых не было совпадений:
![](https://habrastorage.org/getpro/habr/upload_files/c6b/478/76c/c6b47876c88a9d89a7a7cd7c8d669a05.png)
Повторим это действие, и программа отберёт только те строки, для которых не нашлось совпадений. То есть к Иванову, который купил мультиварку, присоединятся две другие строки, где будут стиральная машина и бритва.
Итоговая таблица выглядит так:
![](https://habrastorage.org/getpro/habr/upload_files/952/1eb/10c/9521eb10c5e7eea349881f474c1c81ce.png)
Таблица получилась длиннее предыдущей, ведь видов товаров, которые покупатели не купили, намного больше купленных.
Эксперимент 2: определить ещё один оператор AND или OR
Следующий шаг — добавить Иванова и Коршуна. Для этого прописываем WHERE и указываем фамилии «Иванов» и «Коршун». Но и это не обязательно. Такое условие можно просто положить в ON. Только стоит распределить операторы по скобкам, чтобы установить правильный порядок выполнения действий.
SELECT *
FROM orders o
JOIN prices p ON p.product <> o.product
AND (o.last_name = 'Петров'
OR o.last_name = 'Коршун');
Итоговая таблица выглядит так:
![](https://habrastorage.org/getpro/habr/upload_files/8ef/785/b32/8ef785b3297e77b03bd746c42ace7890.png)
Тут условие сработало как обычное WHERE
, только WHERE
даже не использовали. Точнее ON
— по сути и есть WHERE
, работающий в рамках JOIN
.
Эксперимент 3: добавить условия по сумме товаров
Чтобы добавить условие, например «сумма товаров больше или равна 20 000», действуем аналогично 2-му эксперименту, но в ON
добавляем ещё одно условие.
SELECT *
FROM orders o
JOIN prices p ON p.product <> o.product
AND (o.last_name = 'Петров'
OR o.last_name = 'Коршун')
AND p.price >= 20000;
Обратите внимание, что в этом эксперименте определили много условий, не выходя за пределы FROM. Если связывать логику присоединений и фильтрации, можно получить качественные результаты в оптимальном запросе:
![](https://habrastorage.org/getpro/habr/upload_files/0dc/85c/234/0dc85c234f252f56519d5c0380f2ce88.png)
SQL — очень вариативный язык. Если дать 10 специалистам написать запрос, то 9 из 10 запросов будут разными и большинство будет правильно выполнять задачу. Конечно, каждый случай индивидуален, но принцип, я думаю, понятен. Какие-то запросы будут выполняться быстрее, какие-то медленнее.
Когда создаём категории из другой таблицы, можно использовать оператор CASE
, с помощью которого решается то же самое, но дольше. Стоит конкретно прописать: «если значение больше заданного, то назначьте ему, например, категорию 1, если меньше — то 2».
Но проблема в том, что оператор CASE
стоит в порядке запроса достаточно поздно. Запрос может выполняться слишком медленно. А JOIN
срабатывает практически сразу, как только начинает выполняться запрос. Действует логика присоединения, и мы быстрее получим результат.
Пример тестового задания
Вышеописанными способами опытные разработчики экономят время на прописывание отдельных условий. Но эти «лайфхаки» могут пригодиться даже джунам, например на собеседованиях.
Задача на собеседовании:
Выгрузите таблицу orders
с дополнительным полем category
, в котором будет отображаться категория из таблицы categories
. Сделать это нужно в соответствии с тем, в какой диапазон попадает цена товара.
Дана таблица, где описаны категории дорогих, средних и дешёвых товаров:
![](https://habrastorage.org/getpro/habr/upload_files/48b/0a8/9a5/48b0a89a57b43ff41196e6463d8a2b23.png)
Например, если цена товара — 30 тысяч, в дополнительном поле должна отобразиться категория «средний». Использовать условные операторы типа CASE
или IF
запрещено.
Полученный в экспериментах 1, 2 и 3 опыт помогает справиться с задачей: присоединиться должна та строка, где price
будет больше либо равно limit_1
, и меньше, чем limit_2
.
Получаем решение:
SELECT last_name,
o.product,
p.price,
c.category
FROM orders o
JOIN prices p ON p.product = o.product
JOIN category c ON p.price >= c.limit_1 AND p.price < c.limit_2
Итоговая таблица будет выглядеть так:
![](https://habrastorage.org/getpro/habr/upload_files/aec/e98/b9e/aece98b9e772ed5529cc1196c6b2ce5f.png)
Собеседование пройдено — вас взяли на работу!
![](https://habrastorage.org/getpro/habr/upload_files/d0b/1b4/6a6/d0b1b46a62326969a124bfef9369d3f8.png)
Совет вместо вывода
Описанное в статье скорее относится к лайфхакам, чем к ежедневной практике. Обращаться к этим методикам стоит только после того, как поняли и разобрались в JOIN.
Как говорил мой тренер по фехтованию: лучше научиться одному простому удару, чем «финтить» и не понимать, зачем ты это делаешь. Если не понимать, как работает присоединение таблиц, есть риск запутаться и ошибиться.
Изучить основные функции и операторы, потренироваться в решении задач и работе с СУБД можно на курсе от Практикума «SQL для работы с данными и аналитики». Студенты за период от 1,5 месяцев учатся не только понимать язык запросов SQL, но и начинают использовать его для бизнес-целей.
Комментарии (5)
eandr_67
24.06.2023 08:09+5Достаточно странно неоднократно видеть в статье:
(o.last_name = 'Петров' OR o.last_name = 'Коршун')
при наличии в SQL:
o.last_name IN ('Петров', 'Коршун')
nronnie
24.06.2023 08:09+1Позанудствую. То, что вы пишете, допускается синтаксисом SQL, но, формально, все что не
a = b AND c = d AND foo = bar AND ...
(причем всеa
,b
, и т.д. должны быть при этом именно столбцами таблиц) с точки зрения реляционной теории (да и с точки зрения планировщика запросов) JOIN-ом не является. Если вы пишете для новичков, то, имхо, стоило бы об этом упомянуть.
FanatPHP
24.06.2023 08:09+2Странная статья. Поставил минус с комментарием "ничего не понял".
Во-первых, я бы поменял заголовок, потому что под этим читатель действительно ждет рифмы "inner" и "outer". Если речь только про дополнительные условия в ON, то так и надо написать в заголовке.
Плюс непонятно, куда делись фонари во втором эксперименте. Ведь подопытные их вроде не покупали?
Ну и CASE тут явно притянут за уши. Вот прямо серьезно, case с подзапросами на два экрана? И не используем мы его только потому что "медленнее", а не потому что он тут не пришей кобыле хвост?
lgnmx
24.06.2023 08:09+2В эксперименте 1 постановка задачи не соответствует решению.
В постановке - найти товары, которые никогда не покупались двумя покупателями, в решении - товары, которые покупались не этими двумя покупателями.
AlexeyK77
При расшифровке key\ID значением из таблицы-справочника корректнее использовать left outer join