MySQL not equal to (<>, !=) operator

Last update on July 13 2024 08:23:59 (UTC/GMT +8 hours)

not equal to (<>, !=) operator

In SQL, comparison operators are essential for filtering data. The "not equal to" operator allows you to exclude specific values from your query results, enabling more targeted data retrieval.

Syntax:

The "not equal to" operator can be represented in two ways in MySQL:

- '<>'
- '!='

Both can be used interchangeably in most cases, but it's good practice to stick to one for consistency.

MySQL Version: 8.0

Example: MySQL not equal to (<>) operator

The following MySQL statement will fetch the rows from the table publisher which contain publishers those who don't belong to the country USA.

Sample table: publisher

Code:


-- This SQL query selects the name, country, city, and establishment year of publishers
-- Explanation: The query retrieves information from the 'publisher' table, filtering out publishers located in the USA.
SELECT pub_name, country, pub_city, estd  -- Selecting specific columns pub_name, country, pub_city, and estd from the 'publisher' table
FROM publisher  -- Specifying the table 'publisher' from which to retrieve the data
WHERE country <> "USA";  -- Filtering the rows based on the country column, selecting rows where the country is not equal to "USA"

Explanation:

The SELECT statement is used to specify the columns to be retrieved from the 'publisher' table: pub_name, country, pub_city, and estd.

The FROM clause specifies the table from which the data is to be retrieved, which is 'publisher' in this case.

The WHERE clause is used to filter the rows based on a condition.

The comparison operator <> (not equal to) is used to select rows where the country is not equal to "USA".

Output:

mysql> SELECT pub_name,country,pub_city,estd 
    -> FROM publisher 
    -> WHERE country <>"USA";
+------------------------------+-----------+-----------+------------+
| pub_name                     | country   | pub_city  | estd       |
+------------------------------+-----------+-----------+------------+
| BPP Publication              | India     | Mumbai    | 1985-10-01 | 
| New Harrold Publication      | Australia | Adelaide  | 1975-09-05 | 
| Ultra Press Inc.             | UK        | London    | 1948-07-10 | 
| Pieterson Grp. of Publishers | UK        | Cambridge | 1950-07-15 | 
| Novel Publisher Ltd.         | India     | New Delhi | 2000-01-01 | 
+------------------------------+-----------+-----------+------------+
5 rows in set (0.00 sec)

Relational Algebra Expression:

Relational Algebra Tree:

Example : MySQL not equal to (!=) operator with AND using IN operator

The following MySQL statement will fetch the rows from the table book_mast which contain books not written in English and the price of the books are 100 or 200.

Sample table: book_mast

Code:


-- This SQL query selects the name of the book, date of publication, publication language, number of pages, and book price
-- Explanation: The query retrieves information from the 'book_mast' table, filtering out books published in languages other than English and having a price of either 100 or 200.
SELECT book_name, dt_of_pub, pub_lang, no_page, book_price  -- Selecting specific columns book_name, dt_of_pub, pub_lang, no_page, and book_price from the 'book_mast' table
FROM book_mast  -- Specifying the table 'book_mast' from which to retrieve the data
WHERE pub_lang != "English"  -- Filtering the rows based on the publication language column, selecting rows where the language is not equal to "English"
AND book_price IN (100, 200);  -- Further filtering based on the book price column, selecting rows where the price is either 100 or 200

Explanation:

The SELECT statement is used to specify the columns to be retrieved from the 'book_mast' table: book_name, dt_of_pub, pub_lang, no_page, and book_price.

The FROM clause specifies the table from which the data is to be retrieved, which is 'book_mast' in this case.

The WHERE clause is used to filter the rows based on multiple conditions.

The comparison operator != (not equal to) is used to select rows where the publication language is not equal to "English".

The IN operator is used to specify a list of values for which rows should be selected. In this case, it selects rows where the book price is either 100 or 200.

Output:

mysql> SELECT book_name,dt_of_pub,pub_lang,no_page,book_price
    -> FROM book_mast
    -> WHERE pub_lang!="English" AND book_price IN(100,200);
+----------------------------------+------------+----------+---------+------------+
| book_name                        | dt_of_pub  | pub_lang | no_page | book_price |
+----------------------------------+------------+----------+---------+------------+
| Guide to Networking              | 2002-09-10 | Hindi    |     510 |     200.00 | 
| Environment a Sustainable Future | 2003-10-27 | German   |     165 |     100.00 | 
+----------------------------------+------------+----------+---------+------------+
2 rows in set (0.00 sec)

Example : MySQL not equal to ( !=) operator

This following MySQL statement will fetch the rows from the table book_mast which contain books not written in English and the price of the books are less than 100 or more than 200.

