SQL Not Equal: Syntax, Usage, and Examples
The SQL not equal operator helps you filter out rows where a column value does not match a specific value. This comparison operator plays a key role in writing flexible and precise queries.
You’ll typically use it when you want to exclude certain results from a dataset, such as filtering out inactive users or specific categories.
How to Use the SQL Not Equal Operator
SQL provides two syntaxes for not equal:
sql
<> -- Standard SQL syntax
!= -- Alternative (MySQL, PostgreSQL, SQL Server support this)
Both options compare values and return true if they are not equal. Use whichever your SQL engine accepts—though <> works across more systems.
Here’s a basic usage example:
sql
SELECT * FROM products
WHERE category <> 'Electronics';
This query returns all products not in the “Electronics” category.
When to Use Not Equal in SQL
Exclude Specific Values
Use SQL not equal to filter out records that match a certain condition:
sql
SELECT * FROM employees
WHERE department != 'Sales';
You’ll get all employees not working in the Sales department.
Compare Columns with Each Other
The not equal operator isn’t just for comparing a column to a fixed value. You can use it between two columns as well:
sql
SELECT * FROM orders
WHERE billing_address <> shipping_address;
This query returns orders where the billing and shipping addresses are different.
Combine With Other Operators
Use SQL not equal with AND, OR, or IN for more complex filters:
sql
SELECT * FROM users
WHERE status != 'inactive' AND role = 'admin';
This returns only active admins.
Examples of SQL Not Equal in Practice
Example 1: SQL Query Not Equal to a Specific Number
sql
SELECT * FROM orders
WHERE quantity <> 1;
If you’re reviewing multi-item orders, this query skips those with only a single item.
Example 2: Using NOT EQUAL With Text
sql
SELECT name FROM employees
WHERE title != 'Manager';
This pulls all employees whose title is not “Manager.”
Example 3: Filter Rows That Don’t Match Multiple Conditions
sql
SELECT * FROM events
WHERE event_type != 'webinar'
AND event_type != 'meeting';
This excludes both webinars and meetings, showing only other event types.
You could also write this using NOT IN:
sql
SELECT * FROM events
WHERE event_type NOT IN ('webinar', 'meeting');
But the original example with multiple not equal SQL conditions works just as well and reads clearly.
Learn More About Not Equal in SQL
Does Not Equal SQL vs. NOT LIKE
People sometimes confuse not equal with NOT LIKE. While both exclude data, they work differently.
!=or<>excludes exact values.NOT LIKEexcludes values that match a pattern.
For example:
sql
-- Not equal to exact value
SELECT * FROM customers
WHERE country <> 'Canada';
-- Not like pattern (e.g., starts with 'C')
SELECT * FROM customers
WHERE country NOT LIKE 'C%';
Use SQL not equal for precise matches, and NOT LIKE for patterns.
NULL Behavior With Not Equal
Be careful when comparing to NULL. In SQL, any comparison with NULL returns unknown, not true or false. So this will not work:
sql
SELECT * FROM users
WHERE last_login <> NULL; -- This won’t return anything
Instead, use IS NOT NULL:
sql
SELECT * FROM users
WHERE last_login IS NOT NULL;
If you want to include both non-null and not-equal logic, combine them:
sql
SELECT * FROM users
WHERE last_login IS NOT NULL AND last_login <> '2024-01-01';
SQL Not Equal in JOIN Conditions
You can use the NOT EQUAL SQL operator in JOIN conditions for anti-matching records. Though rare, it can be useful.
Example:
sql
SELECT a.id, b.id
FROM table_a a
JOIN table_b b ON a.category <> b.category;
This returns all pairs where the category is different. It can be resource-intensive, so use with care on large tables.
Filtering Negative Cases in Reports
When building dashboards or audit reports, not equal helps you highlight discrepancies:
sql
SELECT *
FROM transactions
WHERE approved_by <> processed_by;
This identifies transactions where the approver and processor are different people.
Or this:
sql
SELECT *
FROM users
WHERE role != 'admin';
This is useful when you want to apply settings or permissions to non-admin users.
Not Equal SQL in Subqueries
You can use not equal inside subqueries to exclude specific records from your main query:
sql
SELECT *
FROM users
WHERE user_id NOT IN (
SELECT user_id
FROM suspended_accounts
);
Or use <> inside correlated subqueries:
sql
SELECT *
FROM users u
WHERE EXISTS (
SELECT 1
FROM login_history l
WHERE l.user_id = u.user_id
AND l.status <> 'success'
);
This example returns users who’ve had at least one failed login.
Best Practices for Using SQL Not Equal
- Use
<>instead of!=for maximum compatibility. Some older databases don’t support!=. - Avoid comparing with NULL using
<>or!=. UseIS NOT NULLinstead. - Use indexes wisely. Not equal conditions often cause full table scans.
- Be explicit in your logic. Don’t assume that
<> 'admin'means non-users. It means everything except'admin'.
The SQL not equal operator gives you direct control over what to leave out in your result sets. Whether you’re excluding a role, filtering records that don’t match a condition, or comparing across columns, you’ll use <> or != often.