Introduction

SQL provides two operators for "not equal" comparisons: <> (the SQL standard) and != (widely supported but not in the original standard). Both work identically for string comparisons and exclude rows where the column matches the specified value. However, neither operator matches NULL values — NULL <> 'value' evaluates to NULL, not TRUE. This article covers string inequality patterns, NULL handling, and related filtering techniques.

Basic String Not Equal

1-- Standard SQL operator
2SELECT * FROM employees
3WHERE department <> 'Sales';
4
5-- Alternative operator (supported by MySQL, PostgreSQL, SQL Server, Oracle)
6SELECT * FROM employees
7WHERE department != 'Sales';

Both queries return all employees whose department is not 'Sales'. Rows where department is NULL are excluded from the results because NULL <> 'Sales' evaluates to NULL (unknown), not TRUE.

Handling NULL Values

1-- This MISSES rows where department is NULL
2SELECT * FROM employees
3WHERE department <> 'Sales';
4
5-- Include NULL values explicitly
6SELECT * FROM employees
7WHERE department <> 'Sales' OR department IS NULL;
8
9-- Using COALESCE to provide a default
10SELECT * FROM employees
11WHERE COALESCE(department, '') <> 'Sales';
12
13-- Using IS DISTINCT FROM (PostgreSQL, MySQL 8.0+)
14SELECT * FROM employees
15WHERE department IS DISTINCT FROM 'Sales';

IS DISTINCT FROM treats NULL as a comparable value — NULL IS DISTINCT FROM 'Sales' returns TRUE.

Case Sensitivity

String comparison behavior depends on the database and collation:

1-- MySQL with default utf8_general_ci collation (case-insensitive)
2SELECT * FROM users WHERE name <> 'alice';
3-- Excludes 'Alice', 'ALICE', 'alice', etc.
4
5-- PostgreSQL (case-sensitive by default)
6SELECT * FROM users WHERE name <> 'alice';
7-- Only excludes exact 'alice', keeps 'Alice' and 'ALICE'
8
9-- Force case-insensitive comparison
10SELECT * FROM users WHERE LOWER(name) <> LOWER('Alice');
11-- Or use ILIKE in PostgreSQL
12SELECT * FROM users WHERE name NOT ILIKE 'alice';

NOT Equal with Multiple Values

1-- Exclude multiple values with NOT IN
2SELECT * FROM products
3WHERE category NOT IN ('Electronics', 'Clothing', 'Toys');
4
5-- Equivalent using AND
6SELECT * FROM products
7WHERE category <> 'Electronics'
8  AND category <> 'Clothing'
9  AND category <> 'Toys';

NOT IN is cleaner for excluding multiple specific values. Note that NOT IN with a NULL in the list returns no rows — filter NULLs from the subquery.

Pattern-Based Exclusion

1-- Exclude strings starting with 'test'
2SELECT * FROM users WHERE name NOT LIKE 'test%';
3
4-- Exclude strings containing 'admin'
5SELECT * FROM users WHERE name NOT LIKE '%admin%';
6
7-- PostgreSQL: case-insensitive pattern exclusion
8SELECT * FROM users WHERE name NOT ILIKE '%admin%';
9
10-- Exclude using regular expressions (PostgreSQL)
11SELECT * FROM users WHERE name !~ '^test_\d+$';
12
13-- MySQL regex exclusion
14SELECT * FROM users WHERE name NOT REGEXP '^test_[0-9]+$';

NOT Equal in JOINs

1-- Self-join to find pairs of employees in different departments
2SELECT a.name AS employee1, b.name AS employee2
3FROM employees a
4JOIN employees b ON a.id < b.id
5WHERE a.department <> b.department;
6
7-- Exclude specific join matches
8SELECT o.order_id, o.status
9FROM orders o
10LEFT JOIN cancellations c ON o.order_id = c.order_id
11WHERE c.order_id IS NULL;  -- Orders without cancellations

Database-Specific Syntax

Common Pitfalls

• Forgetting that <> does not match NULL: WHERE col <> 'value' silently excludes rows where col is NULL. If NULL rows should be included, add OR col IS NULL or use IS DISTINCT FROM (PostgreSQL/MySQL 8.0+).
• Using NOT IN with a subquery that returns NULL: WHERE col NOT IN (SELECT ...) returns no rows if the subquery produces any NULL values. Filter NULLs from the subquery: WHERE col NOT IN (SELECT val FROM t WHERE val IS NOT NULL).
• Assuming case-insensitive comparison across databases: MySQL's default collation is case-insensitive, but PostgreSQL is case-sensitive. Code that works in MySQL ('alice' <> 'Alice' is false) fails in PostgreSQL (it is true). Use LOWER() or COLLATE for portable case-insensitive comparisons.
• Using != in strict SQL standard contexts: While != works in all major databases, it is not part of the original SQL standard. Use <> in code that must be strictly standards-compliant or portable across uncommon databases.
• Comparing with empty string vs NULL: In Oracle, empty string '' is treated as NULL. WHERE col <> '' behaves differently in Oracle than in PostgreSQL or MySQL. Be explicit about NULL handling when targeting multiple databases.

Summary

• Use <> (standard) or != (widely supported) for string not-equal comparisons
• Neither operator matches NULL — add OR col IS NULL or use IS DISTINCT FROM to include NULLs
• Use NOT IN (...) for excluding multiple specific values
• Use NOT LIKE or NOT REGEXP for pattern-based exclusion
• Case sensitivity depends on the database collation — use LOWER() for portable case-insensitive comparisons
• Always test NULL handling explicitly, especially with NOT IN subqueries