MySQL - NOT LIKE Operator

Hello, aspiring database enthusiasts! Today, we're going to dive into the wonderful world of MySQL and explore a powerful tool in our SQL toolkit: the NOT LIKE operator. As your friendly neighborhood computer teacher, I'm excited to guide you through this journey, even if you've never written a line of code before. So, buckle up and let's get started!

MySQL NOT LIKE Operator

The NOT LIKE operator is like the rebellious sibling of the LIKE operator. While LIKE helps us find matching patterns, NOT LIKE does the opposite – it helps us find everything that doesn't match a specific pattern. It's like telling your computer, "Show me everything except this!"

Let's start with a simple example. Imagine we have a table called books with a column title. If we want to find all books that don't start with the letter 'A', we could use:

SELECT * FROM books WHERE title NOT LIKE 'A%';

Here's what this code does:

• SELECT *: This tells MySQL to select all columns.
• FROM books: We're looking in the books table.
• WHERE title NOT LIKE 'A%': This is the magic part. It says, "Give me all titles that don't start with 'A'."

The % is a wildcard that means "anything can follow." So 'A%' means "A followed by anything."

Using NOT LIKE Operator with Wildcards

Now, let's spice things up with some wildcards! MySQL provides two main wildcards:

• %: Represents zero, one, or multiple characters
• _: Represents a single character

Here's a table of common wildcard uses:

Wildcard	Description	Example
%	Any number of characters	'%adventure%'
_	Single character	'_at'
[charlist]	Any single character in charlist	'[abc]%'
[^charlist] or [!charlist]	Any single character not in charlist	'[^abc]%'

Let's say we want to find all book titles that don't end with "ing":

SELECT * FROM books WHERE title NOT LIKE '%ing';

Or maybe we want books that don't have exactly three characters:

SELECT * FROM books WHERE title NOT LIKE '___';

Each underscore represents one character, so '___' means "exactly three characters."

Using NOT LIKE Operator with AND/OR Operators

Sometimes, we need to combine multiple conditions. That's where AND and OR come in handy. Let's find books that don't start with 'T' and don't end with 'ing':

SELECT * FROM books 
WHERE title NOT LIKE 'T%' 
AND title NOT LIKE '%ing';

Or maybe we want books that neither start with 'A' nor end with 'z':

SELECT * FROM books 
WHERE title NOT LIKE 'A%' 
OR title NOT LIKE '%z';

Remember, AND means both conditions must be true, while OR means at least one condition must be true.

NOT LIKE Operator on Strings

The NOT LIKE operator is particularly useful when working with strings. Let's say we have a customers table and we want to find all customers whose names don't contain 'son':

SELECT * FROM customers 
WHERE name NOT LIKE '%son%';

This query will return all names that don't have 'son' anywhere in them. So 'Johnson' and 'Sonny' would be excluded, but 'Smith' and 'Brown' would be included.

Here's a fun twist: what if we want to find names that don't have 'a' as the second letter?

SELECT * FROM customers 
WHERE name NOT LIKE '_a%';

This query says, "Show me all names where the second letter is not 'a'."

NOT LIKE Operator Using a Client Program

Now, let's put all this knowledge into practice using a MySQL client program. I'll use the MySQL command-line client for this example, but the principles apply to any MySQL client.

First, let's create a simple employees table:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50)
);

INSERT INTO employees (name, position) VALUES
('John Doe', 'Manager'),
('Jane Smith', 'Developer'),
('Bob Johnson', 'Designer'),
('Alice Brown', 'Tester');

Now, let's find all employees whose positions don't start with 'D':

SELECT * FROM employees 
WHERE position NOT LIKE 'D%';

This should return John Doe (Manager) and Alice Brown (Tester).

Let's try something more complex. We want to find employees whose names don't start with 'J' and whose positions don't end with 'er':

SELECT * FROM employees 
WHERE name NOT LIKE 'J%' 
AND position NOT LIKE '%er';

This should return Alice Brown (Tester).

And there you have it! You've just mastered the NOT LIKE operator in MySQL. Remember, practice makes perfect, so don't be afraid to experiment with your own queries. Who knows? You might discover some interesting patterns in your data that you never noticed before!

As we wrap up, I want to share a little secret from my years of teaching: the best way to learn SQL is to treat it like a puzzle game. Each query is a riddle waiting to be solved. So, put on your detective hat and start exploring your data. Happy querying, future database wizards!

Credits: Image by storyset