Чем WHERE id IN (подзапрос) отличается от обычного JOIN?

Главное отличие — что попадает в результат:

• JOIN подмешивает в выборку колонки из второй таблицы и МНОЖИТ строки: если у юзера 5 заказов, он появится 5 раз. Чтобы получить уникальных, придётся добавлять DISTINCT.
• WHERE user_id IN (подзапрос) (или EXISTS) — это просто фильтр «есть совпадение / нет». Строки users не дублируются, и колонки заказов в результат не попадают.

Правило: нужны данные из второй таблицы — JOIN. Нужна только проверка «существует ли связь» — IN/EXISTS. По скорости на нормальной БД с индексами они часто почти равны, планировщик нередко сводит их к одному плану.

Ещё про NULL: NOT IN (подзапрос со списком, где есть NULL) коварен — вернёт пустоту. NOT EXISTS и анти-JOIN этим не страдают. Для «нет связи» лучше избегать NOT IN.

Нужны колонки — JOIN, нужна проверка — IN/EXISTS.