Equal (=) & Not Equal (<>, !=)

The most common filters ask "is it exactly this?" or "is it anything but this?". Show me MI players uses equal. Show me everyone except MI uses not equal.

Definition

A comparison operator compares two expressions and returns TRUE, FALSE, or UNKNOWN (when either side is NULL).

• = (equal) is TRUE when both values are exactly the same.
• <> / != (not equal) is TRUE when the two values are different.

<> is the ISO/ANSI standard spelling. != means exactly the same thing; SQL Server, MySQL and PostgreSQL all accept it, but it is not part of the SQL standard. Prefer <> in professional code — though both are fine.

Syntax

SELECT column_list
FROM table_name
WHERE column_name = value;     -- exact match

SELECT column_list
FROM table_name
WHERE column_name <> value;    -- everything except value (same as !=)

Example — = keeps exact matches

SELECT *
FROM players
WHERE country = 'India';

= keeps rows where the value is exactly the same — so 4 of the 6 players stay.

Example — <> keeps everyone except MI

SELECT *
FROM players
WHERE team <> 'MI';

How it works: FROM loads 6 rows → WHERE drops the two MI rows (Rohit Sharma and Jasprit Bumrah) → SELECT shows all columns. Writing WHERE team != 'MI' gives the identical result.

Not equal and NULL — a hidden trap

In player_profiles, David Warner's team is NULL (unknown). Look what happens:

SELECT name, team
FROM player_profiles
WHERE team <> 'MI';

David Warner is missing, even though NULL is "not MI". Comparing NULL with anything (NULL <> 'MI') gives UNKNOWN, and WHERE throws away UNKNOWN rows. If you want him too, add OR team IS NULL.

Case sensitivity

Whether team = 'mi' matches 'MI' depends on the collation (the text-comparison rules) of the database:

Tip: To be safe everywhere, write values in the same case as the data, or compare UPPER(team) = 'MI'.

Common mistakes

• ❌ WHERE team = MI → missing quotes. ✅ WHERE team = 'MI'
• ❌ WHERE team = NULL → never TRUE. ✅ WHERE team IS NULL
• ❌ WHERE team =! 'MI' → not an operator. ✅ <> or !=
• ❌ Hidden spaces: ' India' (leading space) does not equal 'India'. SQL Server ignores trailing spaces in = comparisons, but never leading ones — keep values clean.

Interview tip

Q: Is there any difference between <> and !=? A: No difference in result or performance. <> is the ANSI standard; != is a widely supported extension. Neither will match NULL — use IS NULL / IS NOT NULL for that.