MySQL NOT LIKE Operator

MySQL NOT LIKE Operator

The MySQL NOT LIKE operator is used to search for records that do not match a specified pattern in a column. This operator is essential for querying data that does not match a particular pattern in SQL queries.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column1 NOT LIKE pattern;

The NOT LIKE operator has the following components:

  • column1, column2, ...: The columns to be retrieved.
  • table_name: The name of the table from which to retrieve the data.
  • column1 NOT LIKE pattern: The condition to filter the records where column1 does not match the specified pattern.

Example MySQL NOT LIKE Operator

Let's look at some examples of the MySQL NOT LIKE operator:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    email VARCHAR(100)
);

This query creates a table named employees with columns for id, first_name, last_name, department, and email.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (first_name, last_name, department, email)
VALUES ('John', 'Doe', 'HR', 'john.doe@example.com'),
       ('Jane', 'Smith', 'IT', 'jane.smith@example.com'),
       ('Jim', 'Brown', 'IT', 'jim.brown@example.com'),
       ('Jake', 'White', 'HR', 'jake.white@example.com'),
       ('Jill', 'Green', 'Marketing', 'jill.green@example.com');

This query inserts five rows into the employees table.

MySQL INSERT INTO TABLE

Step 4: Using NOT LIKE with WHERE Clause

Use the NOT LIKE operator to filter records that do not match a pattern:

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

This query retrieves all columns from the employees table where the first_name does not contain with 'i'.

MySQL NOT LIKE WITH WHERE CLAUSE

Step 5: Using NOT LIKE with Wildcards

Use the NOT LIKE operator with wildcards:

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

This query retrieves all columns from the employees table where the email does not end with 'example.com'.

MySQL NOT LIKE WITH WILDCARDS

Step 6: Using NOT LIKE with Multiple Patterns

Use the NOT LIKE operator with multiple patterns:

SELECT * 
FROM employees 
WHERE first_name NOT LIKE '%i%' AND last_name NOT LIKE 'D%';

This query retrieves all columns from the employees table where the first_name does not contain 'i' and the last_name does not start with 'D'.

MySQL NOT LIKE WITH MULTIPLE PATTERNS

Conclusion

The MySQL NOT LIKE operator is a powerful tool for searching for records that do not match a specified pattern in a column. Understanding how to use the NOT LIKE operator is essential for effective data querying and analysis in MySQL.