Equal and Not Equal in SQL: The = , <> and != Operators Explained

Short answer

• In SQL, = tests whether two values are equal, and <> tests whether two values are not equal. In PostgreSQL, MySQL, and SQL Server, != is also accepted as a not equal operator.
• <> is the standard SQL spelling for not equal. PostgreSQL documentation states that <> is the standard notation and that != is an alias converted to <> early in parsing, so the two cannot behave differently in PostgreSQL.
• For ordinary non NULL values, = returns true when the values match and false when they do not, and <> and != return the exact opposite.
• A comparison involving NULL does not return true or false. It returns unknown, represented as NULL. A WHERE clause keeps only rows where the condition is true, so rows with an unknown result are left out.
• Never write = NULL or <> NULL to find or exclude missing values. Use IS NULL to find missing values and IS NOT NULL to find values that are present.
• NOT IN with a list that contains NULL can return no rows at all, even when matching rows exist. Use NOT EXISTS or add an explicit NULL test when the list may contain NULL.
• When you need a comparison that treats NULL as an ordinary comparable value, use IS DISTINCT FROM or IS NOT DISTINCT FROM where your database supports them. MySQL also provides the NULL safe equality operator <=>.

SHORT-ANSWER BOX

The Sample Table Used Throughout This Guide

Every worked example uses the same small orders table, so every result can be checked by hand. The table has ten orders. Two orders have no discount code, which is recorded as NULL, and one order has no ship date, also recorded as NULL. Those NULL rows are deliberate: they show how equal and not equal tests behave when a value is missing, and they make the difference between a correct NULL test and a broken one visible.

Read NULL in this table as “no value recorded”, not as zero, not as an empty string, and not as the word “null”. That distinction drives everything that follows. A discount code of NULL means the order has no recorded code. It does not mean the code is blank, and it does not mean the code equals any other missing code.

The Equal Operator: =

The = operator compares two expressions and returns true when they are equal, false when they are not equal, and unknown when either side is NULL. It is the operator most readers meet first, because it appears in WHERE filters, in JOIN conditions, and in UPDATE and DELETE statements.

SELECT order_id, customer, status
FROM orders
WHERE status = 'shipped';

This query returns orders 101, 103, 106, 107, and 110. Each of those rows has a status value that equals the text ‘shipped’. Orders with any other status are left out, because for them the comparison is false. The query does not change the table. It only selects rows.

The same operator works with numbers and dates, provided the types are comparable. A numeric comparison such as order_id = 103 returns true for exactly one row in the sample table. A date comparison such as ship_date = '2026-03-02' returns true for order 101 only. In both cases the logic is the same: compare the stored value with the supplied value, keep the row when the result is true, and drop the row otherwise.

SELECT order_id, customer
FROM orders
WHERE order_id = 103;

One common confusion deserves a direct answer. In SQL, = is a comparison operator, not an assignment operator, when it appears in a WHERE clause or a SELECT condition. Assignment in SQL uses SET in an UPDATE statement, as in SET status = 'shipped'. That SET form changes stored data. A = inside WHERE only tests data. Keeping those two roles apart prevents a reader from assuming that a filter query has changed anything, and it prevents a reader from writing a WHERE clause that was meant to update a value.

Equality also appears in JOIN conditions, where it matches rows from two tables on a shared value. The related explainer on inner joins in SQL covers join types and join logic in full, so this guide does not repeat that coverage. The point to carry forward is narrower: a JOIN condition built on = matches only rows whose join values are equal and known. When a join key is NULL on either side, the equality test is unknown, not true, so the pair does not match. That is the same NULL rule that governs WHERE filters, applied inside a join.

Equality in a SELECT list is also useful. A query can return a true or false result per row instead of filtering rows out. The following query labels each order rather than removing any row:

SELECT order_id, customer,
    status = 'shipped' AS is_shipped
FROM orders;

In PostgreSQL this returns a boolean true or false per row. In MySQL the same expression returns 1 for true and 0 for false, because MySQL represents boolean results numerically in this context. The idea is identical in both systems: the comparison is evaluated for each row, and the result is returned as a value. Dialect output formatting differs, but the true and false pattern does not.

The Not Equal Operators: <> and !=

