SQL Not Equal: Complete Guide with Interactive Examples

When filtering data in SQL, you’ll frequently need to exclude specific values from your results. The SQL not equal operator is your primary tool for this task. Whether you’re filtering out cancelled orders, excluding certain departments, or removing unwanted categories, understanding how to use not equal effectively is essential.

Throughout this guide, you’ll find interactive SQL exercises that let you practice not equal queries in your browser—no database setup required. Write queries, get instant feedback, and see if your results match the expected output.

The Two Not Equal Operators: <> and !=

SQL provides two operators for checking inequality:

• <> - The ANSI SQL standard operator
• != - An alternative syntax supported by most databases

Both operators do exactly the same thing. Here’s how they work:

SELECT * FROM products WHERE category <> 'Electronics';
SELECT * FROM products WHERE category != 'Electronics';

These queries produce identical results. The <> operator is part of the SQL standard and works in every database. The != operator is more familiar to programmers coming from languages like JavaScript, Python, or C, and is supported by PostgreSQL, MySQL, SQLite, SQL Server, and Oracle.

Which should you use? It’s largely a matter of preference and team conventions. Many developers prefer != for readability, while <> is the more “correct” choice from a standards perspective.

Basic Not Equal Filtering

The most common use of not equal is in a WHERE clause to exclude specific values:

SELECT name, status
FROM orders
WHERE status <> 'cancelled';

This query returns all orders except those with a ‘cancelled’ status. The not equal operator works with:

• Strings: category <> 'Electronics'
• Numbers: quantity <> 0
• Dates: order_date <> '2024-01-01'

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Not Equal with Numbers

When comparing numeric values, don’t use quotes:

SELECT employee_name, department_id
FROM employees
WHERE department_id <> 100;

Common numeric comparisons include:

• Excluding a specific ID: user_id <> 1
• Filtering out zero values: quantity <> 0
• Removing placeholder values: status_code <> -1

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Combining Not Equal with Other Conditions

You can combine not equal with AND and OR operators to create more complex filters:

SELECT product_name, category, price
FROM products
WHERE category <> 'Discontinued'
  AND price > 0
  AND stock_quantity <> 0;

This query finds active products that are in stock and have a price set. You can also exclude multiple values:

SELECT name, status
FROM orders
WHERE status <> 'cancelled'
  AND status <> 'refunded';

For excluding multiple values, consider using NOT IN for cleaner syntax:

SELECT name, status
FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Not Equal and NULL Values

Here’s a critical concept that trips up many SQL users: the not equal operator does NOT match NULL values.

Consider this table:

If you run:

SELECT * FROM employees WHERE manager_id <> 10;

You might expect to get Bob and Carol. But you’ll only get Carol! Bob’s NULL manager_id is not returned because NULL comparisons with <> always evaluate to UNKNOWN (not TRUE or FALSE).

To include NULL values in your results, you need to explicitly handle them:

SELECT * FROM employees
WHERE manager_id <> 10 OR manager_id IS NULL;

Or use COALESCE to provide a default value:

SELECT * FROM employees
WHERE COALESCE(manager_id, -1) <> 10;

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Performance Considerations

Not equal queries can have performance implications. Here’s what to keep in mind:

Index Usage

The not equal operator often doesn’t use indexes as efficiently as equals. Consider:

-- This might not use an index on status
SELECT * FROM orders WHERE status <> 'completed';

-- This might use an index better
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');

If most of your records match a condition and you’re excluding a small subset, sometimes it’s more efficient to select what you want rather than exclude what you don’t.

Cardinality Matters

When the excluded value is common, not equal queries can return large result sets:

-- If 90% of products are active, this returns lots of data
SELECT * FROM products WHERE status <> 'inactive';

-- Better to query for what you need directly
SELECT * FROM products WHERE status = 'active';

Common Patterns and Use Cases

Excluding Soft-Deleted Records

Many applications use soft deletes where records are marked as deleted rather than removed:

SELECT * FROM users
WHERE deleted_at IS NULL
   OR deleted_at <> deleted_at;  -- This is always false for non-NULL, always NULL for NULL
   
-- Simpler approach:
SELECT * FROM users WHERE is_deleted <> 1;

Filtering Out Default Values

When data contains placeholder or default values:

SELECT customer_name, phone
FROM customers
WHERE phone <> ''
  AND phone <> 'N/A'
  AND phone IS NOT NULL;

Finding Changed Records

Compare current and previous values:

SELECT *
FROM audit_log
WHERE old_value <> new_value;

Excluding the Current User

Common in social features:

SELECT username
FROM users
WHERE user_id <> @current_user_id;

NOT Operator vs Not Equal

Don’t confuse the not equal operator with the NOT logical operator:

-- Not equal: excludes one specific value
SELECT * FROM products WHERE category <> 'Books';

-- NOT: negates a condition
SELECT * FROM products WHERE NOT (category = 'Books');

-- NOT with IN: excludes multiple values
SELECT * FROM products WHERE category NOT IN ('Books', 'Music');

-- NOT with LIKE: excludes pattern matches
SELECT * FROM products WHERE name NOT LIKE '%test%';

-- NOT with BETWEEN: excludes a range
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 50;

The NOT operator is more flexible but can be less readable for simple inequality checks.

Database-Specific Notes

While <> is universally supported, here are some database-specific considerations:

PostgreSQL: Both <> and != work. Also supports IS DISTINCT FROM for NULL-safe comparisons:

SELECT * FROM users WHERE status IS DISTINCT FROM 'active';

MySQL: Both operators work. Also has the <=> NULL-safe equals operator:

SELECT * FROM users WHERE NOT (status <=> 'active');

SQL Server: Both operators work identically.

SQLite: Both operators work. SQLite is very permissive with types, so be careful with implicit conversions.

Oracle: Both operators work. Oracle treats empty strings as NULL, which can affect not equal comparisons.

Working with Not Equal in Beekeeper Studio

When building complex filter queries, a good SQL editor helps you write and test conditions quickly. Beekeeper Studio provides:

• Query autocomplete: Column names and operators suggested as you type
• Syntax highlighting: Easily spot operators and string values
• Quick data preview: See your table data to understand what to filter
• Query history: Save and reuse your exclusion queries
• Multi-database support: Same syntax works across PostgreSQL, MySQL, SQLite, and more

The free version includes everything you need for everyday database work, making it easy to build and test not equal queries.

Key Takeaways

The SQL not equal operator is simple but has important nuances:

• Use <> or != - both work the same way in most databases
• NULL values are not matched by not equal - handle them explicitly with IS NULL or IS NOT NULL
• Combine with AND/OR for complex exclusion logic
• Consider NOT IN when excluding multiple values for cleaner code
• Watch performance on large tables - not equal may not use indexes efficiently
• Remember NULL behavior - this is the most common source of bugs with not equal queries
• Use IS DISTINCT FROM (PostgreSQL) or <=> (MySQL) for NULL-safe comparisons

Understanding these details will help you write more accurate and efficient SQL queries when you need to exclude specific data from your results.

Ready to practice more? Try the interactive examples above, or explore more SQL tutorials to continue building your database skills.