INNER JOIN лишає лише ті рядки, для яких знайшлася пара в обох таблицях; LEFT JOIN лишає всі рядки лівої таблиці, підставляючи NULL там, де пари немає.
Як питають
Чому LEFT JOIN повернув менше рядків, ніж очікувалось?
Як знайти користувачів без жодного замовлення?
У чому різниця між умовою в ON і в WHERE?
JOINSQL
Пояснення
INNER JOIN повертає лише ті рядки, для яких умова звʼязку знайшла пару в обох таблицях: користувачі без замовлень у результат не потрапляють. LEFT JOIN бере всі рядки лівої таблиці й додає до них збіги з правої; якщо збігу немає, колонки правої таблиці заповнюються NULL. RIGHT JOIN є дзеркалом LEFT, а FULL OUTER JOIN обʼєднує обидва й у MySQL відсутній.
Найважливіше для практики — різниця між умовою в ON і у WHERE. ON описує, як шукати пару, і при LEFT JOIN відсутність пари дає NULL. WHERE застосовується вже до зібраного результату, і порівняння NULL з чим завгодно не є true, тому рядки без пари зникають. Так LEFT JOIN orders o ... WHERE o.status = 'paid' непомітно стає INNER JOIN. Якщо потрібні всі користувачі, а замовлення лише оплачені, умова на статус іде в ON.
Цей самий NULL є інструментом: WHERE o.id IS NULL після LEFT JOIN знаходить рядки без відповідника, і це стандартний anti-join поруч із NOT EXISTS. NOT IN з підзапитом, який може повернути NULL, дає порожній результат, тому в таких запитах його уникають.
Окрема пастка — множення рядків. JOIN один-до-багатьох повертає по рядку на кожен збіг, тому агрегати після нього подвоюються. Агрегувати праву таблицю варто до JOIN, у підзапиті або CTE.
КодSQL
-- INNER: лише користувачі, у яких є замовлення
SELECT u.id, o.id AS order_id
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
-- LEFT: усі користувачі; без замовлень order_id буде NULL
SELECT u.id, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
-- Пастка: умова у WHERE відкидає NULL-рядки, це вже фактично INNER JOIN
SELECT u.id, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';
-- Правильно: умова на праву таблицю в ON, користувачі без оплат лишаються
SELECT u.id, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';
-- Користувачі без жодного замовлення
SELECT u.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;
-- Те саме без JOIN, безпечно до NULL
SELECT u.id FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
Що хоче почути інтервʼюер
Чітке визначення обох через результат: INNER це перетин, LEFT це вся ліва таблиця плюс збіги з правої або NULL.
Що умова на праву таблицю у WHERE відфільтровує NULL-рядки й перетворює LEFT JOIN на INNER, а в ON вона обмежує лише збіг.
Що IS NULL по колонці правої таблиці після LEFT JOIN є стандартним способом знайти рядки без відповідника, як і NOT EXISTS.
Що JOIN на звʼязок один-до-багатьох множить рядки, тому COUNT і SUM після нього потребують DISTINCT або підзапиту.
Що RIGHT JOIN це дзеркало LEFT, а FULL OUTER JOIN є у PostgreSQL і відсутній у MySQL.
Типові помилки
Казати, що різниця лише у швидкості.
Писати умову на праву таблицю у WHERE й отримувати INNER JOIN, не розуміючи чому.
Рахувати COUNT(*) після JOIN один-до-багатьох і отримувати кількість замовлень замість кількості користувачів.
Використовувати NOT IN з підзапитом, який може повернути NULL: результат порожній, бо порівняння з NULL не є true.
Не знати, що для LEFT JOIN потрібен індекс на колонці звʼязку правої таблиці, інакше кожен рядок лівої сканує праву.
ПОРАДА
Саме приклад із WHERE проти ON відрізняє того, хто розуміє JOIN, від того, хто вивчив визначення. Намалюйте два кола або наведіть таблицю з трьома рядками.
Додаткові питанняЗ ВІДПОВІДЯМИ
LEFT JOIN спершу будує результат: для користувачів без замовлень усі колонки orders дорівнюють NULL. Потім WHERE o.status = 'paid' перевіряє NULL = 'paid', що не є true, і такі рядки відкидаються. Лишаються лише користувачі зі збігом, тобто те саме, що INNER JOIN. Умова в ON застосовується під час пошуку пари, тому користувач без оплачених замовлень лишається з NULL.
LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL, або NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id). NOT EXISTS часто читається краще й безпечний до NULL, на відміну від NOT IN. Оптимізатори PostgreSQL і MySQL 8 виконують обидва варіанти як anti-join, тому продуктивність зазвичай однакова.
JOIN один-до-багатьох повертає по рядку на кожен збіг: користувач з трьома замовленнями зʼявиться тричі. SUM(u.balance) додасть баланс тричі. Рішення: агрегувати праву таблицю в підзапиті чи CTE до JOIN, або рахувати COUNT(DISTINCT u.id). Це найчастіша помилка у звітах.
Декартів добуток: кожен рядок лівої таблиці з кожним рядком правої, без умови. Потрібен для генерації комбінацій, наприклад сітка дат і категорій для звіту з нулями, або для JOIN з таблицею з одним рядком параметрів. Випадковий CROSS JOIN через забуту умову дає мільйони рядків.