The <> operator returns true when two values are different and false when they are equal. For ordinary non NULL values it is the exact mirror of =. If status = 'shipped' is true for a row, then status <> 'shipped' is false for that row, and the reverse holds for every other row.

SELECT order_id, customer, status
FROM orders
WHERE status <> 'shipped';

On the sample table this returns orders 102, 104, 105, 108, and 109. Those are the five orders whose status is known and is not shipped. Notice what is missing: no order is missing a status in this table, so every non shipped row appears. The missing row problem appears later, when the compared column itself contains NULL. Keep that distinction in mind, because it explains most not equal surprises.

The != operator does the same job in the dialects covered here. The following query returns the same five rows as the <> version above:

SELECT order_id, customer, status
FROM orders
WHERE status != 'shipped';

For a beginner, the practical question is which spelling to use. The answer has two parts. First, <> is the standard SQL spelling, and it is accepted by PostgreSQL, MySQL, and SQL Server. A query written with <> is therefore the most portable choice. Second, != is familiar to readers who come from programming languages such as C, Java, or Python, where != is the usual not equal symbol, and it is accepted by the same three dialects. Within one project, pick one spelling and use it consistently. Consistency matters more than the choice itself, because a mixed codebase forces every reader to check whether two symbols mean two different things. In PostgreSQL they do not: the documentation states that != is converted to <> early in parsing, so the two cannot be given different meanings there. MySQL lists <> and != together as the not equal operator, and SQL Server lists both symbols among its comparison operators.

There is one symbol that beginners sometimes try by mistake: ==. SQL does not use == for equality. In the dialects covered here, a double equals sign is not the equality test, and a query that uses it will fail or behave unexpectedly depending on the system. Write one equals sign for equality in SQL, and keep == for programming languages that use it.

A related mistake is to assume that not equal means “everything else”. In SQL it means “everything else that is known and different”. That wording matters. A row whose compared value is NULL is not known to be different, so it is not returned by a not equal test. The section on NULL below shows this with the discount code column, where the difference between “everything else” and “everything known and different” changes the row count by two.

<> Compared With !=: What Actually Differs

Across PostgreSQL, MySQL, and SQL Server, <> and != return the same result for the same inputs, including NULL inputs, where both return unknown. The verified differences are about standard status, portability, and parsing, not about the answer for ordinary values.

Two qualifications belong here, because they are easy to overstate. First, this table covers the three dialects named in the brief. It is not a claim about every database system ever built. A small or unusual system may accept only one spelling, so a query intended for an unfamiliar system should be checked against that system’s documentation before it is relied on. Second, the SQL Server note about ANSI status follows the historical T-SQL documentation pattern, in which <> is described as the ANSI standard form and != as the non standard form. Both are accepted in current SQL Server comparison operator lists. The practical advice is unchanged: prefer <> for portable code, accept != when reading existing code, and never assume the two symbols differ in result within these three dialects.

Style guides sometimes add a further reason to prefer <>. A reader scanning a query sees <> and knows at once that the code is SQL, while != could come from almost any programming language. That readability point is a convention, not a rule of the language, but conventions reduce reading errors in shared code, and shared code is where most SQL lives.

Equality Across Data Types

Equality means the same thing in outline for every type, but the details of what counts as equal depend on the type and on the database configuration. The safest way to think about it is to ask, for each type, what the database actually compares.

Numbers compare by numeric value. The integers 45 and 45 are equal. The decimal values 45.0 and 45.00 are equal as numbers, even though their written forms differ, because the comparison is about value rather than text. That is why a query that filters on a price or a quantity should compare numbers with numbers, not numbers with text, wherever possible. Mixing types forces the database to convert one side, and conversion rules differ by dialect and can surprise a reader, for example when a text value that looks numeric is converted before comparison.

Text compares character by character, but case and trailing spaces depend on collation, which is the set of rules a database uses to order and compare text. Many default configurations compare text without regard to case, so ‘Shipped’ and ‘shipped’ may compare as equal, while a case sensitive configuration treats them as different. This guide does not claim one universal case rule, because the verified sources do not support one: the correct statement is that collation decides, and a query that must be case sensitive or case insensitive should state that requirement explicitly, for example by using a stated collation or a case conversion function, rather than assuming a default. The same caution applies to trailing spaces, where some configurations ignore them in certain comparisons and others do not. When exact text matching matters, test the query against real data in the target database instead of trusting an assumption carried over from another system.

