Comparison Operators in SQL

Comparison operators compare one value with another value and produce a logical result that can be used to filter data.

Common SQL comparison operators include:

The = operator checks whether two values are equal.

SELECT *
FROM Students
WHERE age = 20;

This returns students whose age is exactly 20.

Text values are normally written inside single quotes.

SELECT *
FROM Students
WHERE course = 'Python';

This returns students whose course is Python.

SELECT *
FROM Students
WHERE fee = 10000.00;

This returns records where the fee is equal to 10000.00.

The > operator checks whether one value is greater than another.

SELECT *
FROM Students
WHERE age > 18;

This returns students older than 18.

To find students whose fee is greater than 5000:

SELECT name, fee
FROM Students
WHERE fee > 5000;

The < operator checks whether one value is less than another.

SELECT *
FROM Students
WHERE age < 18;

This returns students younger than 18.

SELECT name, fee
FROM Students
WHERE fee < 10000;

This returns students whose fee is less than 10000.

The >= operator means greater than or equal to.

SELECT *
FROM Students
WHERE age >= 18;

This includes students who are exactly 18 as well as those older than 18.

The <= operator means less than or equal to.

SELECT *
FROM Students
WHERE age <= 18;

This includes students who are exactly 18 as well as those younger than 18.

The <> operator means not equal to.

SELECT *
FROM Students
WHERE course <> 'Python';

This returns students whose course is not Python.

In MySQL, != can also be used to mean not equal.

SELECT *
FROM Students
WHERE age != 20;

This returns students whose age is not 20.

Both operators are commonly used for not-equal comparisons.

SELECT *
FROM Students
WHERE age <> 20;

and:

SELECT *
FROM Students
WHERE age != 20;

In MySQL, both forms mean that age should not equal 20.

SELECT *
FROM Students
WHERE city = 'Patna';

This checks whether the city value matches the specified text.

Text comparison can also be affected by the database's collation and comparison rules.

Comparison operators can be used with date values.

SELECT *
FROM Students
WHERE admission_date > '2026-01-01';

This finds records with an admission date later than January 1, 2026.

SELECT *
FROM Students
WHERE admission_date >= '2026-01-01';

This includes records dated January 1, 2026 and later.

SELECT *
FROM Students
WHERE admission_date < '2026-01-01';

This finds records before January 1, 2026.

Comparison operators can be combined using AND.

SELECT *
FROM Students
WHERE age > 18
AND fee > 5000;

Both conditions must be true.

SELECT *
FROM Students
WHERE age < 18
OR age > 60;

This returns students whose age is either below 18 or above 60.

SELECT *
FROM Students
WHERE NOT age = 20;

This returns records where age is not equal to 20.

BETWEEN provides a convenient way to compare a value against a range.

SELECT *
FROM Students
WHERE age BETWEEN 18 AND 25;

This includes both 18 and 25.

SELECT *
FROM Students
WHERE age NOT BETWEEN 18 AND 25;

This returns values outside the specified range, excluding NULL values from the comparison result.

The IN operator checks whether a value matches one of several values.

SELECT *
FROM Students
WHERE age IN (18, 20, 22);

This returns students whose age is 18, 20, or 22.

SELECT *
FROM Students
WHERE course NOT IN ('Python', 'Java');

This excludes Python and Java courses.

NULL should not be compared using ordinary operators such as = or <>.

Use:

SELECT *
FROM Students
WHERE mobile IS NULL;

or:

SELECT *
FROM Students
WHERE mobile IS NOT NULL;

LIKE is used when you want to compare text using a pattern rather than an exact value.

SELECT *
FROM Students
WHERE name LIKE 'A%';

This finds names beginning with A.

Suppose a training institute wants to find students who have a fee of at least ₹10,000.

SELECT name, course, fee
FROM Students
WHERE fee >= 10000;

This includes students whose fee is exactly 10000 and those with a higher fee.

SELECT name, age, course, fee
FROM Students
WHERE age >= 18
AND age <= 30
AND fee > 5000;

This finds students aged 18 to 30 whose fee is greater than 5000.

Comparison operators can be used with UPDATE.

UPDATE Students
SET status = 'Eligible'
WHERE age >= 18;

This updates records where the age is at least 18.

SELECT name, age, course, city, fee
FROM Students
WHERE age >= 18
AND age <= 30
AND fee > 5000
AND course <> 'Tally'
ORDER BY fee DESC;

This query:

• Finds students aged between 18 and 30.
• Requires a fee greater than 5000.
• Excludes students whose course is Tally.
• Sorts the result by fee from highest to lowest.