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

Короткий ответ

Выборка связанных данных в чистом SQL выполняется через JOIN - INNER, LEFT, RIGHT или FULL, в зависимости от того, какие записи нужны. Для связи "многие ко многим" используется промежуточная таблица и два JOIN. Альтернатива - подзапросы или UNION, но JOIN обычно эффективнее и читаемее. В Python это сочетается с ORM (SQLAlchemy), но понимание чистого SQL критично для оптимизации и отладки.

Подробное объяснение

Основной механизм - оператор JOIN, который объединяет строки из двух таблиц по условию. Виды:

• INNER JOIN - только совпадающие строки в обеих таблицах.
• LEFT JOIN - все строки из левой таблицы + совпадающие из правой (NULL, если нет совпадения).
• RIGHT JOIN - зеркально LEFT.
• FULL JOIN - все строки из обеих, NULL где нет совпадения (поддерживается не везде, в MySQL эмулируется через UNION).

Для связи "многие ко многим" (например, users и roles) нужна промежуточная таблица user_roles с внешними ключами. Тогда выборка - два JOIN подряд.

Также есть подзапросы: коррелированные и некоррелированные. Они полезны для агрегаций или фильтрации, но часто менее производительны, чем JOIN.

Важно про индексы: условие JOIN должно использовать индексы по внешним ключам, иначе будет full scan.

На практике

В Python с чистым SQL (через psycopg2 или sqlite3) выборка выглядит как обычный запрос. С SQLAlchemy Core или ORM - через relationship и joinedload, но это уже обёртка над тем же SQL.

Типичный сценарий: получить пользователя с его заказами. Один запрос с LEFT JOIN вернёт дублирующиеся данные пользователя на каждую строку заказа - это нормально, но нужно дедуплицировать на стороне Python или использовать агрегацию (array_agg в PostgreSQL).

Для больших выборок лучше делать несколько запросов и собирать данные в Python, чем один гигантский JOIN - trade-off между количеством запросов и объёмом передаваемых данных.

Пример кода

SQL
-- один ко многим: пользователь и его заказы
SELECT u.id, u.name, o.id AS order_id, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id = 42;

-- многие ко многим: пользователи и их роли
SELECT u.name, r.name AS role_name
FROM users u
JOIN user_roles ur ON ur.user_id = u.id
JOIN roles r ON r.id = ur.role_id
WHERE u.active = true;

PYTHON
import sqlite3

conn = sqlite3.connect("db.sqlite")
cur = conn.cursor()
cur.execute("""
    SELECT u.id, u.name, o.id, o.total
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id
    WHERE u.id = ?
""", (42,))
rows = cur.fetchall()

Как отвечать на собеседовании

Начни с определения JOIN и его видов. Затем объясни, когда какой использовать: INNER для строгого соответствия, LEFT для "все из левой, даже без пары". Приведи пример "многие ко многим" через промежуточную таблицу. Упомяни, что в Python это обычно делается через ORM, но чистый SQL даёт контроль над производительностью. Если спросят про альтернативы - скажи про подзапросы и когда они оправданы (например, для EXISTS). Покажи понимание индексов и плана запроса (EXPLAIN).

Что проверяет интервьюер

• Понимание семантики разных JOIN и их отличий.
• Умение строить запросы для связей 1:N и N:M.
• Знание, когда JOIN неэффективен и что делать (индексы, денормализация, несколько запросов).
• Способность объяснить, как это ложится на Python-код и ORM.
• Внимание к деталям: NULL-значения, дубликаты строк, порядок условий.

Типичные ошибки

• Использование INNER JOIN там, где нужен LEFT - теряются записи без связанных данных.
• Забывают про дубликаты при JOIN 1:N - каждая строка родителя повторяется.
• Не создают индексы на внешние ключи - запросы деградируют на больших данных.
• Путают условие JOIN и условие WHERE: фильтр в WHERE после LEFT JOIN убивает смысл LEFT (превращает в INNER).
• При N:M забывают промежуточную таблицу и пытаются сделать один JOIN напрямую.
• В Python не дедуплицируют результат и получают "распухшие" объекты.