Dates and times compare chronologically. The date 2026-03-02 equals itself and is not equal to 2026-03-03. A date and time value adds a further trap: a timestamp of 2026-03-02 at 09:30 is not equal to the date 2026-03-02 at midnight, because the time parts differ. A query that filters by a whole day should therefore use a range, such as a start date and an end date, or a date conversion, rather than an equality test against a date alone when the column stores times. This is a common source of “missing rows” reports that are not equality bugs at all: the equality test worked exactly as written, but the stored value contained more precision than the filter assumed.

Boolean values compare as true or false. A query such as WHERE is_active = TRUE keeps rows whose flag is true. A row whose flag is NULL is not kept, for the same NULL reason that governs every other type: NULL compared with true is unknown, not false, and WHERE keeps only true rows. Boolean handling is covered again in the NULL section, because it is where boolean filters most often confuse readers.

Type consistency is the practical lesson of this section. Compare numbers with numbers, dates with dates, and text with text. When a comparison must cross types, convert explicitly and check the result on sample data. An equality test is only as reliable as the values it is given.

NULL and Three Valued Logic: The Core of This Topic

NULL means no value is recorded. It is not zero, not an empty string, and not a placeholder value. Because a missing value could in principle be anything, SQL cannot say whether it equals a known value or differs from it. The language therefore uses three valued logic: a comparison can be true, false, or unknown. Unknown is represented as NULL in a comparison result.

PostgreSQL documentation states this rule directly: ordinary comparison operators yield null, signifying unknown, when either input is null. The examples given are 7 = NULL, which yields null, and 7 <> NULL, which also yields null. MySQL documentation gives the same rule: if one or both arguments are NULL, the result of the comparison is NULL, except for the NULL safe <=> operator. Microsoft documentation for SQL Server describes the same three outcomes, true, false, and unknown, and states that two null values compared with each other are not considered equal.

The consequence for WHERE clauses is the single most important sentence in this guide: WHERE keeps only rows where the condition is true. It drops rows where the condition is false, and it also drops rows where the condition is unknown. A filter therefore silently excludes NULL rows, whether the filter uses = or <>. That silence is what makes NULL bugs hard to spot. The query runs without an error, returns a plausible set of rows, and omits exactly the rows the reader forgot to consider.

The sample table makes this concrete. Run a not equal test on the discount code column:

SELECT order_id, customer, discount_code
FROM orders
WHERE discount_code <> 'SPRING10';

A reader who thinks in two valued logic might expect six rows: every order except the four SPRING10 orders. The actual result is four rows: orders 104, 107, 109, and 110. Orders 102 and 105 are missing, even though their discount codes are certainly not SPRING10 in any ordinary sense. They are missing because their discount codes are NULL, so NULL <> 'SPRING10' is unknown, and WHERE drops unknown rows. The query did not say “give me orders without SPRING10”. It said “give me orders whose discount code is known and is not SPRING10”, and the database answered exactly that question.

The equal version has the mirror problem. The query below returns only the four SPRING10 orders:

SELECT order_id, customer, discount_code
FROM orders
WHERE discount_code = 'SPRING10';

Orders 102 and 105 do not appear here either, because NULL = 'SPRING10' is unknown. Together, the equal and not equal queries return eight rows, not ten. The two missing rows are the NULL rows, which belong to neither result set. Any reader who assumes that equal and not equal split a table into two complete halves will be wrong whenever NULL is present. That assumption is safe only for columns that cannot contain NULL.

Truth tables make the rule easy to check. For the equal operator, with a known value A and a second value B:

For logical combinations, the unknown value propagates in a defined way. True AND unknown is unknown. False AND unknown is false, because one false side is enough to make an AND false regardless of the other side. True OR unknown is true, because one true side is enough to make an OR true. False OR unknown is unknown. NOT unknown is unknown. These rules explain why adding a condition sometimes rescues NULL rows and sometimes does not: it depends on whether the added condition forces a definite true or false, or merely adds another unknown.

A useful check for any filter is to ask two questions before running it. First, can this column contain NULL? Second, if it can, does my question intend to include or exclude the NULL rows? If the answer to the first question is yes and the second question has not been answered, the query is not finished. Add an explicit NULL test, as the next section shows, so the intent is written in the query rather than left to the default behavior of WHERE.

