Сталкивался ли с Anti JOIN в PostgreSQL

Anti JOIN в PostgreSQL

Anti JOIN — это один из типов соединений, который возвращает строки из левой таблицы, для которых НЕ существует соответствующих строк в правой таблице. Это противоположность обычному INNER JOIN.

Когда используется Anti JOIN

Anti JOIN решает задачи вроде:

• Найти заказы, у которых нет доставок
• Получить пользователей без аккаунтов в системе
• Выявить товары, не входящие в какие-либо категории
• Найти записи с несоответствием в связанных таблицах

Синтаксис Anti JOIN

В PostgreSQL Anti JOIN реализуется несколькими способами:

-- Вариант 1: NOT EXISTS (самый эффективный)
SELECT o.order_id, o.customer_id
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM deliveries d WHERE d.order_id = o.order_id
);

-- Вариант 2: NOT IN
SELECT o.order_id, o.customer_id
FROM orders o
WHERE o.order_id NOT IN (
    SELECT d.order_id FROM deliveries d
);

-- Вариант 3: LEFT JOIN + IS NULL
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN deliveries d ON o.order_id = d.order_id
WHERE d.order_id IS NULL;

Производительность

NOT EXISTS — предпочтительный способ:

• Работает быстрее благодаря оптимизатору
• Поддерживает подзапросы с корреляциями
• Останавливает сканирование при первом совпадении

-- Хорошо: оптимизатор может использовать индекс
SELECT u.id, u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM accounts a WHERE a.user_id = u.id
);

NOT IN может быть медленнее:

• Особенно если в подзапросе NULL значения
• NULL в подзапросе сделает результат пустым

-- Может быть медленнее и вернуть неправильный результат
SELECT u.id FROM users u
WHERE u.id NOT IN (
    SELECT user_id FROM accounts WHERE user_id IS NOT NULL
);

LEFT JOIN + IS NULL — также эффективен, особенно если нужны данные из обеих таблиц:

SELECT o.*, COUNT(d.id) as delivery_count
FROM orders o
LEFT JOIN deliveries d ON o.order_id = d.order_id
GROUP BY o.order_id
HAVING COUNT(d.id) = 0;

Практические примеры

# Python + psycopg2
import psycopg2

conn = psycopg2.connect("dbname=shop user=postgres")
cur = conn.cursor()

# Найти товары без отзывов
cur.execute("""
    SELECT p.id, p.name
    FROM products p
    WHERE NOT EXISTS (
        SELECT 1 FROM reviews r WHERE r.product_id = p.id
    )
""")

unreviewed = cur.fetchall()
print(f"Товары без отзывов: {unreviewed}")

conn.close()

Индексы для оптимизации

Для Anti JOIN критично наличие индексов на столбцах соединения:

CREATE INDEX idx_deliveries_order_id ON deliveries(order_id);
CREATE INDEX idx_accounts_user_id ON accounts(user_id);

Выводы

1. Используй NOT EXISTS как первый выбор — самый производительный
2. Избегай NOT IN если в подзапросе могут быть NULL
3. LEFT JOIN + IS NULL хорош для относительно небольших таблиц
4. Всегда добавляй индексы на столбцы соединения
5. EXPLAIN ANALYZE — твой друг для проверки плана

Anti JOIN — важный инструмент для работы с несоответствиями в данных и выявления "сирот" в базе.