NOT (Reverse a Condition)

Sometimes it is easier to describe what you don't want. "Everyone not from India." "Teams not in this list." NOT takes any condition and reverses it.

Definition

NOT is a unary logical operator that reverses the result of the condition after it: TRUE becomes FALSE and FALSE becomes TRUE. UNKNOWN stays UNKNOWN.

"Unary" means it works on one condition (unlike AND / OR, which join two). The last sentence is the important one: NOT cannot turn a NULL comparison into TRUE, so NULL rows are still excluded.

Syntax

SELECT column_list
FROM table_name
WHERE NOT condition;

NOT also combines with other operators:

Example — everyone NOT from India

SELECT *
FROM players
WHERE NOT country = 'India';

NOT flips the result: SQL first checks country = 'India', then keeps the rows where that was not true.

Example — NOT on a group in brackets

"Remove the Indian players who scored under 400" (i.e. remove MS Dhoni and Jasprit Bumrah):

SELECT name, team, country, runs
FROM players
WHERE NOT (country = 'India' AND runs < 400);

The brackets are evaluated first, then NOT reverses the combined answer. Without brackets, NOT would apply only to country = 'India' (NOT binds tighter than AND).

Note — De Morgan's law: NOT (A AND B) = NOT A OR NOT B, and NOT (A OR B) = NOT A AND NOT B. So the query above equals country <> 'India' OR runs >= 400.

Example — NOT and NULL

In player_profiles, three players have a NULL nickname.

SELECT name, nickname
FROM player_profiles
WHERE NOT nickname = 'King';

Only 2 rows, not 5. For NULL nicknames nickname = 'King' is UNKNOWN, and NOT UNKNOWN is still UNKNOWN → dropped. Add OR nickname IS NULL to include them.

Common mistakes

• ❌ WHERE country NOT = 'India' → invalid. ✅ WHERE NOT country = 'India' or country <> 'India'.
• ❌ WHERE NOT country = 'India' AND runs < 400 expecting NOT to cover both → NOT applies only to the first condition. ✅ add brackets: NOT (country = 'India' AND runs < 400).
• ❌ Expecting NOT to bring back NULL rows. ✅ handle NULL explicitly with IS NULL.

Interview tip

Q: Rewrite WHERE NOT (team = 'MI' OR team = 'CSK') without NOT. A: By De Morgan's law: WHERE team <> 'MI' AND team <> 'CSK' — which is also what team NOT IN ('MI', 'CSK') means.