IS NULL and IS NOT NULL: The Correct NULL Tests

SQL provides dedicated predicates for testing whether a value is missing. IS NULL returns true when the value is NULL and false otherwise. IS NOT NULL returns true when the value is present and false when it is NULL. Unlike = and <>, these predicates never return unknown for an ordinary column value. They answer a different question: not “does this value equal that value”, but “is a value recorded at all”.

SELECT order_id, customer, discount_code
FROM orders
WHERE discount_code IS NULL;

This returns orders 102 and 105, the two rows the earlier equal and not equal queries both missed. The companion query returns the other eight rows:

SELECT order_id, customer, discount_code
FROM orders
WHERE discount_code IS NOT NULL;

To express “orders without SPRING10, including orders with no code recorded”, combine a not equal test with an explicit NULL test:

SELECT order_id, customer, discount_code
FROM orders
WHERE discount_code <> 'SPRING10'
   OR discount_code IS NULL;

This returns six rows: 102, 104, 105, 107, 109, and 110. The OR is essential. Without it, the NULL rows are dropped by the unknown result of the not equal test. With it, the NULL rows satisfy the second condition, which is true, so the combined condition is true and the rows are kept. Parentheses matter when this pattern is combined with other conditions, because AND is evaluated before OR in the usual precedence rules. When in doubt, add parentheses to state the grouping explicitly.

The same pattern applies to dates. Order 103 has no ship date. A query for orders not shipped on 2026-03-02 will miss order 103 unless it adds OR ship_date IS NULL, if the intent is to include unshipped or undated orders. Whether undated orders should be included is a business question, not a SQL question. The SQL point is that the query must say so, because the default behavior excludes them without warning.

A related predicate family, IS TRUE, IS FALSE, and IS UNKNOWN, exists in PostgreSQL for boolean expressions. These predicates always return true or false, never unknown, because they test the logical value itself. For example, a boolean expression that is NULL is not true, so NULL::boolean IS TRUE is false in PostgreSQL, while NULL::boolean IS UNKNOWN is true. This guide notes the family for completeness, but ordinary column work rarely needs it: IS NULL and IS NOT NULL cover the common cases, and boolean columns should usually be tested with = TRUE, = FALSE, or an explicit IS NULL check, depending on the question being asked.

The NOT IN Pitfall

NOT IN is a not equal test written in list form, and it inherits the NULL problem in its most damaging form. The query below looks harmless:

SELECT order_id, customer
FROM orders
WHERE discount_code NOT IN ('SPRING10', 'FALL20');

If every discount code in the table were known, this would return the orders whose code is neither SPRING10 nor FALL20, namely orders 109 and 110. But the sample table contains NULL discount codes, and the logic of NOT IN expands to a chain of AND conditions: code is not SPRING10 AND code is not FALL20. For order 102, whose code is NULL, the first part is unknown, so the whole AND chain is unknown, and the row is dropped. The same happens for order 105. In this example the query still returns orders 109 and 110, because those rows satisfy both parts. The catastrophic version appears when the list itself contains NULL, rather than the column. Consider a block list table that contains a NULL entry, perhaps from an incomplete import. A query of the form WHERE customer_id NOT IN (SELECT blocked_id FROM block_list) expands, for each candidate row, to a chain that includes customer_id <> NULL, which is unknown. An AND chain containing an unknown part can never be true unless another part is false, and for rows that match no block entry, no part is false. The result is that the query returns no rows at all, even for customers who are plainly not blocked. The query runs without an error, which makes the failure easy to miss in a report or a data export.

There are three reliable fixes, and the choice depends on intent. First, exclude NULL from the list explicitly, for example by adding WHERE blocked_id IS NOT NULL inside the subquery. Second, use NOT EXISTS, which tests for the existence of a matching row rather than comparing values, and therefore does not suffer the same NULL expansion:

SELECT o.order_id, o.customer
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM block_list b
    WHERE b.blocked_id = o.order_id
);

In this form, a NULL blocked identifier simply fails to match any order, because NULL = order_id is unknown rather than true, and NOT EXISTS keeps rows for which no matching row exists. Third, when the intent is to treat NULL as a comparable value, use a NULL aware comparison, described in the next section. The rule to remember is short: never use NOT IN on a list that may contain NULL without an explicit NULL guard. Check the list, or use NOT EXISTS.

