October 1, 2026

 

Introduction

When working with a MySQL database, we usually do not want to display all records at once. We often need to find specific data based on certain conditions.

For example:

• Find customers from Raipur

• Find students older than 18

• Find products between ₹1,000 and ₹5,000

• Find customers from Raipur or Bilaspur

• Search customers whose name starts with "Rahul"

• Find records where an email address is missing

MySQL provides several operators and conditions to filter data.

In this tutorial, we will learn:

• WHERE

• Comparison operators

• AND

• OR

• IN

• BETWEEN

• LIKE

• IS NULL

• IS NOT NULL

• Real-time search examples

• Practical website examples

---

1. What is WHERE in MySQL?

The WHERE clause is used to filter records based on a condition.

Basic Syntax

SELECT column_name
FROM table_name
WHERE condition;

Example

Suppose we have a students table:

id	name	city	age
1	Rahul	Raipur	20
2	Amit	Bilaspur	17
3	Priya	Raipur	22
4	Neha	Durg	19

To find students from Raipur:

SELECT *
FROM students
WHERE city = 'Raipur';

Result

id	name	city	age
1	Rahul	Raipur	20
3	Priya	Raipur	22

The WHERE clause tells MySQL to return only records that satisfy the condition.

---

2. Comparison Operators in MySQL

Comparison operators are used to compare values.

Operator	Meaning
=	Equal to
!=	Not equal to
<>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to

---

Example: Equal To

Find students whose age is 20:

SELECT *
FROM students
WHERE age = 20;

---

Example: Greater Than

Find students older than 18:

SELECT *
FROM students
WHERE age > 18;

---

Example: Less Than

Find students younger than 18:

SELECT *
FROM students
WHERE age < 18;

---

Example: Greater Than or Equal To

SELECT *
FROM students
WHERE age >= 18;

This returns students whose age is 18 or more.

---

Example: Not Equal To

SELECT *
FROM students
WHERE city != 'Raipur';

This returns students who are not from Raipur.

---

3. Using AND in MySQL

The AND operator is used when multiple conditions must be true.

Syntax

SELECT *
FROM table_name
WHERE condition1
AND condition2;

Example

Find students from Raipur who are older than 18:

SELECT *
FROM students
WHERE city = 'Raipur'
AND age > 18;

Both conditions must be satisfied.

Real-Time Example

Suppose an e-commerce website wants to find products that:

• Cost more than ₹1,000

• Have stock available

Query:

SELECT *
FROM products
WHERE price > 1000
AND stock > 0;

This is useful for displaying products that customers can actually purchase.

---

4. Using Multiple AND Conditions

You can use more than two conditions.

SELECT *
FROM products
WHERE category = 'Mobile'
AND price > 10000
AND stock > 0;

This finds mobile products:

• In the Mobile category

• Above ₹10,000

• Currently available in stock

---

5. Using OR in MySQL

The OR operator is used when any one of the conditions can be true.

Syntax

SELECT *
FROM table_name
WHERE condition1
OR condition2;

Example

Find students from Raipur or Durg:

SELECT *
FROM students
WHERE city = 'Raipur'
OR city = 'Durg';

MySQL returns students from either city.

---

6. AND vs OR

This is important for beginners.

AND

Both conditions must be true.

WHERE city = 'Raipur'
AND age > 18

Meaning:

Student must be from Raipur AND older than 18.

OR

At least one condition must be true.

WHERE city = 'Raipur'
OR city = 'Durg'

Meaning:

Student can be from Raipur OR Durg.

---

7. Using Parentheses with AND and OR

When combining AND and OR, parentheses make the logic clear.

Example:

SELECT *
FROM students
WHERE (city = 'Raipur' OR city = 'Durg')
AND age >= 18;

This means:

Find students from Raipur or Durg who are at least 18 years old.

Parentheses are especially useful in complex search queries.

---

8. IN Operator in MySQL

The IN operator is useful when you want to match multiple possible values.

Instead of writing:

SELECT *
FROM students
WHERE city = 'Raipur'
OR city = 'Durg'
OR city = 'Bilaspur';

You can write:

