SQL Not Equal: <> vs != Operators Explained (2026)

SQL not equal looks like the simplest comparison operator in the language — until a query that should exclude a handful of rows quietly excludes half the table, or a WHERE clause that looks correct at a glance returns nothing at all. Most guides stop at "here's the syntax." This one goes further: the standard vs. non-standard not equal sign in sql, the NULL behavior that trips up even experienced developers, the null-safe alternatives each database actually supports, and — because syntax alone rarely explains why a specific query broke — how to line up two versions of a query side by side and spot the exact clause that changed. If you're troubleshooting a broader query mismatch rather than a single operator, the companion guide to data comparison in SQL covers EXCEPT, INTERSECT, and row-level reconciliation, and SQL compare walks through comparing full scripts and stored procedures rather than a single predicate.

SQL defines equality and inequality comparisons as part of its core predicate syntax, and every major relational engine — MySQL, PostgreSQL, SQL Server, Oracle, SQLite, MariaDB, BigQuery, Snowflake — implements them consistently at the surface level. What differs, and what causes most of the confusion, is three-valued logic: SQL comparisons don't just return TRUE or FALSE, they can also return UNKNOWN, and NOT EQUAL is where that third value bites hardest. We'll cover exactly when and why, with runnable examples for each engine.

What Is the SQL NOT EQUAL Operator?

The sql not equal operator compares two values and returns TRUE when they differ, FALSE when they match, and — critically — UNKNOWN when either side is NULL. It's the logical inverse of the equality operator (=), and you'll find it in a WHERE clause, a JOIN ... ON condition, a CASE expression, or a CHECK constraint, anywhere SQL evaluates a boolean predicate. Once you understand equal and not equal in sql as a matched pair — one operator checks sameness, the other checks difference, and both share the exact same NULL behavior — the rules in the rest of this guide follow naturally instead of feeling like a list of exceptions to memorize.

SQL gives you two spellings that do the same job:

SELECT * FROM orders WHERE status <> 'cancelled';
SELECT * FROM orders WHERE status != 'cancelled';

Both statements return every row where status is anything other than 'cancelled' — except rows where status is NULL, which neither version returns. We'll come back to that in detail in the NULL section below, because it's the single most common reason a sql does not equal query produces the wrong row count.

<> vs !=: Which Not Equal Sign Should You Use?

This is the most-asked question about SQL inequality, and the short answer is: it rarely matters for correctness, but it's worth knowing the history.

• <> is the ANSI/ISO SQL standard not equal sign in sql. It has been part of the specification since SQL-92 and appears in every subsequent revision of ISO/IEC 9075. If you're writing SQL that must be portable across strict standard-compliant engines, <> is the operator to reach for.
• != is a widely supported but non-standard alias, borrowed from C-family language syntax. It reads naturally to developers coming from JavaScript, Python, or Java, which is likely why it has become the more common sql not equal operator in application code, even though it was never part of the SQL standard itself.

Both operators are supported — with identical behavior — in MySQL, PostgreSQL, SQL Server (T-SQL), Oracle, SQLite, MariaDB, BigQuery, and Snowflake. There's no performance difference between them on any of these engines: PostgreSQL's parser literally rewrites != to <> before the query is planned, so the two produce an identical execution plan every time. See the PostgreSQL comparison operators documentation for the canonical reference.

In practice, teams pick one and standardize on it for consistency — the same way you'd standardize on tabs vs. spaces or single vs. double quotes. If your team has a SQL style guide, follow it; if not, <> signals "I'm writing portable, standard SQL" while != signals "this reads like application code." Neither is wrong. The important part is knowing that equal and not equal in sql stay symmetric across every engine covered in this guide — whichever spelling you pick for one, the matching equality operator (=) behaves the same way everywhere.

SQL Query for NOT EQUAL: Syntax and Examples

This section is a practical reference for how to write not equal to in sql — a sql query for not equal comparison in each clause where you'll actually use it.

