8.2 Comparison Operators

Level: 8 | Version: v1.1 | Author: Meptrasoft

Overview

Comparison operators are used to compare values, usually inside a WHERE clause.

They help SQL determine whether a condition is true or false.

Operator	Meaning
`=`	Equal to
`!=` / `<>`	Not equal to
`>`	Greater than
`<`	Less than
`>=`	Greater than or equal to
`<=`	Less than or equal to

Comparison operator → Compare values and filter rows

SQL Comparison Operators: Compare values and filter rows using WHERE — Figure 1: Comparison operators compare column values with conditions and are commonly used with WHERE to filter rows.

Practice Table

This topic reuses the employees table from 8.1 The WHERE Clause.

emp_id	emp_name	dept_id	salary	phone_no
1	John Smith	10	45000	9876543210
2	Emma Brown	20	60000	NULL
3	Samuel Reed	10	32000	9123456780
4	Anderson	30	55000	NULL
5	Sarah Wilson	20	48000	9988776655
6	Tom Watson	10	51000	9090909090

Examples

Equal `=`

SELECT *
FROM employees
WHERE dept_id = 10;

Returns employees whose department is 10.

Not Equal `!=` or `<>`

SELECT *
FROM employees
WHERE dept_id != 10;

Returns employees whose department is not 10.

!= and <> are commonly supported for “not equal,” although <> is the standard SQL operator.

Greater Than `>`

SELECT *
FROM employees
WHERE salary > 50000;

Returns employees whose salary is greater than 50000.

Less Than `<`

SELECT *
FROM employees
WHERE salary < 50000;

Returns employees whose salary is less than 50000.

Greater Than or Equal `>=`

SELECT *
FROM employees
WHERE salary >= 50000;

Returns employees whose salary is 50000 or more.

Less Than or Equal `<=`

SELECT *
FROM employees
WHERE salary <= 50000;

Returns employees whose salary is 50000 or less.

Quick Comparison

salary > 50000   → More than 50000
salary < 50000   → Less than 50000
salary >= 50000  → 50000 or more
salary <= 50000  → 50000 or less
salary = 50000   → Exactly 50000
salary <> 50000  → Anything except 50000

The operator determines exactly which values satisfy the condition.

Important: NULL Values

Comparison operators do not work with NULL in the same way as normal values.

For example:

WHERE phone_no = NULL

is not the correct way to test for a missing value.

Use:

WHERE phone_no IS NULL;

The IS NULL topic will be covered separately.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
`=` means assignment in SQL	In a `WHERE` condition, `=` means equal
`!=` is the only not-equal operator	`<>` is also commonly used
`>=` means only greater than	It includes equality
`<=` means only less than	It includes equality
`= NULL` checks for NULL	Use `IS NULL`
Comparison operators change the data	They normally filter or compare values in a query

Placement Quick Points

=    → EQUAL
<>   → NOT EQUAL
>    → GREATER
<    → LESS
>=   → GREATER OR EQUAL
<=   → LESS OR EQUAL

Comparison operators are commonly used inside WHERE.
They compare values and produce a condition used for filtering.
= checks equality.
<> and != commonly mean not equal.
>= and <= include equality.
NULL should be checked using IS NULL or IS NOT NULL.

Interview Questions

What are comparison operators in SQL?

They are operators used to compare values in conditions.

What is the not-equal operator in SQL?

<> is the standard SQL not-equal operator, and != is also commonly supported.

Can comparison operators be used with WHERE?

Yes. They are commonly used with WHERE to filter rows.

How do you compare a value with NULL?

Use:

WHERE phone_no IS NULL;

not:

WHERE phone_no = NULL;

Practice & Hands-On Exercises

Using the employees table:

Find employees with dept_id = 10:

SELECT * FROM employees WHERE dept_id = 10;

Find employees with salary > 50000:

SELECT * FROM employees WHERE salary > 50000;

Find employees with salary < 50000:

SELECT * FROM employees WHERE salary < 50000;

Find employees with salary >= 50000:

SELECT * FROM employees WHERE salary >= 50000;

Find employees with salary <= 50000:

SELECT * FROM employees WHERE salary <= 50000;

Find employees whose department is not 10:

SELECT * FROM employees WHERE dept_id <> 10;

Explain the difference between > and >=.
Explain why = NULL should not be used.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

=    → EQUAL
<>   → NOT EQUAL
>    → GREATER
<    → LESS
>=   → GREATER OR EQUAL
<=   → LESS OR EQUAL

Comparison operators allow SQL to compare values and filter rows based on conditions.

End of Comparison Operators