Sql query with NOT LIKE IN

Pitfalls in syntax

In SQL, beware that IN is configured for matching precise values, not entire patterns. Therefore, when certain patterns need to be excluded, we should not group these in IN. Instead, chain the conditions together using the AND operator:

-- Incorrect syntax:
SELECT * FROM Table1 WHERE EmpPU NOT LIKE IN ('%CSE%', '%ECE%', '%EEE%');

-- Correct syntax:
SELECT * FROM Table1 
WHERE EmpPU NOT LIKE '%CSE%' 
  AND EmpPU NOT LIKE '%ECE%' 
  AND EmpPU NOT LIKE '%EEE%';


Say you're out to exclude multiple distinct, pattern-free values, the NOT IN statement is your tool of choice:

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

Tips and techniques for pattern exclusion

The NOT EXISTS method

If you're looking for an alternative to NOT LIKE IN, consider this: NOT EXISTS with a subquery:

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

REGEXP in MySQL

MySQL users gain the benefit of using NOT REGEXP, which can exclude multiple patterns within a single condition:

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

Applying LEFT JOIN

Another alternative lies in using a LEFT JOIN in conjunction with a NULL check:

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

Mastering pattern matching with precision

Building filter lists

For complex filters, a temporary table or CTE (Common Table Expression) can help clear up clutter:

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
);


Logic and efficacy

In order to maintain logical clarity it's best to separate exact value exclusion (NOT IN) from pattern matching (NOT LIKE). Optimising efficiency can often be achieved by rewording your query or restructuring your data.

The wildcard weapon

Grasping the power of the % wildcard can greatly amplify the flexibility of NOT LIKE, allowing you to design precise exclusion patterns.

-- Excludes any record with 'abc' anywhere in 'field'
SELECT * FROM table WHERE field NOT LIKE '%abc%'; --Who's afraid of the big bad 'abc'?