Basic WHERE not equal SQL

-- Return every customer that is not based in Canada
SELECT customer_id, name, country
FROM customers
WHERE country <> 'Canada';

-- Same query, != spelling
SELECT customer_id, name, country
FROM customers
WHERE country != 'Canada';

This is the canonical where not equal sql pattern: one column, one literal, one comparison. It scales cleanly to numbers, dates, and booleans too:

SELECT * FROM invoices WHERE amount_due <> 0;
SELECT * FROM tasks WHERE due_date <> '2026-08-15';
SELECT * FROM users WHERE is_active <> TRUE;

Combining NOT EQUAL with AND / OR

-- Exclude two statuses at once
SELECT * FROM orders
WHERE status <> 'cancelled'
  AND status <> 'refunded';

-- Equivalent, often more readable for 3+ values
SELECT * FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

Once you're excluding more than one or two values, NOT IN is usually cleaner than chaining AND status <> x clauses — but read the NOT IN section below first, because it has its own NULL trap that's arguably worse than the plain operator's.

NOT EQUAL in a JOIN condition

-- Find pairs of employees who don't share a manager
SELECT a.employee_id, b.employee_id
FROM employees a
JOIN employees b
  ON a.department_id = b.department_id
 AND a.manager_id <> b.manager_id;

NOT EQUAL inside CASE and CHECK

SELECT
  order_id,
  CASE WHEN status <> 'delivered' THEN 'in progress' ELSE 'complete' END AS state
FROM orders;

ALTER TABLE products
ADD CONSTRAINT chk_price_not_zero CHECK (price <> 0);

Every one of these patterns answers the question of how to write not equal to in sql for a specific clause — the operator itself never changes, only where you place it.

The NULL Trap: Why NOT EQUAL Returns Zero Rows

This is the single most reported sql not equal problem: a query that should return rows returns none, or returns fewer than expected, and there's no error to point at.

-- This looks correct...
SELECT * FROM subscriptions WHERE plan <> 'free';

-- ...but if some rows have plan = NULL, those rows are silently dropped,
-- even though NULL clearly "is not" 'free'.

SQL uses three-valued logic: every comparison evaluates to TRUE, FALSE, or UNKNOWN, and a WHERE clause only keeps rows where the predicate is TRUE. Comparing anything to NULL — including with <> or != — always evaluates to UNKNOWN, never TRUE, so the row is filtered out regardless of what you intended.

NULL <> 'free'     -- UNKNOWN, not TRUE
NULL != 'free'     -- UNKNOWN, not TRUE
NULL <> NULL       -- UNKNOWN, not TRUE (not even NULL "equals" NULL)

The fix is to test for NULL explicitly with IS NULL / IS NOT NULL, and combine it with your inequality check if you want NULL rows included in the result:

-- Include rows where plan is anything other than 'free', including unset plans
SELECT * FROM subscriptions
WHERE plan <> 'free' OR plan IS NULL;

-- Exclude rows where plan is unset, and also exclude 'free'
SELECT * FROM subscriptions
WHERE plan <> 'free' AND plan IS NOT NULL;

Microsoft's own T-SQL reference documents this behavior explicitly — see Not Equal To (Transact-SQL) — and the same three-valued logic applies identically on every other engine in this article, because it comes from the SQL standard, not from any one vendor's implementation.

Null-Safe Not Equal: IS DISTINCT FROM and Friends

Sometimes you genuinely want NULL to be a comparable value — for example, when diffing two columns and treating "both NULL" as equal and "one NULL, one not" as different. Regular <>/!= can't do this because of the UNKNOWN behavior above. Each database ships a null-safe alternative:

• PostgreSQL, SQL Server 2022+, Snowflake, BigQuery — IS DISTINCT FROM / IS NOT DISTINCT FROM. Treats NULL as a regular, comparable value: NULL IS DISTINCT FROM 'free' evaluates to TRUE, and NULL IS NOT DISTINCT FROM NULL evaluates to TRUE.
• MySQL and MariaDB — no IS DISTINCT FROM. Instead they offer the null-safe equality operator <=>, so the null-safe not-equal is its negation: NOT (a <=> b).

