Как делать выборку связанных данных в чистом SQL

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

Умеете ли вы работать с базой без ORM и понимаете ли, какой SQL Django или SQLAlchemy генерируют за вас. Следом почти всегда спрашивают про N+1 и про разницу между условием в ON и в WHERE при LEFT JOIN.

Как отвечать

Покажите соединения на паре таблиц users и orders:

• INNER JOIN: только строки, у которых есть пара: SELECT u.name, o.id FROM users u JOIN orders o ON o.user_id = u.id;
• LEFT JOIN: все строки слева, справа NULL при отсутствии пары. Классика: пользователи без заказов через LEFT JOIN ... WHERE o.id IS NULL;
• RIGHT и FULL JOIN: правое соединение обычно переписывают в левое, полное нужно для сверки двух наборов. В MySQL FULL JOIN нет, его собирают через UNION;
• EXISTS: когда нужно только проверить наличие связанной строки, не размножая результат: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
• агрегация: GROUP BY u.id с count(o.id), а не count(*), иначе пользователь без заказов получит единицу;
• многие ко многим: через связующую таблицу, два соединения подряд;
• последняя запись в группе: row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) или JOIN LATERAL в PostgreSQL.

Со стороны Python: значения передаются только через плейсхолдеры драйвера (%s в psycopg, :name в sqlalchemy.text()), в Django есть Manager.raw() и connection.cursor(). Сборка SQL f-строкой открывает дорогу инъекции. Для скорости нужен индекс на внешнем ключе orders.user_id: PostgreSQL сам его не создаёт, а план проверяется через EXPLAIN ANALYZE.

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

Ставят условие на правую таблицу в WHERE после LEFT JOIN. Фильтр WHERE o.status = 'paid' отбрасывает строки с NULL и превращает соединение во внутреннее, условие нужно переносить в ON.

Присоединяют сразу две «многие» таблицы, например заказы и отзывы. Строки перемножаются, и sum(o.amount) считает неверно. Агрегировать надо в подзапросах до соединения.

Выбирают пользователей одним запросом, а заказы добирают в цикле по запросу на каждого. Это тот же N+1, что и в ORM, только написанный руками.