MySQL Comparison Operators (IN, BETWEEN, LIKE, IS NULL, NOT NULL)

Introduction – Why Learn MySQL Comparison Operators?

When writing queries in MySQL, it’s not just about fetching data—it’s about fetching the right data. MySQL comparison operators like IN, BETWEEN, LIKE, IS NULL, and NOT NULL give you powerful ways to filter rows based on patterns, value ranges, or null checks.

In this tutorial, you’ll learn:

• Syntax and purpose of each comparison operator
• When and how to use IN, BETWEEN, LIKE, IS NULL, NOT NULL
• Examples of each operator in real-world scenarios
• Tips to write clear and efficient filter conditions

What Are MySQL Comparison Operators?

MySQL comparison operators are used inside WHERE clauses to evaluate conditions and return only matching rows. These operators offer various methods for value checking, such as membership (IN), pattern search (LIKE), null detection (IS NULL), and range selection (BETWEEN).

Examples of MySQL Comparison Operators

1. IN – Match Any Value in a List

SELECT * FROM employees
WHERE department IN ('HR', 'Finance', 'IT');

Returns rows where department is one of the listed values.

2. NOT IN – Exclude Values in a List

SELECT * FROM products
WHERE category NOT IN ('Clothing', 'Shoes');

Excludes products in the listed categories.

3. BETWEEN – Filter by Value Range (Inclusive)

SELECT * FROM orders
WHERE amount BETWEEN 1000 AND 5000;

Returns orders with amount from ₹1000 to ₹5000, inclusive.

4. LIKE – Pattern Matching

SELECT * FROM customers
WHERE email LIKE '%@gmail.com';

Matches any email ending with “@gmail.com”.

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

Matches names starting with the letter “A”.

5. IS NULL – Test for Null Values

SELECT * FROM payments
WHERE discount IS NULL;

Finds rows where the discount column has no value.

6. IS NOT NULL – Test for Non-Null Values

SELECT * FROM payments
WHERE discount IS NOT NULL;

Filters rows that have a valid (non-null) discount value.

Operator Comparison Table

Wildcard Characters for LIKE

Use Cases of Comparison Operators

Summary – Recap & Next Steps

MySQL comparison operators make your queries more targeted, efficient, and flexible. Whether you’re checking ranges, excluding nulls, or finding patterns, these operators are fundamental to advanced SQL querying.

Key Takeaways

• Use IN to match multiple values
• Use BETWEEN for range filtering
• Use LIKE for flexible text matching
• Use IS NULL / IS NOT NULL to check for presence/absence of values
• Combine with AND, OR, NOT for complex conditions

Real-World Relevance

From login systems to CRM filters, these operators power condition logic in nearly every MySQL-powered application.

FAQ – MySQL Comparison Operators

Is BETWEEN inclusive in MySQL?

Yes. BETWEEN 10 AND 20 includes 10 and 20.

Can I use LIKE with numbers?

Technically yes, but it’s meant for string comparisons. Convert numbers to strings with CAST() if needed.

What is the difference between = and IN?

= checks for a single value, while IN checks for a list:

WHERE department = 'Sales'          -- Single value  
WHERE department IN ('Sales','HR')  -- Multiple values

What does % mean in LIKE?

It matches any number of characters:

WHERE name LIKE 'A%' -- Names starting with A

How do I filter rows with no value?

Use IS NULL or IS NOT NULL.