How To Write Not Equal To In Sql

3 min read

In SQL, writing not equal to is a fundamental comparison operation that lets you filter rows where one value does not match another. Even so, the two most common ways to express not equal to in SQL are the standard <> operator and the widely supported ! = operator. Knowing when and how to use each one—and how SQL handles edge cases such as NULL values—can make your queries more accurate, portable, and readable.

People argue about this. Here's where I land on it.

The Two SQL Not Equal Operators: <> and !=

SQL provides two operators for testing inequality:

  • <> — the ISO standard SQL “not equal to” operator.
  • != — a popular alternative that is supported by most major database systems.

Both operators return a Boolean result: TRUE when the two values are different, FALSE when they are equal, and UNKNOWN when NULL is involved. In everyday queries, they are usually interchangeable Not complicated — just consistent..

SELECT *
FROM employees
WHERE department <> 'Sales';
SELECT *
FROM employees
WHERE department != 'Sales';

The first query uses the standard operator. In practice, the second uses the common shorthand. In MySQL, PostgreSQL, SQL Server, SQLite, and many other systems, both queries return the same rows. Oracle also supports both, although <> is often preferred in traditional Oracle code. For maximum portability across database platforms, <> is the safer choice because it is part of the SQL standard Not complicated — just consistent..

How to Use Not Equal To in a WHERE Clause

The most common place to use not equal to in SQL is inside a WHERE clause. The basic syntax is:

SELECT column_list
FROM table_name
WHERE column_name <> value;

To give you an idea, to find products that are not priced at exactly 100:

SELECT product_name, price
FROM products
WHERE price <> 100;

To find customers whose country is not Canada:

SELECT customer_id, customer_name
FROM customers
WHERE country <> 'Canada';

You can also use != in the same position:

SELECT customer_id, customer_name
FROM customers
WHERE country != 'Canada';

When the value is text, wrap it in single quotes. In real terms, when the value is numeric, do not use quotes. When the value is a date, use a valid date literal or a database-specific date format.

SELECT order_id, order_date
FROM orders
WHERE order_date <> '2024-01-01';

Using Not Equal To with Different Data Types

The not equal to in SQL operator works with numbers, text, dates, and many other comparable data types. The key requirement is that the values being compared must be compatible But it adds up..

  • Numbers: WHERE quantity <> 0
  • Text: WHERE status <> 'Cancelled'
  • Dates: WHERE created_at <> '2024-05-01'
  • Boolean values: WHERE is_active <> TRUE

Text comparisons can be case-sensitive or case-insensitive depending on the database and collation settings. Take this: in some systems 'Sales' <> 'sales' may evaluate to TRUE, while in others it may evaluate to FALSE. If case sensitivity matters, use the appropriate collation or a case-insensitive comparison function supported by your database Small thing, real impact..

Honestly, this part trips people up more than it should.

Handling NULL Values with Not Equal To

NULL is one of the most important concepts to understand when using not equal to in SQL. NULL represents an unknown or missing value, and it does not behave like an ordinary value. Any direct comparison with NULL—including <> and !=—returns UNKNOWN, not TRUE Worth keeping that in mind. Practical, not theoretical..

As an example, this query does not return rows where middle_name is NULL:

SELECT *
FROM people
WHERE middle_name <> 'A';

Rows with a NULL middle_name are excluded because NULL <> 'A' is unknown. If you want to include rows where the value is not equal to 'A' and also include NULLs, you must handle NULL explicitly:

SELECT *
FROM people
WHERE middle_name <> 'A'
   OR middle_name IS NULL;

If you want to exclude NULLs and compare only known values, add IS NOT NULL:

SELECT *
FROM people
WHERE middle_name IS NOT NULL
  AND middle_name <> 'A';

Never write column <> NULL or column !Worth adding: = NULL. Those expressions do not test for NULL; they produce UNKNOWN.

Brand New Today

What's Just Gone Live

More Along These Lines

Other Perspectives

Thank you for reading about How To Write Not Equal To In Sql. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home