MySQL

In MySQL, the NOT LIKE operator is used to filter rows where a column's value does not match a specified pattern. It works as the opposite of the LIKE operator and allows you to exclude records based on patterns within string data. This operator is particularly useful when you need to find rows that do not conform to specific text formats or exclude certain patterns from your result set.

Here’s how to use the NOT LIKE operator in MySQL, along with examples and helpful tips for new students:

1. Basic Syntax of NOT LIKE:

   • The NOT LIKE operator is used in the WHERE clause to exclude rows where the column value matches a specified pattern.
   • Syntax:

   SELECT * 
   FROM employees 
   WHERE first_name NOT LIKE 'A%';

   • This query returns all employees whose first names do not start with the letter "A". The % wildcard represents any sequence of characters.

2. Using % Wildcard with NOT LIKE:

   • The % wildcard can be used with NOT LIKE to exclude rows based on patterns with any number of characters.
   • Example:

   SELECT * 
   FROM employees 
   WHERE last_name NOT LIKE '%son';

   • This query returns all employees whose last names do not end with "son", excluding names like "Johnson" or "Wilson".

3. Using _ Wildcard with NOT LIKE:

   • The _ wildcard matches exactly one character, and with NOT LIKE, it helps exclude records where there are specific single-character patterns.
   • Example:

   SELECT * 
   FROM employees 
   WHERE department_name NOT LIKE 'R_n%';

   • This query excludes departments where the name starts with "R", followed by any single character, and then "n". For example, it would exclude departments like "Runway" or "Rentals".

4. Combining NOT LIKE with Other Conditions:

   • You can combine NOT LIKE with AND or OR to build more complex queries that filter based on multiple conditions.
   • Example:

   SELECT * 
   FROM employees 
   WHERE first_name NOT LIKE 'J%' 
   AND department_id = 2;

   • This query returns employees who do not have first names starting with "J" and who work in department 2.

5. Case Sensitivity in NOT LIKE:

   • By default, NOT LIKE is case-insensitive in MySQL, which means that it will match patterns regardless of case. If you need case-sensitive comparisons, use the BINARY keyword.
   • Example:

   SELECT * 
   FROM employees 
   WHERE BINARY first_name NOT LIKE 'john%';

   • This query excludes rows where the first name starts with "john", case-sensitive, so names like "John" or "JOHN" are not excluded.

6. Using NOT LIKE in Joins:

   • The NOT LIKE operator can be used in queries involving joins to exclude rows based on patterns in related tables.
   • Example:

   SELECT e.first_name, d.department_name 
   FROM employees e 
   JOIN departments d 
   ON e.department_id = d.department_id 
   WHERE d.department_name NOT LIKE 'Marketing%';

   • This query returns employees who do not work in departments whose names start with "Marketing".

7. Performance Considerations:

   • Using NOT LIKE with wildcards, especially at the beginning of the pattern (%something), can impact query performance on large datasets. Indexing the relevant columns may help optimize performance.
   • Example:

   SELECT * 
   FROM employees 
   WHERE email NOT LIKE '%@company.com';

   • This query retrieves employees whose email addresses do not end with "@company.com". However, this type of query can be slow if the column is not indexed.

The NOT LIKE operator is a valuable tool when you need to exclude rows based on string patterns in MySQL. By combining it with wildcards and other conditions, you can filter out data that matches specific patterns, giving you more control over your queries and results.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT *
FROM org_client
WHERE last_name NOT LIKE 'M%';

SELECT *
FROM org_client
WHERE first_name NOT LIKE '%e';

Example 1:

Explore the procedure of retrieving data from a designated table using the SQL NOT LIKE operator. Fetch records for clients whose last names do not start with 'M%', where '%' indicates the potential presence of zero or more characters following 'M'.

Example 1 - Raw data from client table

Example 1 - Query

SELECT * FROM org_client WHERE last_name NOT LIKE 'M%';

Example 1 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 1 - Query Output

Example 2:

Retrieve entries for clients whose first names do not conclude with '%e'.

Example 2 - Query

SELECT * FROM org_client WHERE first_name NOT LIKE '%e';

Example 2 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 2 - Query Output