Привет, Хабр! Меня зовут Александр Сушков, я аналитик данных, эксперт по SQL, автор, преподаватель и наставник курсов «SQL для работы с данными и аналитики» и «Аналитик данных».

Один из самых распространённых операторов в 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, наверняка знает об этом.

Рассмотрим таблицу из базы данных, где содержатся идентификаторы, фамилии клиентов и продукты, которые они купили:

Вторая таблица содержит идентификаторы, продукты и их цены: 

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

SELECT *

FROM orders o JOIN prices p ON p.product = o.product;

Здесь мы использовали INNER JOIN, потому две последние фамилии не попадут в итоговую таблицу — для них нет совпадений в таблице с ценами.

Вспомним, как работает присоединение.

Программа берёт первую строку таблицы orders и смотрит данные в таблице prices. Если находит совпадение по product, то такую строку присоединяет к первой.

Затем во второй строке происходит то же самое.

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

Финальная таблица будет выглядеть так:

По сути, это и есть ответ на вопрос — напротив каждой фамилии появилась стоимость товара.

Эксперимент 1: заменить «равно» на «неравно»

Задача: найти все товары, которые никогда не покупали Иванов и Коршун.

Кажется, что для решения достаточно просто написать в WHERE условие:

1. Фамилии покупателей не должны равняться «Иванов» и «Коршун». 

2. Отобрать уникальные значения товаров и получить список тех, которые Иванов и Коршун никогда не покупали.

Однако ON может использовать не только «равно», но и другие математические и логические операторы.

Проведём эксперимент — для каждой фамилии определим товары, которые никогда не покупали клиенты. Для этого в коде поменяем «равно» на «неравно»:

SELECT *

FROM orders o JOIN prices p ON p.product <> o.product;

На этот раз присоединяться к первой таблице будут те строки, для которых не было совпадений:

Повторим это действие, и программа отберёт только те строки, для которых не нашлось совпадений. То есть к Иванову, который купил мультиварку, присоединятся две другие строки, где будут стиральная машина и бритва.

Итоговая таблица выглядит так:

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

Эксперимент 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 = 'Коршун');

Итоговая таблица выглядит так:

Тут условие сработало как обычное 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. Если связывать логику присоединений и фильтрации, можно получить качественные результаты в оптимальном запросе:

SQL — очень вариативный язык. Если дать 10 специалистам написать запрос, то 9 из 10 запросов будут разными и большинство будет правильно выполнять задачу. Конечно, каждый случай индивидуален, но принцип, я думаю, понятен. Какие-то запросы будут выполняться быстрее, какие-то медленнее.

Когда создаём категории из другой таблицы, можно использовать оператор CASE, с помощью которого решается то же самое, но дольше. Стоит конкретно прописать: «если значение больше заданного, то назначьте ему, например, категорию 1, если меньше — то 2».

Но проблема в том, что оператор CASE стоит в порядке запроса достаточно поздно. Запрос может выполняться слишком медленно. А JOIN срабатывает практически сразу, как только начинает выполняться запрос. Действует логика присоединения, и мы быстрее получим результат.

Пример тестового задания

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

Задача на собеседовании:

Выгрузите таблицу orders с дополнительным полем category, в котором будет отображаться категория из таблицы categories. Сделать это нужно в соответствии с тем, в какой диапазон попадает цена товара.

Дана таблица, где описаны категории дорогих, средних и дешёвых товаров:

Например, если цена товара — 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

Итоговая таблица будет выглядеть так:

Собеседование пройдено — вас взяли на работу!

Совет вместо вывода

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

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

Изучить основные функции и операторы, потренироваться в решении задач и работе с СУБД можно на курсе от Практикума «SQL для работы с данными и аналитики». Студенты за период от 1,5 месяцев учатся не только понимать язык запросов SQL, но и начинают использовать его для бизнес-целей.