The positive form, IN, has a milder version of the same issue. WHERE discount_code IN ('SPRING10', 'FALL20') returns the six matching orders and drops the NULL rows, because for them the result is unknown. That is usually acceptable, because an IN query asks for known matches. The asymmetry is worth noting: IN with NULL fails safe by omitting uncertain rows from a match list, while NOT IN with NULL fails dangerous by omitting every row from an exclusion list. That asymmetry is why NOT IN deserves its own warning.

NULL Aware Comparisons: IS DISTINCT FROM and MySQL <=>

Sometimes the question genuinely is “are these two values different, treating missing as a value in its own right”. For that question, ordinary <> is the wrong tool, because it returns unknown for NULL. Two NULL aware tools exist, and their availability depends on the dialect.

PostgreSQL supports IS DISTINCT FROM and IS NOT DISTINCT FROM. These predicates treat NULL as a comparable value. PostgreSQL documentation gives the examples directly: 1 IS DISTINCT FROM NULL is true, NULL IS DISTINCT FROM NULL is false, 1 IS NOT DISTINCT FROM NULL is false, and NULL IS NOT DISTINCT FROM NULL is true. For non NULL inputs they behave exactly like <> and = respectively. They always return true or false, never unknown, which makes them ideal for change detection, such as finding rows where a value changed between two snapshots, including changes from a value to NULL or from NULL to a value.

SELECT order_id, customer
FROM orders
WHERE discount_code IS DISTINCT FROM 'SPRING10';

In PostgreSQL this returns six rows: 102, 104, 105, 107, 109, and 110. Unlike the <> version, it includes the NULL rows, because a NULL code is treated as distinct from SPRING10. That is the answer a reader usually means when they say “orders without SPRING10” in plain language, and it is the reason this predicate exists.

SQL Server added IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 2022. Microsoft Learn confirms that availability: the IS [NOT] DISTINCT FROM (Transact-SQL) page lists SQL Server 2022 (16.x) among its applies-to versions and guarantees a true or false result even when an operand is NULL, because NULL values count as known values in this predicate. Readers on older SQL Server versions should use the explicit OR pattern from the IS NULL section instead, which works in every version.

MySQL provides a different tool: the NULL safe equality operator <=>. MySQL documentation states that <=> performs an equality comparison like =, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL. There is no separate NULL safe not equal symbol in MySQL. The equivalent is to negate the NULL safe equality test, as in NOT (discount_code <=> 'SPRING10'). That expression returns true for NULL codes as well as for known different codes, which matches the IS DISTINCT FROM result.

The practical advice is to use the NULL aware tool only when the question requires it. For most filters, an explicit IS NULL or IS NOT NULL test is clearer to the next reader than a NULL aware predicate, because it states the intent in plain terms. Reserve IS DISTINCT FROM and <=> for change detection, snapshot comparison, and join conditions where NULL keys must match, and add a short comment explaining the choice when the query will be read by others.

Using = and <> in UPDATE, DELETE, and JOIN Work

Equality and inequality tests are not confined to SELECT filters. They control which rows an UPDATE changes and which rows a DELETE removes, so a NULL mistake in those statements changes or deletes the wrong set of rows, or no rows at all.

UPDATE orders
SET status = 'shipped'
WHERE order_id = 102;

This changes one row, because order_id 102 is known and equal to 102. A condition such as WHERE discount_code <> 'SPRING10' in an UPDATE would change the four known non SPRING10 rows and leave the NULL rows unchanged, for the same reason as in SELECT. If the intent is to change NULL rows too, the condition must add OR discount_code IS NULL. The same rule governs DELETE. A DELETE with a not equal condition does not delete NULL rows, which is usually safe but can surprise a reader who expected a clean sweep. Always preview a destructive statement as a SELECT with the same WHERE clause first, count the rows, and check whether NULL rows are included or excluded as intended.

In JOIN work, equality in the ON clause matches rows, and the related inner join explainer covers the join forms. The equality point to retain here is that a join on a nullable key will not match NULL to NULL. Two orders with NULL discount codes do not join to each other on that code, because NULL = NULL is unknown. When a task genuinely requires NULL keys to match, use a NULL aware predicate in the ON clause where the dialect supports it, or add an explicit condition that both sides are NULL. Otherwise, treat unmatched NULL rows as expected, not as a data error.

