MySQL - NOT EQUAL Operator

MySQL - The NOT EQUAL Operator

In MySQL, the NOT EQUAL operator is used in conditional logic, typically within a WHERE or HAVING clause, to filter for rows that do not match a specific value. There are two interchangeable syntaxes for this operator.

• <>: This is the SQL standard operator for inequality.
• !=: This is a common, C-style alternative also supported by MySQL.

Both operators are functionally identical in MySQL. However, using <> is often recommended for better portability across different SQL database systems.

Scenario Setup

Let’s use a CUSTOMERS table for our examples.

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    AGE INT NOT NULL,
    CITY VARCHAR(50),
    SALARY DECIMAL(18, 2)
);

INSERT INTO CUSTOMERS(ID, NAME, AGE, CITY, SALARY) VALUES
(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),
(8, 'James', 35, NULL, 12000.00); -- Note the NULL city

Basic Usage in a WHERE Clause

The most common use is to exclude rows based on a column’s value.

Example: Filtering by a String Value

This query selects all customers who are not from ‘Mumbai’.

SELECT ID, NAME, CITY, SALARY FROM CUSTOMERS WHERE CITY <> 'Mumbai';

Output

ID	NAME	CITY	SALARY
1	Ramesh	Ahmedabad	2000.00
2	Khilan	Delhi	1500.00
3	Kaushik	Kota	2000.00
5	Hardik	Bhopal	8500.00
6	Komal	Hyderabad	4500.00
7	Muffy	Indore	10000.00

Important: NOT EQUAL and NULL Values

A critical behavior to understand is that NULL represents an unknown or missing value. Therefore, any comparison with NULL using standard operators (=, <>, !=) evaluates to UNKNOWN (treated as false in a WHERE clause). This means the query above did not include the customer James, whose city is NULL.

To correctly include rows with NULL values when filtering, you must explicitly check for them using IS NULL.

-- Correct way to select customers not in Mumbai, including those with no city listed
SELECT ID, NAME, CITY, SALARY
FROM CUSTOMERS
WHERE CITY <> 'Mumbai' OR CITY IS NULL;

Combining with Other Conditions

You can combine the NOT EQUAL operator with AND and OR to create more specific filters.

Example

Find all customers who are not 25 years old AND have a salary greater than 5000.

SELECT ID, NAME, AGE, SALARY
FROM CUSTOMERS
WHERE AGE != 25 AND SALARY > 5000.00;

Output

ID	NAME	AGE	SALARY
5	Hardik	27	8500.00
7	Muffy	24	10000.00
8	James	35	12000.00

Using NOT EQUAL in a Client Program

Executing queries with the NOT EQUAL operator from an application is straightforward. Using prepared statements is crucial to prevent SQL injection.

Python
NodeJS
Java
PHP



import mysql.connector

config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

# Find customers not in a specific city
city_to_exclude = 'Delhi'
query = "SELECT ID, NAME, CITY FROM CUSTOMERS WHERE CITY <> %s"

try:
    with mysql.connector.connect(**config) as connection:
        with connection.cursor(dictionary=True) as cursor:
            # Use a tuple for parameters, even for a single value
            cursor.execute(query, (city_to_exclude,))
            results = cursor.fetchall()
            print(f"Customers not in {city_to_exclude}:")
            for row in results:
                print(f"- ID: {row['ID']}, Name: {row['NAME']}, City: {row['CITY']}")
except mysql.connector.Error as err:
    print(f"Error: {err}")



const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: '127.0.0.1',
    user: 'your_user',
    password: 'your_password',
    database: 'your_database'
});

const getCustomersByAge = async (ageToExclude) => {
    const query = 'SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE != ?';
    try {
        const [rows] = await pool.execute(query, [ageToExclude]);
        console.log(`Customers not aged ${ageToExclude}:`);
        rows.forEach(row => {
            console.log(`- ID: ${row.ID}, Name: ${row.NAME}, Age: ${row.AGE}`);
        });
    } catch (error) {
        console.error('Query failed:', error);
    } finally {
        await pool.end();
    }
};

getCustomersByAge(25);



import java.sql.*;

public class NotEqualOperator {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "your_user";
    private static final String PASS = "your_password";

    public static void main(String[] args) {
        String salaryToExclude = "2000.00";
        String query = "SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY <> ?";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(query)) {

            pstmt.setString(1, salaryToExclude);

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.printf("Customers with salary not equal to %s:%n", salaryToExclude);
                while (rs.next()) {
                    System.out.printf("- ID: %d, Name: %s, Salary: %.2f%n",
                        rs.getInt("ID"),
                        rs.getString("NAME"),
                        rs.getBigDecimal("SALARY")
                    );
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}



<?php
$host = '127.0.0.1';
$db   = 'your_database';
$user = 'your_user';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];

$nameToExclude = 'Komal';
$query = 'SELECT ID, NAME, CITY FROM CUSTOMERS WHERE NAME != :name';

try {
    $pdo = new PDO($dsn, $user, $pass, $options);

    $stmt = $pdo->prepare($query);
    $stmt->execute(['name' => $nameToExclude]);
    $results = $stmt->fetchAll();

    echo "Customers whose name is not $nameToExclude:\n";
    foreach ($results as $row) {
        echo "- ID: {$row['ID']}, Name: {$row['NAME']}, City: {$row['CITY']}\n";
    }

} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

?>