SELECT *
FROM students
WHERE city IN ('Raipur', 'Durg', 'Bilaspur');

This is shorter and easier to read.

---

9. Real-Time IN Example

Suppose an online store wants to display products from three categories:

• Mobile

• Laptop

• Tablet

Query:

SELECT *
FROM products
WHERE category IN ('Mobile', 'Laptop', 'Tablet');

This is commonly useful for website filters.

---

10. NOT IN

NOT IN returns records that do not match the specified values.

Example:

SELECT *
FROM students
WHERE city NOT IN ('Raipur', 'Durg');

This returns students who are not from Raipur or Durg.

---

11. BETWEEN Operator

The BETWEEN operator is used to find values within a specific range.

Syntax

SELECT *
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example

Find students whose age is between 18 and 25:

SELECT *
FROM students
WHERE age BETWEEN 18 AND 25;

BETWEEN includes both boundary values.

So:

18 → Included
25 → Included

---

12. Real-Time Price Example Using BETWEEN

Suppose an e-commerce website wants to display products between ₹1,000 and ₹5,000.

SELECT *
FROM products
WHERE price BETWEEN 1000 AND 5000;

This is useful for a website price filter.

For example:

₹500     ❌
₹1,000   ✅
₹2,500   ✅
₹5,000   ✅
₹7,000   ❌

---

13. NOT BETWEEN

You can also find values outside a range.

SELECT *
FROM products
WHERE price NOT BETWEEN 1000 AND 5000;

This returns products below ₹1,000 or above ₹5,000.

---

14. LIKE Operator

The LIKE operator is used for pattern matching.

It is extremely useful for website search functionality.

Syntax

SELECT *
FROM table_name
WHERE column_name LIKE 'pattern';

MySQL uses two important wildcard characters:

Wildcard	Meaning
%	Any number of characters
_	Exactly one character

---

15. LIKE — Starts With

Find students whose name starts with Rahul:

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

Possible results:

Rahul
Rahul Sharma
Rahul Kumar
Rahul Sahu

The % means any characters can come after Rahul.

---

16. LIKE — Ends With

Find names ending with Sharma:

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

Possible results:

Rahul Sharma
Amit Sharma
Neha Sharma

---

17. LIKE — Contains

Find names containing mit:

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

This can match names such as:

Amit
Amit Verma
Sumit
Sumit Sharma

This type of query is commonly used in search boxes.

---

18. LIKE with Mobile Numbers

Suppose you want to find mobile numbers beginning with 987.

SELECT *
FROM customers
WHERE mobile LIKE '987%';

This can be useful when filtering customer records.

---

19. LIKE with Email

Find customers using Gmail:

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

This finds email addresses ending with @gmail.com.

---

20. Underscore _ Wildcard

The underscore represents exactly one character.

Example:

SELECT *
FROM students
WHERE name LIKE 'A_it';

This can match values such as:

Amit

because _ represents one character.

---

21. IS NULL

NULL means a value is missing or not available.

Suppose some customers do not have an email address.

To find them:

SELECT *
FROM customers
WHERE email IS NULL;

Do not write:

WHERE email = NULL;

The correct syntax is:

WHERE email IS NULL;

---

22. IS NOT NULL

To find customers whose email is available:

SELECT *
FROM customers
WHERE email IS NOT NULL;

This returns records where the email column contains a value.

---

23. Real-Time CRM Example

Suppose Paramwebinfo has a CRM with a leads table:

CREATE TABLE leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    mobile VARCHAR(15),
    email VARCHAR(100),
    service VARCHAR(100),
    city VARCHAR(50),
    status VARCHAR(30)
);

Suppose the table contains:

id	name	service	city	status
1	Rahul	Website	Raipur	New
2	Amit	SEO	Bilaspur	Follow Up
3	Priya	Website	Raipur	Converted
4	Neha	Google Ads	Durg	New

---

Find all new leads

SELECT *
FROM leads
WHERE status = 'New';

---

Find Raipur leads

SELECT *
FROM leads
WHERE city = 'Raipur';

---

Find Raipur website leads

SELECT *
FROM leads
WHERE city = 'Raipur'
AND service = 'Website';