Sample table: book_mast

Code:


-- This SQL query selects the name of the book, date of publication, publication language, number of pages, and book price
-- Explanation: The query retrieves information from the 'book_mast' table, filtering out books published in languages other than English and having a price outside of the range from 100 to 200.
SELECT book_name, dt_of_pub, pub_lang, no_page, book_price  -- Selecting specific columns book_name, dt_of_pub, pub_lang, no_page, and book_price from the 'book_mast' table
FROM book_mast  -- Specifying the table 'book_mast' from which to retrieve the data
WHERE pub_lang != "English"  -- Filtering the rows based on the publication language column, selecting rows where the language is not equal to "English"
AND book_price NOT BETWEEN 100 AND 200;  -- Further filtering based on the book price column, selecting rows where the price is not between 100 and 200

Explanation:

The SELECT statement is used to specify the columns to be retrieved from the 'book_mast' table: book_name, dt_of_pub, pub_lang, no_page, and book_price.

The FROM clause specifies the table from which the data is to be retrieved, which is 'book_mast' in this case.

The WHERE clause is used to filter the rows based on multiple conditions.

The comparison operator != (not equal to) is used to select rows where the publication language is not equal to "English".

The NOT BETWEEN operator is used to select rows where the book price is not within the specified range (100 to 200).

Output:

mysql> SELECT book_name,dt_of_pub,pub_lang,no_page,book_price
    -> FROM book_mast
    -> WHERE pub_lang!="English" AND book_price NOT BETWEEN 100 AND 200;
+----------------------------------+------------+----------+---------+------------+
| book_name                        | dt_of_pub  | pub_lang | no_page | book_price |
+----------------------------------+------------+----------+---------+------------+
| Advanced 3d Graphics             | 2004-02-16 | Hindi    |     165 |      56.00 | 
| Human Anatomy                    | 2001-05-17 | German   |      88 |      50.50 | 
| The Experimental Analysis of Cat | 2007-06-09 | French   |     225 |      95.00 | 
| Networks and Telecommunications  | 2002-01-01 | French   |      95 |      45.00 | 
+----------------------------------+------------+----------+---------+------------+
4 rows in set (0.00 sec)

Example Usage in JOIN

Code:


-- This SQL query selects specific columns from two tables (tableA and tableB)
SELECT a.column1, b.column2 
FROM tableA a  -- Specifies tableA as 'a' for reference
JOIN tableB b  -- Joins tableB as 'b' for reference
ON a.id <> b.id;  -- Condition for joining: selects rows where 'id' in tableA is not equal to 'id' in tableB

Explanation:

This query retrieves column1 from tableA and column2 from tableB, joining the two tables on the condition that the id values from both tables are not equal.

This means it will include combinations of records from both tables where the IDs differ.

Use Case Examples:

Data Cleanup: Exclude unwanted records during data import.

User Queries: Filter user inputs where specific values are not acceptable, such as removing banned countries from user registrations.

Performance Considerations:

Using the "not equal to" operator can lead to full table scans, especially on large datasets. Consider indexing columns that are frequently filtered with this operator to improve query performance.

Common Mistakes:

NULL Values: Remember that comparisons with NULL do not return true. For example, 'WHERE column <> NULL' will not filter anything. Instead, use 'WHERE column IS NOT NULL AND column <> value'.

Frequently Asked Questions (FAQ) - MySQL Not Equal To (<>, !=) Operator

1. What is the "not equal to" operator in MySQL?

The "not equal to" operator is used to filter out specific values from query results, allowing for more precise data retrieval.

2. How can we represent the "not equal to" operator in MySQL?

In MySQL, the "not equal to" operator can be represented as either <> or !=. Both operators function similarly.

3. When should we use the MySQL "not equal to" operator?

This operator is useful when we want to exclude certain records based on a specific condition, such as filtering out entries that match a particular value.

4. Are there any performance considerations with using the MySQL "not equal to" operator?

Yes, using this operator may lead to full table scans, especially in large datasets. It’s advisable to index columns frequently filtered with this operator to enhance performance.

5. What are common mistakes to avoid when using the MySQL "not equal to" operator?

A common mistake is comparing with NULL values. For instance, using WHERE column <> NULL will not yield the expected results. Instead, use WHERE column IS NOT NULL AND column <> value.

6. Is there a best practice for using the MySQL "not equal to" operator?

While both representations (<> and !=) are valid, it’s best to choose one and use it consistently throughout your queries for clarity and maintainability.

Slideshow of MySQL Comparison Function and Operators

Previous: NOT BETWEEN AND
Next: NOT IN()