🔎 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

Operator	Purpose	Example Usage
IN	Match against a list of values	WHERE city IN ('Delhi', 'Mumbai')
NOT IN	Exclude values from a list	WHERE role NOT IN ('guest', 'test')
BETWEEN	Check if a value is within a range	WHERE age BETWEEN 18 AND 30
LIKE	Match values using wildcard patterns	WHERE name LIKE '%Singh'
IS NULL	Check if a column has a NULL value	WHERE phone IS NULL
IS NOT NULL	Check if a column does not have NULL	WHERE email IS NOT NULL

---

🛠️ Wildcard Characters for LIKE

Wildcard	Meaning	Example
%	Zero or more characters	'S%' matches ‘Sam’, ‘Steve’, ‘Smith’
_	Exactly one character	'S_m' matches ‘Sam’, ‘Sim’

---

🔍 Use Cases of Comparison Operators

Scenario	SQL Example
✅ Find students aged between 18–25	SELECT * FROM students WHERE age BETWEEN 18 AND 25;
📧 List emails from Gmail	SELECT * FROM users WHERE email LIKE '%@gmail.com';
🧾 Show orders without discounts	SELECT * FROM orders WHERE discount IS NULL;
🚫 Exclude test users	SELECT * FROM users WHERE user_type NOT IN ('demo', 'test');
🔍 Match names starting with “Raj”	SELECT * FROM customers WHERE name LIKE 'Raj%';

---

📌 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.

---

Share Now :