MySql - Comparison operators

MySQL Comparison Operators

MySQL is a popular relational database management system that uses Structured Query Language (SQL) to manage and manipulate data. In SQL, comparison operators are essential for filtering records, performing logical comparisons, and forming conditions in queries. These operators are used in WHERE clauses, CASE statements, and control structures to compare two expressions and return a Boolean result.

Introduction to Comparison Operators

Comparison operators evaluate the relationship between two values or expressions and return a Boolean result — either true or false. These operators are typically used in SELECT, UPDATE, DELETE, and INSERT statements to narrow down the affected rows based on specific criteria.

Why Use Comparison Operators?

• To filter records based on conditions.
• To control the flow in procedural SQL (with IF statements, CASE expressions, etc.).
• To compare values in columns or between columns and constants.
• To create dynamic and complex query logic.

List of MySQL Comparison Operators

Here are the most commonly used comparison operators in MySQL:

• = : Equal to
• > : Greater than
• < : Less than
• >= : Greater than or equal to
• <= : Less than or equal to
• <> : Not equal to (alternative: !=)

1. Equal To (=)

The equal to operator = is used to test whether two values are the same. If they are equal, the condition returns true; otherwise, it returns false.

Syntax

expression1 = expression2

Example

SELECT * FROM employees WHERE department = 'HR';

This query returns all records where the department column is equal to 'HR'.

Use Cases

• Finding specific values in a table.
• Matching foreign key relationships.
• Setting up conditional logic in CASE statements.

Points to Remember

• Case sensitivity depends on the collation of the column.
• NULL comparisons require special attention; column = NULL  is always false. Use IS NULL  instead.

2. Greater Than (>)

The greater than operator checks whether the value on the left is greater than the value on the right.

Syntax

expression1 > expression2

Example

SELECT * FROM orders WHERE amount > 500;

This query retrieves orders where the amount exceeds 500.

Use Cases

• Comparing numerical values such as prices, quantities, or ages.
• Filtering records after a certain date.

Tips

• Works on numeric, date, and time data types.
• Can be combined with ORDER BY for sorted results.

3. Less Than (<)

The less than operator returns true if the left expression is smaller than the right expression.

Syntax

expression1 < expression2

Example

SELECT * FROM products WHERE price < 100;

This returns all products priced below 100.

Use Cases

• Limiting values to a threshold.
• Selecting earlier dates in a timeline.

Best Practices

• Always verify the data type being compared.
• Use indexes for performance when filtering large datasets.

4. Greater Than or Equal To (>=)

This operator returns true if the left expression is either greater than or exactly equal to the right expression.

Syntax

expression1 >= expression2

Example

SELECT * FROM students WHERE marks >= 75;

This query retrieves all students who scored 75 or more.

Use Cases

• Defining eligibility criteria (e.g., passing marks).
• Creating ranges with lower limits included.

Important Notes

• Can be used with date and time to include a starting point.

5. Less Than or Equal To (<=)

The less than or equal to operator evaluates to true when the left operand is either smaller than or equal to the right operand.

Syntax

expression1 <= expression2

Example

SELECT * FROM employees WHERE experience <= 2;

Finds all employees with 2 or fewer years of experience.

Use Cases

• Setting upper limits on numeric values.
• Filtering records with end date inclusivity.

Pro Tips

• Double-check logical boundaries when forming ranges.

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

MySQL supports two syntax forms for "not equal to": <>  and != . Both yield the same result.

Syntax

expression1 <> expression2
expression1 != expression2

Example

SELECT * FROM employees WHERE department <> 'Finance';

This query returns all employees not working in the Finance department.

Use Cases

• Excluding certain categories or types.
• Creating negation logic in filters.

Important Notes

• NULL values are never equal to anything — even themselves.
• Always use IS NOT NULL for null comparisons.

Using Comparison Operators with NULL

NULL represents missing or undefined values. Using comparison operators with NULL returns unknown. Therefore, comparisons like column = NULL or column <> NULL always return false.

Proper Usage

SELECT * FROM users WHERE email IS NULL;

SELECT * FROM users WHERE email IS NOT NULL;

Combining Comparison Operators

Comparison operators can be combined with logical operators like AND, OR, and NOT to create complex conditions.

Example

SELECT * FROM products 
WHERE price >= 50 AND price <= 200;

This retrieves products priced between 50 and 200 (inclusive).

Comparison Operators with Dates

You can use comparison operators to filter based on date and time values.

Example

SELECT * FROM events 
WHERE event_date >= '2025-01-01';

Returns all events scheduled on or after January 1, 2025.

Comparison Operators in Subqueries

Subqueries often use comparison operators to compare values from the outer query.

Example

SELECT name FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

This query returns employees earning above the average salary.

Performance Considerations

To optimize query performance with comparison operators:

• Use indexes on the columns involved in comparison.
• Avoid functions on columns being compared (e.g., YEAR(date_column) = 2024 disables index use).
• Ensure proper data types to avoid unnecessary type conversions.

Best Practices

• Use consistent formatting for readability.
• Test boundary conditions in your queries (e.g., >= vs >).
• Always handle NULL explicitly.
• Prefer standard syntax like <> over != for better compatibility across databases.

Comparison operators in MySQL form the backbone of conditional logic within SQL queries. From simple checks like equality to complex range queries and subquery comparisons, understanding and using these operators correctly is vital for writing efficient and accurate SQL statements. Whether filtering customer orders, calculating statistics, or enforcing business logic, these operators ensure that the right data is retrieved, updated, or removed. Make sure to combine them wisely with logical operators and always test with real data to ensure your queries behave as expected.