MySQL Comparison Operators

Comparison operators compare one value with another value.

SELECT *
FROM students
WHERE age > 20;

Here, > compares the age column with 20.

The = operator checks whether two values are equal.

SELECT *
FROM students
WHERE age = 20;

This returns students whose age is exactly 20.

The equal operator can also compare text values.

SELECT *
FROM students
WHERE course = 'Python';

This returns students whose course is Python.

The != operator checks whether two values are different.

SELECT *
FROM students
WHERE age != 20;

This returns students whose age is not 20.

MySQL also supports <> as a not-equal operator.

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

This returns students whose course is not Python.

The > operator finds values greater than a specified value.

SELECT *
FROM students
WHERE age > 18;

This returns students older than 18.

The < operator finds values less than a specified value.

SELECT *
FROM students
WHERE age < 25;

This returns students younger than 25.

The >= operator includes the specified value as well as larger values.

SELECT *
FROM students
WHERE age >= 18;

This returns students whose age is 18 or greater.

The <= operator includes the specified value as well as smaller values.

SELECT *
FROM students
WHERE age <= 25;

This returns students whose age is 25 or less.

Comparison operators can be used with decimal values.

SELECT *
FROM students
WHERE fee > 10000.00;

This returns students whose fee is greater than 10,000.

Dates can also be compared using comparison operators.

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

This returns students whose admission date is after January 1, 2026.

Text columns can be compared with text values.

SELECT *
FROM students
WHERE name = 'Rahul';

This finds records with the specified name.

Comparison operators are frequently used inside WHERE.

SELECT name, age, fee
FROM students
WHERE fee >= 15000;

Only records satisfying the condition are returned.

You can combine comparison operators using AND.

SELECT *
FROM students
WHERE age > 18
AND fee > 10000;

Both conditions must be true.

OR allows either comparison condition to be true.

SELECT *
FROM students
WHERE age < 18
OR fee > 20000;

A record is returned if at least one condition is satisfied.

Several comparison operators can be used in one query.

SELECT *
FROM students
WHERE age >= 18
AND age <= 25
AND fee > 10000;

This finds students aged from 18 to 25 whose fee is greater than 10,000.

NOT can reverse a comparison condition.

SELECT *
FROM students
WHERE NOT age > 20;

This selects records where the condition age > 20 is not true.

Normal comparison operators should not be used to test whether a value is NULL.

Incorrect:

SELECT *
FROM students
WHERE mobile = NULL;

Correct:

SELECT *
FROM students
WHERE mobile IS NULL;

Use IS NOT NULL when you want records containing a value.

SELECT *
FROM students
WHERE mobile IS NOT NULL;

Comparison operators can be used with UPDATE to select specific records.

UPDATE students
SET fee = fee + 1000
WHERE fee < 15000;

This increases the fee for students whose current fee is less than 15,000.

Comparison operators can also be used with DELETE.

DELETE FROM students
WHERE age < 18;

This removes records where the age is less than 18.

Comparison conditions can be combined with sorting.

SELECT name, course, fee
FROM students
WHERE fee > 10000
ORDER BY fee DESC;

First, records are filtered and then sorted by fee.

LIMIT can restrict the number of matching records.

SELECT *
FROM students
WHERE age > 18
LIMIT 5;

This returns up to five students older than 18.

BETWEEN is useful when you need to compare a value against a range.

SELECT *
FROM students
WHERE age BETWEEN 18 AND 25;

This includes both boundary values.

IN can be used when comparing a column with multiple possible values.

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

This returns records whose age matches one of the listed values.

LIKE performs pattern matching and is commonly used with text columns.

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

This finds names that begin with A.

• Confusing = with !=
• Using = NULL instead of IS NULL
• Forgetting quotes around text values
• Using the wrong comparison operator
• Forgetting that > does not include the boundary value
• Forgetting that >= includes the boundary value
• Using UPDATE or DELETE without checking the condition

Suppose we want students whose age is at least 18 and whose fee is less than or equal to 20,000.

SELECT id, name, course, age, fee
FROM students
WHERE age >= 18
AND fee <= 20000
ORDER BY fee DESC;

This query uses two comparison operators together.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    course VARCHAR(100),
    age INT,
    fee DECIMAL(10,2)
);

INSERT INTO students
(name, course, age, fee)
VALUES
('Rahul', 'Python', 22, 15000.00),
('Priya', 'Java', 21, 18000.00),
('Amit', 'Python', 24, 12000.00),
('Neha', 'PHP', 17, 10000.00),
('Ravi', 'JavaScript', 26, 22000.00);

SELECT id, name, course, age, fee
FROM students
WHERE age >= 18
AND fee <= 20000
AND course != 'PHP'
ORDER BY fee DESC;

This query uses >=, <=, and != to filter student records.