Equality and inequality also appear inside CASE expressions and in HAVING filters after grouping. The SQL CASE explainer covers conditional values in full, and the HAVING explainer covers post aggregation filtering. The shared principle is that a CASE branch tested with = or <> follows the same NULL rule: a WHEN condition that evaluates to unknown does not match, so the branch is skipped. A CASE that must handle NULL needs an explicit WHEN column IS NULL branch. Similarly, the count of count explainer is the place to go for nested counting, where equality tests in inner queries must be checked with the same NULL care as outer queries. Readers building broader SQL skills will also find the Wave 101 companion on SQL for data analysts useful for how these operators fit into everyday analysis work, once that article is live.

Combining Comparisons With AND, OR, and NOT

Real filters rarely use one comparison alone. They combine several with AND, OR, and NOT, and the combination rules decide the result. AND keeps a row only when every part is true. OR keeps a row when at least one part is true. NOT reverses true to false and false to true, but NOT unknown stays unknown. That last rule catches readers who assume that NOT of a failed test must succeed. For a NULL row, NOT (discount_code = 'SPRING10') is NOT unknown, which is unknown, so the row is still dropped by WHERE. To include NULL rows under a negation, add the explicit NULL test rather than relying on NOT alone.

Parentheses control grouping, and they should be used whenever AND and OR appear together. The query below is unambiguous because the OR pair is grouped:

SELECT order_id, customer
FROM orders
WHERE (status = 'shipped' OR status = 'pending')
  AND region <> 'West';

Without parentheses, AND is evaluated before OR under the usual precedence rules, which would change the meaning to shipped orders plus pending non Western orders. The result set would differ, and the difference would be silent. A reader who writes parentheses even when they are technically unnecessary is not being cautious for no reason; they are making the intent visible to the next person who edits the query.

A second combination pattern deserves attention: testing a column against several values. The query WHERE status <> 'shipped' AND status <> 'cancelled' keeps only pending orders, because a row must satisfy both not equal tests. The query WHERE status <> 'shipped' OR status <> 'cancelled' keeps every known row, because every known status differs from at least one of the two values. That second form is almost never what the writer intended, yet it appears regularly in beginner code. When the intent is “neither of these values”, use AND between not equal tests, or use NOT IN with a NULL guard, as described above.

Common Mistakes Checklist

The following mistakes account for most equal and not equal errors in practice. Check each one before treating a query as finished.

1. Writing = NULL or <> NULL instead of IS NULL or IS NOT NULL. Both forms return unknown for every row, so the filter returns nothing useful.
2. Assuming not equal returns “everything else”. It returns everything known and different. NULL rows are excluded unless an explicit NULL test is added.
3. Using NOT IN on a list that may contain NULL. The result can be an empty set with no error. Guard the list or use NOT EXISTS.
4. Writing == for equality in SQL. SQL uses a single =. The double form belongs to other languages.
5. Comparing a date column that stores times with a date alone, and concluding that rows are missing. The time part makes the values unequal.
6. Assuming text comparison is always case insensitive, or always case sensitive. Collation decides, and it must be checked in the target database.
7. Mixing <> and != without a project convention, so readers must guess whether the symbols differ. In the dialects covered here they do not, but a consistent style removes the question.
8. Forgetting parentheses when AND and OR are combined, so the database groups the conditions differently from the writer’s intent.
9. Using OR between not equal tests when AND was intended, which keeps almost every row instead of excluding two values.
10. Running an UPDATE or DELETE with a not equal condition without previewing it as a SELECT, so NULL rows are silently left unchanged or undeleted.

Each item on this list is cheap to check and expensive to debug after the query has fed a report, an export, or a data change. A two minute review of the WHERE clause against this list, with the NULL question asked explicitly, prevents most of the damage.

Choosing the Right Operator

A short decision guide covers most everyday cases. Use = when the question is “does this value match this value”, and the column cannot contain NULL or the NULL rows are meant to be excluded. Use <> when the question is “is this value known and different from this value”, and prefer <> over != for portable code. Use IS NULL when the question is “is a value recorded at all”, and IS NOT NULL for the reverse. Use OR column IS NULL alongside a not equal test when the intent includes missing values. Use NOT EXISTS instead of NOT IN when the exclusion list may contain NULL. Use IS DISTINCT FROM or MySQL <=>, negated, only when the task is change detection or NULL aware matching, and note the dialect limit when the query must run elsewhere.