---

Find leads from Raipur or Durg

SELECT *
FROM leads
WHERE city IN ('Raipur', 'Durg');

---

Find Website or SEO leads

SELECT *
FROM leads
WHERE service IN ('Website', 'SEO');

---

Search leads whose name starts with "Ra"

SELECT *
FROM leads
WHERE name LIKE 'Ra%';

---

Search leads whose name contains "ha"

SELECT *
FROM leads
WHERE name LIKE '%ha%';

---

24. Real-Time Website Search Example

Imagine a CRM has a search box:

--------------------------------
Search Customer: Rahul
--------------------------------

When the user enters Rahul, PHP/CodeIgniter can send a query like:

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

The database returns matching customers.

This is the basic concept behind many website search features.

---

25. Real-Time Product Filter Example

Suppose an e-commerce website has filters:

Category: Mobile
Price: ₹10,000 - ₹30,000
Stock: Available

The SQL query could be:

SELECT *
FROM products
WHERE category = 'Mobile'
AND price BETWEEN 10000 AND 30000
AND stock > 0;

This is a practical example of combining:

• WHERE

• AND

• BETWEEN

• >

---

26. Combining IN, BETWEEN and LIKE

You can combine different operators.

Example:

SELECT *
FROM products
WHERE category IN ('Mobile', 'Laptop')
AND price BETWEEN 20000 AND 60000
AND name LIKE '%Pro%';

Meaning:

Find products belonging to Mobile or Laptop, priced between ₹20,000 and ₹60,000, and whose name contains "Pro".

---

27. Common Beginner Mistakes

Mistake 1 — Forgetting quotes around text

Incorrect:

SELECT *
FROM students
WHERE city = Raipur;

Correct:

SELECT *
FROM students
WHERE city = 'Raipur';

---

Mistake 2 — Using = with NULL

Incorrect:

WHERE email = NULL;

Correct:

WHERE email IS NULL;

---

Mistake 3 — Forgetting WHERE in UPDATE

Be careful with:

UPDATE students
SET city = 'Raipur';

This can update every record.

Instead:

UPDATE students
SET city = 'Raipur'
WHERE id = 5;

---

Mistake 4 — Forgetting WHERE in DELETE

Avoid:

DELETE FROM students;

if you only want to delete one student.

Use:

DELETE FROM students
WHERE id = 5;

---

28. Quick Reference

Requirement	SQL
Equal	=
Not equal	!=
Greater than	>
Less than	<
Multiple conditions	AND
Either condition	OR
Multiple values	IN
Range	BETWEEN
Search pattern	LIKE
Missing value	IS NULL
Available value	IS NOT NULL
Exclude values	NOT IN
Exclude range	NOT BETWEEN

---

29. Practice Exercises

Create a products table:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock INT
);

Insert some products and practice these queries:

Exercise 1

Find products costing more than ₹20,000.

Exercise 2

Find products between ₹10,000 and ₹50,000.

Exercise 3

Find products from Mobile or Laptop categories.

Exercise 4

Find products whose name contains Pro.

Exercise 5

Find products with stock greater than 0.

Exercise 6

Find products costing less than ₹10,000 OR having stock greater than 50.

Exercise 7

Find products whose category is not Mobile.

These exercises will help you understand filtering through practical queries.

---

30. Conclusion

The WHERE clause and filtering operators are fundamental parts of MySQL.

You can use:

WHERE
AND
OR
IN
BETWEEN
LIKE
IS NULL
IS NOT NULL

to find exactly the data your application needs.

These concepts are used extensively in real-world applications such as:

• CRM systems

• E-commerce websites

• School Management Systems

• Hotel Management Systems

• Job Portals

• Billing Software

• ERP Systems

• News Portals

• Customer Management Systems

Once you understand these operators, you can build much more powerful database searches and filters.

What's Next?

In the next MySQL tutorial, we will learn:

MySQL ORDER BY, LIMIT & DISTINCT: Sorting, Removing Duplicates and Managing Query Results

We will cover sorting records, limiting results, removing duplicate values, pagination basics, and real-world website examples.