Как я могу выполнить запрос SQL "NOT IN" быстрее?

У меня есть таблица (EMAIL) адресов электронной почты:

EmailAddress
------------
[email protected]
[email protected]
[email protected]
[email protected]

и таблицу (BLACKLIST) адресов черного списка:

EmailAddress
------------
[email protected]
[email protected]

и я хочу выбрать те адреса электронной почты, которые находятся в таблице EMAIL, но НЕ в таблице BLACKLIST. Я делаю:

SELECT EmailAddress
FROM EMAIL
WHERE EmailAddress NOT IN
   (
      SELECT EmailAddress
      FROM BLACKLIST
   )

но когда количество строк становится очень высоким, производительность ужасна.

Как я могу это сделать лучше? (Предположим, если возможно, общий SQL, если нет, предположим T-SQL.)

Ответ 1

Вы можете использовать левое внешнее соединение или предложение not exists.

Левое внешнее соединение:

select E.EmailAddress
  from EMAIL E left outer join BLACKLIST B on (E.EmailAddress = B.EmailAddress)
 where B.EmailAddress is null;

Не существует:

select E.EmailAddress
  from EMAIL E where not exists
         (select EmailAddress from BLACKLIST B where B.EmailAddress = E.EmailAddress)

Оба представляют собой довольно общие решения SQL (не зависят от конкретного механизма БД). Я бы сказал, что последний немного более результативен (но не очень). Но определенно более результативнее, чем not in.

Как отмечали комментаторы, вы также можете попробовать создать индекс на BLACKLIST(EmailAddress), что должно ускорить выполнение вашего запроса.

Ответ 2

NOT IN отличается от NOT EXISTS, если черный список допускает значение null как EmailAddress. Если есть одно нулевое значение, результат запроса всегда будет возвращать нулевые строки, потому что NOT IN (null) неизвестно /false для каждого значения. Поэтому планы запросов отличаются незначительным, но я не думаю, что это повлияет на производительность.

Предполагается создать новую таблицу с именем VALIDEMAIL, добавить триггер в BLACKLIST, который удаляет адреса из VALIDEMAIL, когда строки вставлены, и добавлять к VALIDEMAIL при удалении из BLACKLIST. Затем замените EMAIL видом, который является объединением как VALIDEMAIL, так и BLACKLIST.

Ответ 3

select E.EmailAddress
  from EMAIL E where not exists
         (select EmailAddress from BLACKLIST B where B.EmailAddress = E.EmailAddress)

Равно (кстати есть хозяин)

select EmailAddress from mail.EMAIL 
EXCEPT
select EmailAddress from mail.BLACKLIST

даст вам строки, которые отличаются, даже если NULL в EmailAddress