Readability is part of the choice. A query that a colleague can read correctly on first sight is a better query than a shorter query that requires a second reading to catch a NULL trap. Name the intent in the query: add the NULL test, group the OR pair in parentheses, and keep one not equal spelling throughout the project. Those habits cost almost nothing at writing time and save real debugging time later.

FAQ

What is the difference between = and == in SQL?

In SQL, = is the equality operator. The symbol == is not the SQL equality test. It is used in some programming languages, but in SQL a comparison should use a single equals sign. A query written with == should be corrected to = before it is run.

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

In PostgreSQL, MySQL, and SQL Server, there is no difference in result. Both test for inequality. <> is the standard SQL spelling, and PostgreSQL documentation states that != is an alias converted to <> early in parsing. Prefer <> for portable code and use one spelling consistently within a project.

Why does = NULL return no rows?

Because = NULL does not test for missing values. A comparison with NULL yields unknown, and a WHERE clause keeps only true rows. To find missing values, use IS NULL. To find present values, use IS NOT NULL.

Why does <> exclude NULL rows?

Because a NULL value is not known to be different from the compared value. NULL <> 'SPRING10' is unknown, not true, so WHERE drops the row. To include NULL rows, add OR column IS NULL to the condition, or use a NULL aware predicate where the dialect supports one.

Does NOT IN fail when the list contains NULL?

Yes, it can return no rows at all. NOT IN expands to a chain of AND conditions, and a comparison with NULL in that chain makes the result unknown rather than true for rows that match no list entry. Guard the list with IS NOT NULL, or use NOT EXISTS instead.

Is NULL equal to NULL in SQL?

No, not under ordinary comparison. NULL = NULL is unknown, because two unknown values cannot be shown to be equal. To test whether two values are the same while treating NULL as comparable, use IS NOT DISTINCT FROM in PostgreSQL or in SQL Server 2022 and later, or the negated <=> test in MySQL. For a single column, IS NULL tests directly whether the value is missing.

Should I use <> or != for new code?

Use <>. It is the standard spelling, it is accepted by PostgreSQL, MySQL, and SQL Server, and it signals SQL to the reader at a glance. Existing code that uses != does not need to be rewritten for correctness in those dialects, but new code in a shared project should follow the project convention, and <> is the safer default convention.

Can I compare numbers, text, and dates with the same operators?

Yes, = and <> work across comparable types, but the details differ. Numbers compare by value, text comparison depends on collation for case and trailing spaces, and date and time values compare including their time parts. Compare like types with like types, convert explicitly when types must cross, and test against real data when exact matching matters.

Sources & references

• PostgreSQL Documentation, Comparison Functions and Operators, Table 9.1 and Table 9.2 and the notes on ordinary comparisons yielding null and on IS DISTINCT FROM. Verified 4 Nov 2026.
• MySQL 8.0 Reference Manual, Comparison Functions and Operators, Table 14.4 and the sections on =, <> and !=, <=>, and IS NULL and IS NOT NULL. Verified 4 Nov 2026 via search result text.
• Microsoft Learn, Transact-SQL, Not Equal To operator page and Operator Precedence page, listing <> and != among comparison operators. Verified 4 Nov 2026 via search result text.
• Microsoft Learn, IS [NOT] DISTINCT FROM (Transact-SQL), lists SQL Server 2022 (16.x) among its applies-to versions and states that the predicate guarantees a true or false result even if one or both operands are NULL. https://learn.microsoft.com/en-us/sql/t-sql/queries/is-distinct-from-transact-sql?view=sql-server-ver16&viewFallbackFrom=sql-server-2016 Verified 4 Nov 2026.
• Microsoft Learn, Handling Null Values, ADO.NET documentation, on three valued logic, true, false, and unknown, and on IS NULL under ANSI_NULLS on. Verified 4 Nov 2026 via search result text.
• Amberaisle ARTICLE_DATABASE.csv, rows for AMB-1429, AMB-1530, AMB-1531, and AMB-1532, confirming those SQL explainers and their live URLs. Checked 4 Nov 2026.