kevinemmetfoley.com → cloud → certification sql business → not equal in sql
Posted on by Paxton Baldwin

Not Equal in SQL

SQL query with comparison operators highlighted on a dark screen

The "not equal" operator in SQL returns rows where two values are different. It uses either <> or != depending on the database system.

SQL uses comparison operators to evaluate conditions in queries. The "not equal" operator checks whether two expressions do not have the same value. This is essential for filtering results, joining tables, and validating data integrity.

For example, a query using WHERE status <> 'completed' returns all records where the status field is not set to "completed". This allows users to exclude specific states from output.

How It Works

When SQL evaluates a condition with <> or !=, it compares the left and right operands. If they are not identical, the expression returns TRUE. If they are the same, it returns FALSE. Rows where the condition is TRUE are included in the result set.

Null values behave differently. Comparisons involving NULL always return UNKNOWN, even with not equal operators. For instance, NULL <> 'value' does not return TRUE-it returns UNKNOWN. This means such rows are excluded unless explicitly handled with IS NOT NULL.

Consider a table called orders with columns order_id, status, and total. A query like:

SELECT * FROM orders WHERE total <> 0;

returns all orders where the total amount is not zero. This excludes free or canceled orders, helping identify active transactions.

Operators and Database Variations

Two symbols represent "not equal" in SQL: <> and !=. While both serve the same purpose, support varies by database engine.

Database <> !=
PostgreSQL Yes Yes
MySQL Yes Yes
SQLite Yes Yes
Oracle Yes No
SQL Server Yes Yes

Oracle does not recognize != as valid syntax. Using != in Oracle raises a parsing error. Always use <> in Oracle environments. PostgreSQL and MySQL accept both, but <> is standard and preferred for portability.

A common mistake is assuming != is universally supported. When writing cross-database scripts, stick to <> to avoid runtime errors. The SQL standard specifies <> as the correct symbol for inequality.

Common Uses and Real-World Examples

Not equal is frequently used in filtering, data validation, and reporting. It helps isolate anomalies, track changes, and compare datasets.

For example, a retail company tracks inventory levels. A query like:

SELECT product_name FROM inventory WHERE quantity <> 0;

lists all products currently in stock. This filters out items marked as out of stock, which may be stored as 0.

Another use is identifying mismatched data. Suppose a customer table has a country_code column. To find entries where the country is not the United States:

SELECT name FROM customers WHERE country_code <> 'US';

returns all customers outside the U.S., useful for regional marketing campaigns.

When comparing dates, not equal can detect recent activity. A query like:

SELECT user_id FROM login_logs WHERE last_login <> '2024-01-01';

finds users who did not log in on January 1, 2024. This helps identify inactive accounts.

What It's Commonly Confused With

Developers often mix up "not equal" with "is null" or "does not exist". These are distinct operations. column <> value checks for inequality between two values. column IS NOT NULL checks for absence of null.

For example, status <> 'active' returns rows where status is anything other than "active"-including "inactive", "pending", or even NULL. But status IS NOT NULL only excludes NULL values, allowing "active" and "inactive" to remain.

Another confusion arises with string comparisons. SQL treats strings case-insensitively in some contexts. In MySQL, 'ABC' <> 'abc' may return FALSE depending on collation settings. Always verify case sensitivity behavior in your environment.

One non-obvious fact: Some databases treat empty strings as equivalent to NULL in comparisons. In PostgreSQL, '' <> NULL returns TRUE. But in SQL Server, '' <> NULL returns UNKNOWN because empty strings are not treated as NULL. This affects filtering logic and requires careful testing.

Firsthand Experience

I once debugged a report that missed 12% of users due to incorrect handling of nulls. The original query used WHERE role != 'admin' to filter non-admins. But when role was NULL, the condition returned UNKNOWN, so those rows were silently excluded. Adding AND role IS NOT NULL fixed the issue. This taught me that not equal doesn't handle nulls automatically.

Related Concepts

For example:

SELECT 
  name,
  CASE 
    WHEN salary <> 50000 THEN 'Adjusted'
    ELSE 'Standard'
  END AS pay_status
FROM employees;

This assigns "Adjusted" to employees whose salary differs from $50,000.

When working with fractions or ratios, you might need to compare decimal values. For instance, checking if a ratio is not equal to 1/2:

SELECT * FROM ratios WHERE ratio_value <> 0.5;

Always test edge cases: empty strings, nulls, floating-point precision, and case sensitivity. These affect outcomes more than most developers expect.


Related Posts