Last year I watched a “simple” change request turn into a production incident: someone replaced an equality check with a not-equal check to exclude a department, and suddenly a nightly report dropped hundreds of rows. Nothing crashed. The query still returned data. It was just quietly wrong.
That experience is why I treat the PL/SQL/SQL NOT EQUAL operator as more than basic syntax. Yes, it’s just != or . But the moment NULLs, joins, implicit conversions, or “unknown” logic enter the room, “not equal” stops behaving like the mental model most of us carry around.
I’m going to show you how !=/ behaves in Oracle SQL and in PL/SQL, where it shines, where it bites, and what I actually ship in production when I need “different” semantics. You’ll see runnable examples (tables, inserts, queries, PL/SQL blocks), common mistakes I see in code reviews, and a few patterns that make not-equal checks safer and easier to read.
Two Spellings, One Operator: !=
Oracle accepts two not-equal spellings:
is the SQL-standard operator.!=is widely supported in Oracle and many other systems.
In Oracle SQL and PL/SQL, they mean the same thing for normal comparisons.
I generally recommend:
- Use
in shared SQL codebases (portable habits, less debate). - Use
!=only if your team already standardizes on it.
What I don’t recommend is mixing them within the same codebase. Consistency matters more than picking the “better” glyph.
Basic shape
— Both of these are equivalent in Oracle
SELECT CASE WHEN 10 20 THEN ‘different‘ ELSE ‘same‘ END AS result FROM dual;
SELECT CASE WHEN 10 != 20 THEN ‘different‘ ELSE ‘same‘ END AS result FROM dual;
That looks boring (and it is) until you bring NULL into play.
The Part Everyone Trips On: NOT EQUAL and NULL (Three-Valued Logic)
Oracle SQL does not operate in a pure true/false world. It operates in true/false/NULL (often described as “unknown”).
Here’s the key rule:
- Any comparison to NULL using
=,,!=,<,>, etc. yields NULL (unknown), not true or false.
This is not a PL/SQL quirk. It’s the underlying SQL truth model.
Quick reality check
SELECT
CASE WHEN 1 1 THEN ‘T‘ WHEN 1 1 IS NULL THEN ‘NULL‘ ELSE ‘F‘ END AS onevsone,
CASE WHEN 1 2 THEN ‘T‘ WHEN 1 2 IS NULL THEN ‘NULL‘ ELSE ‘F‘ END AS onevstwo,
CASE WHEN 1 NULL THEN ‘T‘ WHEN 1 NULL IS NULL THEN ‘NULL‘ ELSE ‘F‘ END AS onevsnull,
CASE WHEN NULL NULL THEN ‘T‘ WHEN NULL NULL IS NULL THEN ‘NULL‘ ELSE ‘F‘ END AS nullvsnull
FROM dual;
Expected outcomes:
1 1is false.1 2is true.1 NULLis NULL (unknown).NULL NULLis NULL (unknown).
Why this matters in a WHERE clause
A WHERE clause filters rows by keeping those where the predicate evaluates to TRUE.
- TRUE: row kept
- FALSE: row removed
- NULL (unknown): row removed
So if you write:
SELECT *
FROM employees
WHERE dept_id 101;
you are not saying “department is not 101.”
You are saying:
- “department is not 101” AND
- “department is not NULL”
because deptid 101 becomes NULL when deptid is NULL, and NULL doesn’t pass the filter.
In my experience, the fastest way to ship a subtle bug is to forget this behavior and assume automatically includes NULLs as “not equal.” It doesn’t.
Filtering Rows with NOT EQUAL in WHERE (and Why “Not 101” Is More Than It Looks)
Let’s build a small dataset you can run in SQL*Plus, SQLcl, or any Oracle client.
Setup: employees + departments
BEGIN
EXECUTE IMMEDIATE ‘DROP TABLE employees PURGE‘;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE ‘DROP TABLE departments PURGE‘;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50) NOT NULL,
dept_id NUMBER
);
CREATE TABLE departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(50) NOT NULL
);
INSERT INTO employees (empid, empname, dept_id) VALUES (1, ‘John Doe‘, 101);
INSERT INTO employees (empid, empname, dept_id) VALUES (2, ‘Jane Smith‘, 102);
INSERT INTO employees (empid, empname, dept_id) VALUES (3, ‘Emily Davis‘, 103);
INSERT INTO employees (empid, empname, dept_id) VALUES (4, ‘Ravi Patel‘, NULL);
INSERT INTO departments (deptid, deptname) VALUES (101, ‘HR‘);
INSERT INTO departments (deptid, deptname) VALUES (102, ‘Finance‘);
INSERT INTO departments (deptid, deptname) VALUES (104, ‘IT‘);
COMMIT;
Notice what I did on purpose:
- Employee 3 references department 103, which doesn’t exist in
departments. - Employee 4 has a NULL
dept_id.
Those two conditions create the exact edge cases that make not-equal checks tricky.
Basic NOT EQUAL filter
SELECT empid, empname, dept_id
FROM employees
WHERE dept_id 101
ORDER BY emp_id;
What you’ll get:
- Jane Smith (102)
- Emily Davis (103)
What you will not get:
- John Doe (101) because it is equal
- Ravi Patel (NULL) because
NULL 101is unknown
If you actually want “everyone except dept 101, including unassigned,” you must say so:
SELECT empid, empname, dept_id
FROM employees
WHERE deptid 101 OR deptid IS NULL
ORDER BY emp_id;
I prefer to make this explicit rather than hiding the intent in a function call. When someone reads it six months later, the meaning is obvious.
NOT EQUAL with strings and date types
Two common surprises I see:
1) Trailing blanks (mostly a CHAR issue)
2) Implicit conversions (a “works until it doesn’t” issue)
Example of an implicit conversion risk:
— If dept_id is numeric, don‘t compare to a quoted literal.
— This forces implicit conversion and can change index usage and error behavior.
SELECT emp_id
FROM employees
WHERE dept_id ‘101‘;
You should compare numbers to numbers:
SELECT emp_id
FROM employees
WHERE dept_id 101;
When I review SQL, I flag quoted numerics and “string dates” immediately. They are a reliability tax.
Joins and Anti-Joins: Getting “Employees Not In Department X” Without Surprises
NOT EQUAL inside joins is where people accidentally create row explosions or lose rows.
Don’t use
I still see code like this:
— This is a classic mistake.
— It creates many matches because every employee joins to every department
— except the one with the same dept_id.
SELECT e.empname, d.deptname
FROM employees e
JOIN departments d
ON e.deptid d.deptid;
If you have 4 employees and 3 departments, that can return up to 12 rows minus a few “accidental equals.” It’s not a filter; it’s a relationship definition, and it’s almost never the relationship you want.
The normal approach: join on equality, then filter
If you want employees whose department is not HR (101), join on equality and filter on the department you don’t want:
SELECT e.empname, d.deptname
FROM employees e
JOIN departments d
ON d.deptid = e.deptid
WHERE d.dept_id 101
ORDER BY e.emp_id;
That will return employees in known departments other than 101 (in our sample, Jane Smith in Finance). It will not return:
- employees with missing departments (dept_id 103)
- employees with NULL dept_id
If you want those too, you probably want an outer join.
Outer join pattern: “not HR, including unassigned or unknown departments”
SELECT e.emp_name,
e.dept_id,
d.dept_name
FROM employees e
LEFT JOIN departments d
ON d.deptid = e.deptid
WHERE e.deptid 101 OR e.deptid IS NULL
ORDER BY e.emp_id;
This keeps:
- dept_id 102 and 103
- dept_id NULL
and excludes 101.
Anti-join patterns (safer than NOT IN when NULL is around)
A related need is: “employees whose department does not exist in departments.”
Many people reach for NOT IN, and then they get burned by NULL semantics.
If the subquery can return NULL, NOT IN can filter everything out.
I strongly prefer NOT EXISTS for anti-joins:
SELECT e.empid, e.empname, e.dept_id
FROM employees e
WHERE e.dept_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM departments d
WHERE d.deptid = e.deptid
)
ORDER BY e.emp_id;
That returns Emily Davis (103) and nothing else.
If you also want the NULL department as “does not exist,” say it:
SELECT e.empid, e.empname, e.dept_id
FROM employees e
WHERE e.dept_id IS NULL
OR NOT EXISTS (
SELECT 1
FROM departments d
WHERE d.deptid = e.deptid
)
ORDER BY e.emp_id;
PL/SQL Control Flow: IF, CASE, and Guard Clauses That Read Well
In PL/SQL, the operator works the same way, but the consequences show up as control-flow decisions rather than missing rows.
A runnable PL/SQL block: routing by department
SET SERVEROUTPUT ON;
DECLARE
lempid employees.emp_id%TYPE := 2;
ldeptid employees.dept_id%TYPE;
BEGIN
SELECT dept_id
INTO ldeptid
FROM employees
WHERE empid = lemp_id;
— Guard clause: handle NULL explicitly first.
IF ldeptid IS NULL THEN
DBMSOUTPUT.PUTLINE(‘Employee ‘ |
‘ has no department assigned.‘);
RETURN;
END IF;
IF ldeptid 101 THEN
DBMSOUTPUT.PUTLINE(‘Employee ‘ |
ldept_id);
ELSE
DBMSOUTPUT.PUTLINE(‘Employee ‘ |
‘ is in HR.‘);
END IF;
END;
/
Why I like this style:
- The NULL case is handled early and loudly.
- The
check reads as plain English. - You avoid the mental overhead of remembering how
behaves with NULL.
CASE expressions with NOT EQUAL
CASE is great when you want a value, not a branch:
SELECT e.emp_name,
CASE
WHEN e.dept_id IS NULL THEN ‘UNASSIGNED‘
WHEN e.deptid 101 THEN ‘NONHR‘
ELSE ‘HR‘
END AS hr_bucket
FROM employees e
ORDER BY e.emp_id;
This is the same rule: handle NULL first, then compare.
Looping with a NOT EQUAL stop condition
I’ll show a tiny pattern I use in data repair scripts: loop until a value changes.
SET SERVEROUTPUT ON;
DECLARE
lprevdept employees.dept_id%TYPE;
lcurrdept employees.dept_id%TYPE;
lempid employees.emp_id%TYPE := 1;
ltries PLSINTEGER := 0;
BEGIN
SELECT deptid INTO lprevdept FROM employees WHERE empid = lempid;
LOOP
ltries := ltries + 1;
— Imagine an external process updates dept_id; here we just re-read.
SELECT deptid INTO lcurrdept FROM employees WHERE empid = lempid;
EXIT WHEN lcurrdept lprevdept
OR (lcurrdept IS NULL AND lprevdept IS NOT NULL)
OR (lcurrdept IS NOT NULL AND lprevdept IS NULL);
EXIT WHEN l_tries >= 3; — safety stop
END LOOP;
DBMSOUTPUT.PUTLINE(
‘Stopped after ‘ |
NVL(TOCHAR(lprevdept),‘NULL‘)
|
NVL(TOCHAR(lcurr_dept),‘NULL‘)
);
END;
/
That EXIT WHEN looks noisy because I’m implementing a “distinct” comparison that treats NULL as a real value. If your Oracle version supports a null-safe distinct operator, use it. If not, you need to spell it out.
Safer Comparisons: Null-Safe Patterns, Data Type Traps, and Collation Gotchas
When someone tells me “I need to check that A is different from B,” my next question is always: “What should happen when one side is NULL?”
Here are patterns I actually use.
Pattern 1: Explicit NULL handling (most readable)
If you mean “different, treating NULL as unknown and excluding it,” the plain is fine.
If you mean “different, and NULL counts as different,” write it:
— Null-safe not-equal logic
WHERE (a b)
OR (a IS NULL AND b IS NOT NULL)
OR (a IS NOT NULL AND b IS NULL)
It’s verbose, but it is unambiguous.
Pattern 2: Sentinel values (fast to write, risky if you pick a bad sentinel)
Sometimes you want a shorter predicate and you control the domain well.
Example with numbers:
— Treat NULL deptid as -1, assuming -1 can never be a real deptid
WHERE NVL(dept_id, -1) 101
This includes NULLs. But you must be disciplined:
- Pick a sentinel that cannot appear.
- Apply the same rule everywhere, or you’ll create inconsistent logic.
For strings:
WHERE NVL(status, ‘<>‘) ‘ACTIVE‘
I’m careful with string sentinels because they can collide during integrations.
Pattern 3: Use a null-safe distinct operator if available
Some Oracle versions support IS DISTINCT FROM / IS NOT DISTINCT FROM semantics, which treat NULL as a comparable value.
When available, it’s the cleanest expression of intent:
— Null-safe: true when different, including NULL vs non-NULL
WHERE a IS DISTINCT FROM b
If your environment doesn’t support it, fall back to Pattern 1.
Data type traps that make NOT EQUAL look broken
These are the issues I see most often:
1) Implicit conversion
— Danger: comparing DATE to string depends on session NLS settings
WHERE hire_date ‘2026-02-03‘
Do this instead:
WHERE hire_date DATE ‘2026-02-03‘
2) Time components
If hire_date is a DATE, it includes a time component (down to seconds). Comparing to midnight can surprise you.
If you mean “not on that calendar day,” compare ranges:
WHERE hire_date < DATE '2026-02-03'
OR hire_date >= DATE ‘2026-02-03‘ + 1
3) Collation and case
If your database uses case-insensitive collation, string inequality may behave differently than you expect, especially across environments.
If you require case-sensitive logic, be explicit and consistent (for example, normalize with UPPER()/LOWER()), but understand that functions can affect index usage.
Traditional vs modern practice (what changed in the last few years)
Traditional Habit
—
Assume NULL 101 is true
Compare numbers to quoted strings
NOT IN (subquery) everywhere
NOT EXISTS to avoid NULL pitfalls Manually spot-check queries
On that last row: I’m not saying “hand your database to an assistant.” I am saying you can generate test cases (NULLs, empty strings, weird NLS cases), and then you codify them with real assertions.
NOT EQUAL vs NOT ( = ): Why These Are Not the Same in SQL
I’m calling this out because it’s a mental trap: in two-valued logic, “A is not equal to B” is identical to “NOT (A equals B).” In SQL’s three-valued logic, they’re not identical when NULL is possible.
Let’s compare these two predicates:
a bNOT (a = b)
If either side can be NULL, both predicates can evaluate to NULL (unknown), and both will be filtered out in a WHERE clause. In practice, they often behave similarly, which is why this trap survives.
But the bigger point is this: writing NOT (a = b) does not magically make comparisons “more correct.” It just makes them harder to read.
If you mean “different, treating NULL as unknown,” use .
If you mean “different, and NULL counts as different,” use IS DISTINCT FROM (if available) or spell out the null-safe logic explicitly.
Here’s a concrete demonstration using columns, because that’s where the confusion usually shows up:
— Suppose a and b are nullable columns.
— If a is NULL and b is 5:
— a = b is NULL
— NOT (a = b) is also NULL
— a b is also NULL
So the “NOT around equals” rewrite is not a fix for NULL semantics.
NOT EQUAL in DML: UPDATE, DELETE, and MERGE Gotchas
When a not-equal predicate is in a report, you might notice missing rows. When it’s in a data change statement, it can silently do the wrong write, which is worse.
UPDATE with
A common pattern is “only update when value changed”:
UPDATE employees
SET dept_id = 101
WHERE emp_id = 4
AND dept_id 101;
If deptid is NULL for empid = 4, then dept_id 101 is NULL, which is treated as false in the WHERE clause. Result: the update does not happen.
If your business rule is “set it to 101 unless it’s already 101,” you probably want NULLs included:
UPDATE employees
SET dept_id = 101
WHERE emp_id = 4
AND (deptid 101 OR deptid IS NULL);
Or, if available:
UPDATE employees
SET dept_id = 101
WHERE emp_id = 4
AND dept_id IS DISTINCT FROM 101;
This is one of the most practical places for null-safe distinct semantics.
DELETE with
This is a classic footgun:
DELETE FROM employees
WHERE dept_id 101;
It will not delete rows with NULL dept_id. That may be good or bad depending on what you meant.
When I’m deleting “everything except,” I force myself to decide explicitly:
- “Except X, keep NULLs”:
DELETE FROM employees
WHERE deptid 101 OR deptid IS NULL;
- “Except X, ignore NULLs”:
DELETE FROM employees
WHERE dept_id IS NOT NULL
AND dept_id 101;
MERGE: comparing source vs target safely
In MERGE, you often want to update only if source and target values differ. If you write:
— Sketch only: focus is the predicate
WHEN MATCHED THEN
UPDATE SET t.deptid = s.deptid
WHERE t.deptid s.deptid;
then rows where one side is NULL will not update. Sometimes that’s correct. Often it isn’t.
My rule: if the column is nullable, I treat “changed” as “distinct,” not “not equal.” That means IS DISTINCT FROM when I can, or the explicit null-safe pattern when I can’t.
NOT EQUAL and Empty Strings in Oracle: The Special Case You Must Know
Oracle treats the empty string ‘‘ as NULL for VARCHAR2 (and many related contexts). This is another reason NULL semantics and not-equal semantics are inseparable in Oracle.
So if you think you’re comparing “empty vs not empty,” you’re actually comparing “NULL vs not NULL.”
That affects things like:
col ‘‘is effectivelycol NULL, which is NULL/unknown and filters out everything.
If you want “non-empty string,” the clearer intent is:
WHERE col IS NOT NULL
And if you want “empty or null,” in Oracle those collapse to the same thing for VARCHAR2:
WHERE col IS NULL
This is one of those Oracle-specific behaviors that makes portability discussions feel academic. In the real world, you need to know what your database does.
Practical NOT EQUAL Recipes I Actually Use
This section is deliberately copy/paste-friendly. These patterns show up constantly.
Recipe 1: “Exclude one value, but keep NULLs”
WHERE col 😡 OR col IS NULL
When the value is numeric:
WHERE deptid 101 OR deptid IS NULL
Recipe 2: “Exclude one value, and also exclude NULLs”
WHERE col IS NOT NULL AND col 😡
That reads cleanly and avoids relying on three-valued logic to do “the right thing by accident.”
Recipe 3: “Different columns, NULL counts as different”
Use IS DISTINCT FROM if available:
WHERE a IS DISTINCT FROM b
Fallback:
WHERE (a b)
OR (a IS NULL AND b IS NOT NULL)
OR (a IS NOT NULL AND b IS NULL)
Recipe 4: “Not in a list, with NULL rules spelled out”
If you mean “exclude these values, keep NULLs”:
WHERE col NOT IN (101, 102) OR col IS NULL
If you mean “exclude these values, also exclude NULLs”:
WHERE col IS NOT NULL AND col NOT IN (101, 102)
Note: NOT IN has its own NULL trap when the list/subquery contains NULL. For literal lists like (101, 102) you’re safe. For subqueries, I default to NOT EXISTS.
Recipe 5: “Not in subquery (anti-join) safely”
WHERE NOT EXISTS (
SELECT 1
FROM some_table t
WHERE t.key = s.key
)
If you want NULL keys treated as “no match,” decide explicitly:
- Treat NULL as “no match” and include it:
WHERE s.key IS NULL
OR NOT EXISTS (
SELECT 1
FROM some_table t
WHERE t.key = s.key
)
- Treat NULL as “not eligible,” exclude it:
WHERE s.key IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM some_table t
WHERE t.key = s.key
)
Performance and Maintainability: When
NOT EQUAL predicates can be perfectly fine, but they can also produce plans you didn’t expect.
Here’s the mental model I use:
dept_id = 101is usually highly selective and index-friendly.dept_id 101can be low selectivity (it might match most rows), so the database may prefer a full scan.
That’s not “bad.” If most rows match, a full scan can be exactly the right plan. But it’s different than what people expect when they think “I have an index, so it’ll be fast.” Indexes help most when you’re selecting a small portion of the table.
Why
When you filter with dept_id 101, you are asking for “almost everything except one slice.” That can be the majority of rows.
So I think in terms of shapes:
- Equality: narrow slice (good candidate for index range/unique lookups)
- Not-equal: broad slice (often not a good candidate for index access)
Rewriting for performance (only when the semantics match)
Sometimes you can rewrite a not-equal filter into something more index-friendly, but only if your data has a natural ordering and only if you’re careful with NULL.
For numeric values, you can rewrite col 101 into two ranges:
WHERE col 101
This can allow range scans, but it can also be worse (two index probes plus table access) and it still excludes NULL unless you add OR col IS NULL.
If you need NULLs included:
WHERE col IS NULL OR col 101
I don’t do this rewrite automatically. I do it when:
- I’ve measured that the
predicate is a hot path. - The column has an index and the distribution makes the rewrite worthwhile.
- The team is comfortable with the readability trade-off.
Function calls can break index usage
If you use sentinels like NVL(col, -1) 101, you’ve wrapped the column in a function. Depending on indexes and optimizer behavior, that may prevent efficient index usage unless you have a function-based index.
So I choose between:
- Clarity first:
col 101 OR col IS NULL - Performance tuning: sentinel + function-based index (only when justified)
Maintainability: make intent obvious
Not-equal logic is easy to misunderstand. I optimize for “the next person can’t misread it.”
That’s why you’ll see me write:
col IS NOT NULL AND col :xinstead of justcol :xwhen NULLs should not match.col :x OR col IS NULLwhen NULLs should match.
Those extra words are a cheap insurance policy.
Common Pitfalls I Flag in Code Reviews
These are the patterns that correlate strongly with defects.
Pitfall 1:
If I see ON a.id b.id, I immediately assume it’s wrong until proven otherwise.
Almost every time, the author meant:
- join on equality, then filter
- or use
NOT EXISTSfor an anti-join
Pitfall 2:
If the column can be NULL, a b does not mean “changed.” It means “changed AND neither side is NULL.”
If you want “changed,” you want “distinct.”
Pitfall 3: implicit conversions
- Comparing numbers to
‘101‘ - Comparing dates to
‘2026-02-03‘ - Comparing timestamps to strings
This causes session-dependent behavior and can produce surprising errors in production.
Pitfall 4: misunderstanding NOT IN with subqueries
If the subquery can return NULL, NOT IN (subquery) can return no rows at all.
When I see NOT IN (SELECT ...), I ask:
- Can that SELECT return NULL?
- If yes, should we change to
NOT EXISTS?
Pitfall 5: assuming empty string is different from NULL
In Oracle, empty string often behaves like NULL for VARCHAR2. That changes how not-equal comparisons behave.
A Simple Testing Matrix for NOT EQUAL Logic (SQL and PL/SQL)
When I’m writing or reviewing not-equal logic, I like to force a tiny truth table. You don’t need a full testing framework to benefit from this; you just need discipline.
Here’s the minimum set of cases that catch most mistakes:
a
What many people expect
a b) —:
—
5
false
5
true
NULL
true
5
true
NULL
false or true (varies)
Then I choose semantics:
- If I want NULL treated as “unknown”:
a bis fine. - If I want NULL treated as a comparable value (distinctness): use
IS DISTINCT FROMor the explicit null-safe predicate.
In PL/SQL, I apply the same thinking, but I make it visible with guard clauses. In SQL, I make it visible in the predicate.
Quick “What I Ship” Checklist
When I’m about to merge code that uses not-equal logic, I scan for:
- Nullable columns: do we want NULL included or excluded?
- Implicit conversions: are literals typed correctly (
DATE ‘YYYY-MM-DD‘, numeric literals unquoted)? - Joins: is
used in anONclause (usually wrong)? - Anti-joins: can
NOT EXISTSexpress the intent better? - Readability: would a future reader understand NULL semantics without re-deriving it?
Closing Thoughts
The NOT EQUAL operator in PL/SQL and Oracle SQL is simple on paper: or !=. The hard part is not the glyph; it’s the truth model.
Once you internalize three-valued logic, a lot of “mystery bugs” stop being mysterious. You start writing predicates that say what you mean, especially around NULL. And you stop trusting “it looks right” as a substitute for a two-minute edge-case check.
If you remember one thing, make it this: whenever you write , ask yourself out loud—what should happen when the value is NULL? Then encode that answer directly in the SQL or PL/SQL.
