Использование SQL условия NOT LIKE в связке с IN: гайд

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

Чтобы отфильтровать строки, где исключаются различные нежелательные шаблоны, следует применять NOT LIKE для каждого индивидуального шаблона в сочетании с оператором AND:

SELECT * FROM таблица
WHERE поле NOT LIKE '%нежелательно%'
  AND поле NOT LIKE '%исключить%';

Подставьте таблица, поле, нежелательно и исключить актуальными для вашей базы данных параметрами. Каждое условие NOT LIKE служит для исключения строк, содержащих определённые последовательности символов, указанные в шаблонах.

Ошибки в синтаксисе

Необходимо помнить, что в SQL IN применяется для сопоставления с конкретным списком значений, а не с шаблонами. Для исключения нежелательных шаблонов используйте оператор AND для соединения условий NOT LIKE, вместо попыток объединить их через IN:

-- Это неправильно:
SELECT * FROM Table1 WHERE EmpPU NOT LIKE IN ('%CSE%', '%ECE%', '%EEE%');

-- Правильный вариант:
SELECT * FROM Table1 
WHERE EmpPU NOT LIKE '%CSE%' 
  AND EmpPU NOT LIKE '%ECE%' 
  AND EmpPU NOT LIKE '%EEE%';

Если нужно исключить из выборки конкретные значения без использования шаблонов, применяйте NOT IN:

SELECT * FROM Table1 WHERE EmpPU NOT IN ('CSE', 'ECE', 'EEE');

Советы и методы исключения шаблонов

Метод NOT EXISTS

В качестве альтернативы NOT LIKE IN можно использовать NOT EXISTS в сочетании с подзапросом:

SELECT * FROM Table1 t1
WHERE NOT EXISTS (
    SELECT 1 FROM Table2 t2
    WHERE t1.EmpPU LIKE t2.UnwantedPattern
);

Подробнее об этом расскажет наш спикер на видео

NOT REGEXP в MySQL

В MySQL возможно применение NOT REGEXP, позволяющего одним выражением отфильтровать несколько шаблонов:

SELECT * FROM Table1 WHERE EmpPU NOT REGEXP 'CSE|ECE|EEE';

Применение LEFT JOIN

Применяя соединение LEFT JOIN и проверку значения на NULL, можно отфильтровать строки по шаблону:

SELECT t1.* FROM Table1 t1
LEFT JOIN PatternTable pt ON t1.EmpPU LIKE pt.Pattern
WHERE pt.Pattern IS NULL;

Визуализация

Разберём пример выбора фруктов для корзины, где мы хотим исключить некоторые из них:

В корзине есть:
- 🍎
- 🍌
- 🍇
- 🍒

Нужно избегать: [🍌, 🍇]

Используя SQL для фильтрации, мы можем отобрать нужные фрукты:

SELECT *
FROM FruitBasket
WHERE Fruit NOT IN ('🍌', '🍇');

После выполнения запроса в корзине останутся:

Остались фрукты:
- 🍎
- 🍒

Таким образом, NOT IN помогает нам исключить определённые элементы, как и в случае выбора фруктов. 🚫🍌🚫🍇

Искусство точного сопоставления шаблонов

Создание списков исключений

Для более сложных задач фильтрации удобно использовать временную таблицу или общее табличное выражение (CTE), объединяющее исключаемые шаблоны и упрощающее структуру запроса:

WITH ExcludePatterns AS (
  SELECT '%CSE%' AS Pattern
  UNION ALL SELECT '%ECE%'
  UNION ALL SELECT '%EEE%'
)
SELECT * FROM Table1
WHERE NOT EXISTS (
  SELECT 1 FROM ExcludePatterns
  WHERE Table1.EmpPU LIKE Pattern
);

Логика и оптимизация

Особенно уместно разделить процесс исключения конкретных значений (NOT IN) и сопоставления по шаблонам (NOT LIKE). Такая реорганизация логики запросов часто приводит к повышению их производительности, что также может быть достигнуто путем оптимизации структуры данных.

Использование подстановочного символа

Умелое применение символа % в NOT LIKE расширяет возможности и увеличивает гибкость формирования запросов, позволяя исключить записи, содержащие определённые последовательности символов в любом месте поля:

-- Исключаем записи, в которых 'abc' может появляться где угодно в 'поле'
SELECT * FROM таблица WHERE поле NOT LIKE '%abc%'; -- Будьте осторожны с злобной 'abc'!

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

1. Оптимизация запросов в SQL под управлением NOT EXISTS vs NOT IN на Database Administrators Stack Exchange — размышления и дискуссии сообщества о способах оптимизации запросов на SQL.
2. SQL Субзапросы – NOT IN vs. NOT EXISTS — обсуждение на Stack Overflow об отличиях и сравнении производительности этих двух подходов.
3. Документация MySQL по сопоставлению шаблонов — официальное руководство MySQL по синтаксису и применению шаблонов сопоставления.