-- PostgreSQL / SQL Server 2022+ / Snowflake / BigQuery
SELECT * FROM subscriptions WHERE plan IS DISTINCT FROM 'free';

-- MySQL / MariaDB equivalent
SELECT * FROM subscriptions WHERE NOT (plan <=> 'free');

See MySQL's own reference for comparison operators for the full behavior of <=> alongside standard equality. Reach for these null-safe forms specifically when NULL is meaningful data in your comparison, not just an absence — row-level reconciliation between two tables is the most common case, and a dedicated database comparison tool applies the same null-safe logic across entire tables instead of one predicate at a time.

NOT IN vs NOT EXISTS: The Same NULL Trap in Subqueries

NOT IN is essentially a shorthand for multiple <> comparisons ANDed together, and it inherits the same NULL problem — except with a subquery, the failure mode is worse: a single NULL anywhere in the subquery's result set can zero out the entire outer query, not just one row.

-- If ANY customer_id in the subquery is NULL, this returns ZERO rows —
-- even for customers who clearly aren't in the excluded list.
SELECT * FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM excluded_customers
);

This happens because NOT IN (a, b, NULL) expands to <> a AND <> b AND <> NULL, and that last comparison is always UNKNOWN. ANDing anything with UNKNOWN can only produce TRUE or UNKNOWN — never FALSE — but it also can never produce a clean TRUE across the whole conjunction once one leg is UNKNOWN, so the row gets excluded from the result. The safe rewrite is NOT EXISTS, which doesn't evaluate row-by-row equality and isn't affected by NULLs in the subquery:

SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM excluded_customers e
  WHERE e.customer_id = c.customer_id
);

As a rule: default to NOT EXISTS for subquery exclusion unless you've verified the subquery's column is declared NOT NULL. It's one extra line of typing that prevents a genuinely hard-to-diagnose empty-result bug.

Debugging a Broken NOT EQUAL Query With a Diff Tool

Everything above explains the rules. In practice, the moment you actually need them is when a query that used to work stops returning the rows you expect, and you're staring at two versions of a query trying to find what changed. A few scenarios where this comes up constantly:

• An ORM regenerated a query after a library upgrade. SQLAlchemy, Prisma, or ActiveRecord silently changed how a .exclude() or .not_eq() call compiles to SQL — maybe it switched from NOT IN to <> ... AND <> ..., or added an explicit IS NOT NULL that wasn't there before. Pasting the old generated query next to the new one and diffing them line by line shows exactly what the ORM changed, instead of re-reading ORM changelogs hoping to spot it.
• A hand-written query was "fixed" by someone who didn't know about the NULL trap. A teammate adds WHERE status != 'archived' to a report query, tests it against a dataset with no NULLs, ships it, and it silently drops archived-adjacent rows once real data with NULL statuses shows up. Comparing the working version of the query against the new one makes the missing OR status IS NULL obvious in a way that re-reading the new query in isolation often doesn't.
• A migration between database engines changed operator behavior. Moving from MySQL to PostgreSQL, a query using <=> needs to become IS NOT DISTINCT FROM or an explicit CASE. Diffing the MySQL version against its Postgres port confirms every null-safe comparison was actually translated, not just the syntax that happens to run without erroring.

This is exactly the kind of side-by-side check the Diff Checker Chrome extension is built for. Paste the old query into one pane and the new (or ORM-generated, or cross-engine) version into the other, and it lays them out in a Split or Unified view with every changed token highlighted — so a missing OR col IS NULL, a swapped NOT IN for NOT EXISTS, or a <> that quietly became = stands out immediately instead of hiding in a wall of near-identical text. The toolbar gives you three comparison methods — Smart Diff, Ignore Whitespace, and Classic (LCS) — so reformatted queries from a different tool or a different indentation style don't drown the real change in noise. Show Diff Only collapses the identical lines down to a configurable number of context lines, which matters when the two queries are 40 lines of a stored procedure and only one WHERE clause differs. Alt+↓ and Alt+↑ jump you directly from one change to the next, and the Normalize button trims trailing whitespace and line-ending differences before you even start reading — comparing all of this to checking SQL syntax for outright errors, since a diff only tells you what changed, not whether either version is valid.

