MySQL - NOT LIKE Operator

MySQL - The NOT LIKE Operator

This tutorial explains how to use the `NOT LIKE` operator in MySQL to filter data that does not match a specified string pattern. We will cover its syntax, usage with wildcards, and combination with other logical operators. You'll also learn how to use it securely in client applications.

Understanding the NOT LIKE Operator

The NOT LIKE operator is the logical negation of the LIKE operator. While LIKE is used to find rows that match a certain pattern, NOT LIKE is used to find all rows that do not match that pattern. It is essential for exclusion-based filtering.

Like its counterpart, NOT LIKE relies on wildcard characters to define patterns for matching.

Syntax

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

Using Wildcards with NOT LIKE

The power of NOT LIKE comes from these two standard SQL wildcards:

Wildcard	Description
%	The percent sign represents zero, one, or multiple characters.
_	The underscore represents exactly one character.

Note on advanced patterns: For more complex pattern matching, such as character ranges or repetitions, MySQL’s regular expression operators (REGEXP or RLIKE, and their negations NOT REGEXP / NOT RLIKE) are more powerful and flexible.

Prerequisites: Sample Data

Let’s use our CUSTOMERS table for the examples.

-- Assume the CUSTOMERS table is created and populated as in previous tutorials.
SELECT * FROM CUSTOMERS;

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Example: Names Not Starting with ‘K’

To find all customers whose name does not start with the letter ‘K’:

SELECT * FROM CUSTOMERS WHERE NAME NOT LIKE 'K%';

This will exclude ‘Khilan’, ‘Kaushik’, and ‘Komal’.

Example: Addresses Not Ending in ‘bad’

To find all customers whose address does not end with ‘bad’:

SELECT * FROM CUSTOMERS WHERE ADDRESS NOT LIKE '%bad';

This will exclude ‘Ahmedabad’ and ‘Hyderabad’.

Example: Names Not Containing ‘al’

To find all customers whose name does not contain the substring ‘al’:

SELECT * FROM CUSTOMERS WHERE NAME NOT LIKE '%al%';

This will exclude ‘Chaitali’ and ‘Komal’.

Example: Using the Underscore _ Wildcard

To find all customers whose address does not have ‘o’ as the second letter:

SELECT * FROM CUSTOMERS WHERE ADDRESS NOT LIKE '_o%';

This will exclude ‘Kota’, ‘Bhopal’, and ‘Komal’ (address ‘Hyderabad’).

Combining NOT LIKE with AND/OR

You can create more complex filtering logic by combining NOT LIKE with other operators like AND and OR.

Example

Let’s find customers whose name does not start with ‘K’ AND whose address does not start with ‘M’.

SELECT * FROM CUSTOMERS
WHERE NAME NOT LIKE 'K%'
  AND ADDRESS NOT LIKE 'M%';

This query first filters out all names starting with ‘K’, and from that result, it further filters out any addresses starting with ‘M’.

Using NOT LIKE on Standalone Strings

You can also use NOT LIKE in a SELECT statement without a FROM clause to test patterns directly. It returns 1 (true) if the pattern does not match, 0 (false) if it does, and NULL if either operand is NULL.

SELECT 'Hello World' NOT LIKE 'Hello%'; -- Result: 0 (because it does match)
SELECT 'Hello World' NOT LIKE 'Bye%';   -- Result: 1 (because it does not match)
SELECT 'Hello World' NOT LIKE NULL;      -- Result: NULL

Using NOT LIKE in a Client Program

When using NOT LIKE from an application, always use prepared statements to safely pass the pattern, especially if it contains user-provided data. This prevents SQL injection.

Node.js (with mysql2/promise)

require('dotenv').config();
const mysql = require('mysql2/promise');

async function findCustomers(namePattern) {
  let connection;
  try {
    connection = await mysql.createConnection(process.env.DATABASE_URL || 'mysql://root:password@localhost/your_database');
    const query = 'SELECT ID, NAME FROM CUSTOMERS WHERE NAME NOT LIKE ?';
    const [rows] = await connection.execute(query, [namePattern]);
    console.log(`Customers whose name does NOT match '${namePattern}':`);
    console.log(rows);
  } catch (error) {
    console.error('Database query failed:', error.message);
  } finally {
    if (connection) await connection.end();
  }
}

// Find customers whose names do not start with 'K'
findCustomers('K%');

Python (with mysql-connector-python)

import mysql.connector
import os

def find_customers(name_pattern):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            user=os.getenv('DB_USER', 'root'),
            password=os.getenv('DB_PASSWORD', 'password'),
            database=os.getenv('DB_NAME', 'your_database')
        )
        cursor = connection.cursor(dictionary=True)
        query = "SELECT ID, NAME FROM CUSTOMERS WHERE NAME NOT LIKE %s"
        cursor.execute(query, (name_pattern,))
        results = cursor.fetchall()
        print(f"Customers whose name does NOT match '{name_pattern}':")
        for row in results:
            print(row)
    except mysql.connector.Error as err:
        print(f"Database error: {err}")
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

# Find customers whose names do not end with 'h'
find_customers('%h')

PHP (with PDO)

<?php
function findCustomers(string $namePattern): void
{
    $dsn = sprintf("mysql:host=%s;dbname=%s;charset=utf8mb4",
        $_ENV['DB_HOST'] ?? 'localhost',
        $_ENV['DB_NAME'] ?? 'your_database'
    );
    $options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC];

    try {
        $pdo = new PDO($dsn, $_ENV['DB_USER'] ?? 'root', $_ENV['DB_PASSWORD'] ?? 'password', $options);
        $stmt = $pdo->prepare('SELECT ID, NAME FROM CUSTOMERS WHERE NAME NOT LIKE :pattern');
        $stmt->execute(['pattern' => $namePattern]);
        $results = $stmt->fetchAll();

        echo "Customers whose name does NOT match '{$namePattern}':\n";
        print_r($results);

    } catch (\PDOException $e) {
        error_log($e->getMessage());
        echo "An error occurred.\n";
    }
}

// Find customers whose name does not contain 'a'
findCustomers('%a%');