Какие логические join существуют?

Что проверяют

Знаете ли вы, какой результат даёт каждый вид соединения, особенно на строках без пары и на NULL. Слово «логические» часто намекает на второй вопрос: чем логический JOIN отличается от физического алгоритма, которым база его выполняет.

Как отвечать

Логические виды описывают, какие строки попадут в результат:

• INNER JOIN: только пары, где условие совпало. Строки без пары пропадают с обеих сторон;
• LEFT JOIN: все строки левой таблицы, у несовпавших поля правой заполнены NULL. Самый частый в аналитике: «все клиенты и их заказы, если они есть»;
• RIGHT JOIN: зеркало LEFT, обычно его переписывают в LEFT, поменяв таблицы местами;
• FULL OUTER JOIN: все строки обеих таблиц, пары склеены, остальное дополнено NULL. Удобен для сверки двух источников. В MySQL его нет, эмулируют через UNION двух внешних соединений;
• CROSS JOIN: декартово произведение, 1000 строк на 1000 дают миллион. Нужен для сетки «каждый день на каждый магазин»;
• semi join и anti join: EXISTS и NOT EXISTS или LEFT JOIN ... WHERE b.id IS NULL. Отвечают на вопросы «у кого есть» и «у кого нет» без размножения строк.

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

Физический алгоритм выбирает планировщик: Nested Loop для маленькой выборки с индексом, Hash Join для больших наборов по равенству, Merge Join для отсортированных данных. Всё это видно в EXPLAIN.

Где спотыкаются

Ставят условие на правую таблицу в WHERE после LEFT JOIN. Строки с NULL отсекаются, и соединение молча становится внутренним. Фильтру по правой таблице место в ON.

Не учитывают кратность. Соединение клиентов сразу с заказами и платежами размножает строки, и SUM по платежам получается завышенной. Сначала агрегируйте, потом соединяйте.

Забывают про NULL: ключи со значением NULL не совпадают даже друг с другом, а NOT IN по подзапросу, где встретился NULL, вернёт пустой результат.