Оптимизация SQL-запроса: замена not in() и where на join

Быстрый ответ

Создайте аналог конструкции NOT IN, используя LEFT JOIN и выбрав записи, у которых отсутствуют соответствия. В таком случае в конструкции WHERE нужно использовать сравнение t2.id IS NULL.

Пример такого запроса:

SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.id IS NULL;

Такой запрор будет возврщать те строки из table1, которые отсутствуют в table2, выполняя действие, аналогичное NOT IN.

Особенности работы с "Not in"

При использовании конструкции NOT IN могут появляться проблемы при наличии значений NULL в списке. Кроме того, этот оператор не всегда является наиболее оптимальным для обработки больших объемов данных.

Эффективная альтернатива: "Not exists"

NOT EXISTS часто является более производительным решением по сравнению с LEFT JOIN, особенно при работе с большим объемом данных и сложными подзапросами.

Пример использования NOT EXISTS:

SELECT t1.*
FROM table1 t1
WHERE NOT EXISTS (
  SELECT 1 
  FROM table2 t2 
  WHERE t1.id = t2.id
);

/ Этот подзапрос исключает из выборки table1 строки, которые присутствуют в table2. /

Взгляните ближе на "Except"

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

Пример использования EXCEPT:

SELECT id
FROM table1
EXCEPT
SELECT id
FROM table2;

/ Если id присутствует в обеих таблицах, соответствующая строка будет исключена из результата. /

Обратите внимание, что EXCEPT не поддерживается во всех SQL-диалектах (например, в MySQL).

Визуализация: Разбираем SQL-операторы

• Действие NOT IN напоминает рабору вышибалы в клубе, который не пускает определенных посетителей.
• LEFT JOIN представляет собой чертеж, на котором пересекающиеся участки отвечают записям из обеих таблиц. Нам нужны уникальные записи, которые не попадают в это пересечение.
• NOT EXISTS похож на прожектор, который освещает только те элементы, которые не находят соответствие в другой таблице.
• Действие EXCEPT можно сравнить с математической операцией вычитания одного набора данных из другого.

Применение Left Join

Когда вы используете LEFT JOIN, всегда проверяйте NULL в столбце из второй (правой) таблицы. Это поможет избежать получения ложно положительных результатов, которые могут возникнуть, если проверяемый столбец содержит NULL.

Производительность при работе с большими объемами данных тоже важна: LEFT JOIN не всегда будет самым быстрым методом.

Praemonitus praemunitus – Предупрежден, значит вооружен

• Будьте осторожны с полями, которые могут содержать значения NULL.
• Не забывайте об индексации столбцов, участвующих в соединениях.
• Будьте внимательны к типам данных, чтобы избежать неожиданных преобразований.

Полезные материалы

1. SQL – Выбрать строки, которых нет в другой таблице – Stack Overflow — Обсуждение преимуществ и недостатков LEFT JOIN по сравнению с NOT EXISTS.
2. SQL LEFT JOIN Keyword — Учебник по LEFT JOIN с понятными примерами.
3. A Visual Explanation of SQL Joins — Визуализированное объяснение принципа работы SQL JOINS на примерах.
4. SQL – NOT IN против NOT EXISTS – Stack Overflow — Сравнение NOT IN и NOT EXISTS: обсудим их применение на практике.
5. Преобразование NOT IN в JOIN в SQL – Database Tutorial — Каки есть профессиональные способы преобразования условий NOT IN в запросы типа JOIN.