Почему LEFT JOIN учитывает пользователей вне бан-листа, а JOIN нет?

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

Понимаете ли вы, что «пользователи вне бан-листа» это анти-джойн, а не просто другой вид соединения. И знаете ли разницу между условием в ON и условием в WHERE. Для питоновской вакансии это ещё и проверка, читаете ли вы SQL, который сгенерировала ORM.

Как отвечать

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

select u.id, u.email
from users u
left join ban_list b on b.user_id = u.id
where b.user_id is null;

Проверка is null по колонке правой таблицы оставляет ровно тех, у кого совпадения не нашлось.

Ключевой нюанс про место условия: дополнительный фильтр по правой таблице пишется в on, а не в where. Строка where b.reason = 'spam' превращает внешнее соединение обратно во внутреннее, потому что отбрасывает все строки с NULL. В on то же условие лишь уточняет, что считать совпадением, и пользователей без пары не трогает.

В SQLAlchemy это outerjoin(Ban, Ban.user_id == User.id) плюс where(Ban.user_id.is_(None)), а надёжнее и обычно быстрее вариант с ~exists(). В Django .filter(ban__isnull=True) даёт то же внешнее соединение, а .exclude(id__in=...) собирает подзапрос с NOT IN.

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

Пишут NOT IN с подзапросом и получают пустой результат, если в колонке бан-листа встретился NULL. Сравнение с неизвестным даёт неизвестно, и условие не проходит ни для одной строки. NOT EXISTS и анти-джойн через IS NULL от этой ловушки свободны.

Ставят условие по правой таблице в WHERE и удивляются, что внешнее соединение перестало работать. Ошибка тихая: строки без пары исчезают молча, отчёт просто занижает цифры, и никто не видит этого до сверки.

Забывают про размножение строк. Если в бан-листе несколько записей на одного пользователя, соединение выдаст его столько же раз, и любой count соврёт. Лечится через EXISTS или DISTINCT, причём первый обычно дешевле: он останавливается на первом совпадении.