The comparison runs locally in your browser — your queries aren't sent to a server unless you turn on the optional AI summary, which uses your own OpenAI key. That matters here more than in most diffing contexts, because the two query versions you're comparing often contain real table names, column names, and sometimes literal values from production data. Past comparisons are also kept in a local history, so you can pull up last week's version of a query without digging through commit history or Slack threads to find it again.

Performance: Why NOT EQUAL Is Non-Sargable

Beyond correctness, an inequality filter carries a performance cost that catches teams off guard on large tables. <>, !=, and NOT IN are non-sargable — "sargable" meaning the predicate can use an index to seek directly to matching rows ("Search ARGument ABLE"). A B-tree index is built to jump to a specific value or range efficiently; "everything except this one value" doesn't describe a contiguous range the index can seek to, so most query planners fall back to a full table scan or a full index scan instead of an index seek.

-- Sargable — the planner can seek using an index on status
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Non-sargable — the planner typically scans, even with an index on status
EXPLAIN SELECT * FROM orders WHERE status <> 'pending';

On small or medium tables this rarely matters. On a table with tens of millions of rows, a non-sargable WHERE status <> 'pending' scanning the whole table can be the difference between a sub-second query and one that takes minutes. A few mitigations, roughly in order of how often they apply:

• Flip the predicate to a positive condition when the excluded set is small: if status only has five possible values, status IN ('pending', 'active', 'shipped', 'delivered') is sargable where status <> 'cancelled' is not, and returns the identical rows.
• Rewrite as a range for ordered types: amount <> 0 becomes (amount < 0 OR amount > 0), which some optimizers can still plan using two index seeks instead of one scan.
• Add a partial or filtered index (PostgreSQL CREATE INDEX ... WHERE status <> 'cancelled', SQL Server filtered indexes) when the excluded value is the overwhelming majority case and the non-excluded rows are what you query repeatedly.
• Check the actual execution plan before optimizing blind — EXPLAIN ANALYZE (PostgreSQL/MySQL) or the SQL Server execution plan viewer will confirm whether the planner is scanning or seeking, since some optimizers are smarter about small tables and low-cardinality columns than the general rule suggests.

NOT EQUAL Across Databases: MySQL, PostgreSQL, SQL Server, and More

Every major relational database supports both spellings of sql not equal, but null-safe comparison syntax diverges by engine. Use this as a quick reference:

One more per-database wrinkle worth knowing: string inequality depends on collation. Whether 'Smith' <> 'smith' evaluates TRUE or FALSE, and whether trailing spaces or accented characters count as different, is governed by the column's or database's collation setting — not by the comparison operator. Two databases with the same schema but different default collations can return different results from the exact same inequality predicate, which is worth checking first if a string comparison behaves differently after a migration between engines or environments. SQLite documents its own variant in the SQLite expression reference, where IS NOT is defined as the null-safe inequality rather than a synonym for <>.

Common Mistakes When Writing NOT EQUAL Queries

Most bug reports that start with "my sql does not equal query is wrong" trace back to one of these five patterns. Check them in order — they're listed roughly by how often they actually show up in production incidents.

1. Comparing directly against NULL

WHERE col <> NULL and WHERE col != NULL never match any row, including rows where col actually is NULL. This is the single most common inequality mistake in SQL, and it's also the most common way a sql does not equal filter silently returns the wrong count. Always use IS NULL / IS NOT NULL instead.

2. Forgetting NULLs exist in the column being filtered

