Как при двух таблицах в SQL найти пользователей у которых нет соответствующей записи во второй таблице

Ответ

Как найти пользователей в SQL, у которых нет соответствующей записи во второй таблице

Это классическая задача в SQL — найти записи из одной таблицы, которых нет в другой. Рассмотрю несколько подходов.

1. LEFT JOIN с IS NULL (рекомендуется)

Самый понятный и часто используемый способ:

-- Найти пользователей БЕЗ заказов
SELECT u.id, u.email, u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

Как это работает:

1. LEFT JOIN берёт ВСЕ записи из users
2. Присоединяет соответствующие orders
3. Если для пользователя нет заказа, all столбцы orders будут NULL
4. WHERE o.id IS NULL оставляет только строки без совпадений

Практический пример:

-- Таблица users
-- id | email            | name
-- 1  | john@example.com | John
-- 2  | jane@example.com | Jane
-- 3  | bob@example.com  | Bob

-- Таблица orders
-- id | user_id | amount
-- 1  | 1       | 100
-- 2  | 1       | 200
-- 3  | 3       | 150

-- Результат: только Bob (id=3) имеет заказ, Jane (id=2) без заказов
SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
-- Результат: Jane

2. NOT IN (работает, но медленнее)

-- Найти пользователей, которых НЕТ в таблице заказов
SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (SELECT DISTINCT user_id FROM orders);

Недостатки:

• Медленнее чем LEFT JOIN
• Проблемы с NULL значениями (если в orders есть NULL user_id)

-- Проблемный случай:
WHERE u.id NOT IN (1, 2, NULL)  -- ВСЕГДА вернёт FALSE!
-- Решение: добавить IS NOT NULL
WHERE u.id NOT IN (SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL);

3. NOT EXISTS (самый быстрый)

-- Самый оптимальный способ для больших таблиц
SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Преимущества:

• Обычно быстрее чем LEFT JOIN
• Не создаёт лишние соединения
• Автоматически останавливается при нахождении первого совпадения

4. Сравнение методов — примеры

-- МЕТОД 1: LEFT JOIN (понятный)
SELECT u.id, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL
GROUP BY u.id;

-- МЕТОД 2: NOT IN (простой, но медленный)
SELECT id, email
FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

-- МЕТОД 3: NOT EXISTS (быстрый)
SELECT id, email
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders WHERE user_id = u.id
);

-- МЕТОД 4: EXCEPT (для некоторых СУБД)
SELECT id FROM users
EXCEPT
SELECT DISTINCT user_id FROM orders;

5. Для PostgreSQL — использование EXCEPT

-- Найти ID пользователей без заказов
SELECT id FROM users
EXCEPT
SELECT DISTINCT user_id FROM orders;

6. Сложный пример — найти пользователей без заказов в определённый период

SELECT u.id, u.email, u.name, MAX(o.created_at) as last_order
FROM users u
LEFT JOIN orders o 
    ON u.id = o.user_id 
    AND o.created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
WHERE o.id IS NULL
GROUP BY u.id;

7. В Java с JPA/Hibernate

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    // Через @Query
    @Query("SELECT u FROM User u WHERE u.id NOT IN (" +
           "SELECT DISTINCT o.user.id FROM Order o)")
    List<User> findUsersWithoutOrders();
    
    // Или через NOT EXISTS (лучше)
    @Query("SELECT u FROM User u WHERE NOT EXISTS (" +
           "SELECT 1 FROM Order o WHERE o.user.id = u.id)")
    List<User> findUsersWithoutOrdersOptimal();
}

// Нативный SQL
@Query(value = "SELECT u.* FROM users u " +
       "LEFT JOIN orders o ON u.id = o.user_id " +
       "WHERE o.id IS NULL",
       nativeQuery = true)
List<User> findUsersWithoutOrdersNative();

8. В Java с Criteria API

@Service
public class UserService {
    
    @Autowired
    private EntityManager entityManager;
    
    public List<User> findUsersWithoutOrders() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> query = cb.createQuery(User.class);
        Root<User> user = query.from(User.class);
        
        // Подзапрос: заказы
        Subquery<Order> orderSubquery = query.subquery(Order.class);
        Root<Order> order = orderSubquery.from(Order.class);
        orderSubquery.select(order)
            .where(cb.equal(order.get("user"), user));
        
        // NOT EXISTS
        query.where(cb.not(cb.exists(orderSubquery)));
        
        return entityManager.createQuery(query).getResultList();
    }
}

9. Использование ANTI JOIN в больших объёмах

Для очень больших таблиц:

-- Оптимально для big data
SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (
    SELECT /*+ BROADCAST(o) */ user_id
    FROM orders o
    WHERE user_id IS NOT NULL
);

10. Сравнение производительности

11. Best Practices

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

Краткая рекомендация: начни с LEFT JOIN IS NULL для читаемости, если медленно — переходи на NOT EXISTS.