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 wherecolumn1
does not match the specifiedpattern
.
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
.

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
.

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.

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'.

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'.

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'.

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.