Even without comparing to NULL directly, WHERE status != 'archived' excludes every row where status is NULL. If those rows should be included, add OR status IS NULL explicitly — the database won't infer that for you.

3. Using NOT IN with a subquery that can return NULL

Covered in detail above: one NULL in a NOT IN subquery's result set can zero out the entire outer query. Default to NOT EXISTS unless you've confirmed the subquery column is NOT NULL.

4. Assuming <> and != behave differently

They don't, on any of the eight databases covered here. If a query behaves differently after switching from one spelling to the other, the actual cause is elsewhere — a NULL you introduced while editing, a change in collation, or a different value in the comparison literal. Diff the two query versions rather than assuming the operator spelling is the culprit.

5. Expecting an index to speed up a broad NOT EQUAL filter

As covered in the performance section, <>/!= rarely benefit from a standard B-tree index. If a query is slow, check the execution plan before assuming adding an index will fix it — it usually won't, on its own.

Frequently Asked Questions

What is the difference between <> and != in SQL?

Functionally, nothing — both mean "not equal" and both filter out matching rows. The difference is standards heritage: <> is the ANSI/ISO SQL standard not equal sign, defined in SQL-92 and every revision since. != is a C-style alias that most databases adopted for developer familiarity but that isn't part of the SQL standard itself. MySQL, PostgreSQL, SQL Server, Oracle, SQLite, MariaDB, BigQuery, and Snowflake all accept both. PostgreSQL's parser even rewrites != to <> internally before execution, so the two produce an identical query plan.

How do I handle NULL with the NOT EQUAL operator?

You can't compare against NULL with <> or != — col <> NULL always evaluates to UNKNOWN, never TRUE, so the row gets filtered out of your result set no matter what col actually contains. Use IS NOT NULL to test for non-null values, and IS NULL to test for null ones. If you need a comparison that treats NULL as a real, matchable value against another column, use IS DISTINCT FROM (PostgreSQL, SQL Server 2022+, Snowflake, BigQuery) or NOT (a <=> b) in MySQL/MariaDB.

What is the SQL standard for not equal?

The ANSI/ISO SQL standard defines <> as the not equal sign in SQL. It has been part of the specification since SQL-92 and remains unchanged through the current ISO/IEC 9075 standard. != is a widely supported extension that every major relational database accepts, but it originates from C-family languages rather than the SQL standard itself. If you're writing SQL that needs to be portable to a strict-standard-compliance database, <> is the safer choice.

Why does my SQL not equal query return no rows?

The most common cause is a NULL in the column you're filtering. WHERE status != 'archived' silently drops every row where status is NULL, because NULL != 'archived' evaluates to UNKNOWN rather than TRUE. Add OR status IS NULL if you want those rows included. The second most common cause is a NOT IN subquery that returns even one NULL — that propagates UNKNOWN through the entire predicate and can zero out the whole result set. Rewrite with NOT EXISTS to sidestep it.

Does the NOT EQUAL operator use an index?

Usually not efficiently. <>, !=, and NOT IN are non-sargable — a B-tree index is built to seek toward equal values, and "everything except this one value" doesn't map cleanly onto that structure. Most query planners fall back to a full table or index scan. If NOT EQUAL filtering is a performance bottleneck, consider rewriting the predicate as a range (col < x OR col > x), adding a covering index the planner can still scan efficiently, or restructuring the query around a positive condition instead of a negative one.

For the row-level comparison work that usually surrounds a NOT EQUAL query — checking that two tables actually match after a migration, or that an ETL job reconciled correctly — the guide to comparing data sets picks up where this one leaves off. And if the query you're debugging is being generated by application code rather than written by hand, comparing its output against a known-good string with a string equality check is often the fastest way to confirm whether the generated SQL actually changed or the underlying data did. For quick one-off testing of a rewritten query before you commit to it, an online SQL compiler lets you run it against sample data without touching a real connection, and a full MySQL database compare tool is the right call once you're reconciling entire schemas rather than a single predicate.