Comparison Operators

Comparisons involving NULL evaluate to UNKNOWN; use IS NULL or IS NOT NULL. <> is standard SQL, while != is also accepted by many databases.

1. Introduction

Comparison operators are used in SQL to compare values.

They are one of the most important building blocks of filtering data.

You will use them inside the WHERE clause to tell SQL:

• which rows are too small
• which rows are too large
• which rows match exactly
• which rows should be excluded

Without comparison operators, SQL would only be able to return everything.

These operators help SQL make decisions row by row.

2. What Are Comparison Operators?

Comparison operators compare one value against another value.

SQL checks the comparison and decides whether the result is:

• TRUE
• FALSE

If the condition is true, the row stays.

If the condition is false, the row is removed.

This is the foundation of filtering.

3. List of Comparison Operators

These are the core operators used in SQL filtering.

4. Why Comparison Operators Matter

In real projects, most questions are not exact-match questions.

They are usually range-based questions.

Examples:

• Which users are older than 18?
• Which products cost more than 500?
• Which items have fewer than 10 units left?
• Which employees earn at least 1 lakh?
• Which orders are not cancelled?

These are all comparison problems.

That is why comparison operators are one of the most frequently used SQL tools.

5. Deep Dive — Filtering with Ranges and Limits

In beginner SQL, most examples use exact matches:

WHERE city = 'Delhi'

But real business queries are usually about ranges.

Examples:

• greater than
• less than
• minimum
• maximum
• threshold
• limits

Comparison operators allow SQL to answer these real-world business questions.

This is heavily used in:

• reporting
• analytics
• pricing
• inventory
• finance
• dashboards

6. Real-Life Example — products Table

We will use the products table.

7. Greater Than (>)

Definition

The > operator returns rows where the value is strictly greater than the given value.

It excludes the exact value.

Query

SELECT product_name, price
FROM products
WHERE price > 150;

Explanation

Return products where price is greater than 150.

This means:

• 150 is excluded
• only values above 150 are allowed

Output

Use Cases

Used for:

• premium products
• high salaries
• expensive orders
• high-value customers

8. Less Than (<)

Definition

The < operator returns rows where the value is strictly smaller than the given value.

It excludes the exact value.

Example

SELECT product_name, price
FROM products
WHERE price < 100;

Output

Use Cases

Used for:

• budget products
• low prices
• low scores
• younger age groups

9. Greater Than or Equal To (>=)

Definition

The >= operator returns rows where the value is greater than or exactly equal to the given value.

Example

SELECT product_name, price
FROM products
WHERE price >= 250;

Output

Explanation

This includes:

• values above 250
• value exactly equal to 250

Use Cases

Used for:

• minimum thresholds
• high-value sales
• score cutoffs
• eligibility checks

10. Less Than or Equal To (<=)

Definition

The <= operator returns rows where the value is less than or exactly equal to the given value.

Query

SELECT product_name, stock_quantity
FROM products
WHERE stock_quantity <= 50;

Explanation

Return products where stock is:

• below 50
• exactly 50

Output

Use Cases

Used for:

• low stock alerts
• max limits
• warning thresholds
• low balance checks

11. Equal To (=)

Definition

The = operator returns rows where the value exactly matches.

This is the most common comparison operator.

Example

SELECT product_name, category
FROM products
WHERE category = 'Accessories';

Output

Use Cases

Used for:

• exact matches
• category filters
• city filters
• specific statuses

12. Not Equal To (!= or <>)

Definition

!= and <> both mean:

not equal to

They return rows where the value does not match.

Both are valid in SQL (depending on database dialect).

Query

SELECT product_name, category
FROM products
WHERE category != 'Accessories';

Output

Explanation

Exclude rows where category is Accessories.

Everything else is returned.

Use Cases

Used for:

• excluding categories
• excluding statuses
• filtering unwanted records

13. Common Mistakes to Avoid

1. Using == Instead of =

Incorrect:

WHERE price == 100

Correct:

WHERE price = 100

SQL uses single = for equality.

== is used in programming languages like Python and JavaScript, not SQL.

2. Putting Quotes Around Numbers

Avoid:

WHERE price > '100'

Prefer:

WHERE price > 100

Numbers should remain numeric.

Using quotes may force type conversion and slow the query.

3. Writing Operators Incorrectly

Correct:

<=
>=

Incorrect:

=<
=>

The equals sign always comes second.

4. Forgetting Exact Meaning

Be careful with boundaries:

• > means strictly greater
• >= means greater or equal
• < means strictly smaller
• <= means smaller or equal

This matters a lot in reporting and analytics.

14. Summary

Comparison operators are used to compare values and filter rows.

They help SQL answer questions involving:

• ranges
• thresholds
• limits
• exact matches
• exclusions

These operators are the foundation of real-world filtering.

Final Mental Model

Comparison operators help SQL answer:

• Is this bigger?
• Is this smaller?
• Is this equal?
• Should this row stay?
• Should this row be removed?