Advanced WHERE Clause in MySQL | BETWEEN, AND, OR, NOT, IS NULL and IS NOT NULL

The WHERE clause is used to retrieve only those records that satisfy a given condition. In real-world databases, a single condition is often not sufficient. SQL provides advanced operators such as BETWEEN, AND, OR, NOT, IS NULL, and IS NOT NULL to filter data more effectively.

These operators help users search for records within a range, combine multiple conditions, exclude specific records, and identify missing values. They are widely used in school management systems, banking software, hospitals, libraries, and e-commerce applications.

---

Learning Outcomes

After studying this chapter, you will be able to:

• Use the BETWEEN operator to retrieve records within a specified range.
• Combine multiple conditions using AND and OR.
• Exclude records using the NOT operator.
• Identify NULL values using IS NULL.
• Retrieve non-NULL values using IS NOT NULL.
• Write advanced SQL queries for real-life situations.

---

Sample Student Table

Roll_No	Name	Class	Marks	Email
101	Aarav	XI	91	aarav@gmail.com
102	Diya	XI	84	NULL
103	Kabir	XII	95	kabir@gmail.com
104	Riya	XII	78	NULL

---

1. BETWEEN Operator

Syntax

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example

SELECT *
FROM Student
WHERE Marks BETWEEN 80 AND 95;

The above query displays students whose marks are between 80 and 95, including both values.

---

2. AND Operator

Syntax

SELECT column_name
FROM table_name
WHERE condition1 AND condition2;

Example

SELECT *
FROM Student
WHERE Class = 'XI'
AND Marks > 85;

The query displays only those students who belong to Class XI and have marks greater than 85.

---

3. OR Operator

Syntax

SELECT column_name
FROM table_name
WHERE condition1 OR condition2;

Example

SELECT *
FROM Student
WHERE Class = 'XI'
OR Marks > 90;

The above query displays students who either belong to Class XI or have marks greater than 90.

---

Comparison: AND vs OR

Feature	AND	OR
Conditions	All conditions must be true.	At least one condition must be true.
Number of Records	Usually fewer records.	Usually more records.
Example	Class='XI' AND Marks>85	Class='XI' OR Marks>85

---

---

4. NOT Operator

Syntax

SELECT column_name
FROM table_name
WHERE NOT condition;

Example

SELECT *
FROM Student
WHERE NOT Class = 'XI';

The above query displays all students who do not belong to Class XI.

---

5. IS NULL Operator

A NULL value means that data is missing or unknown. It is different from zero (0) or an empty string ('').

Syntax

SELECT column_name
FROM table_name
WHERE column_name IS NULL;

Example

SELECT *
FROM Student
WHERE Email IS NULL;

This query displays students whose email addresses have not been entered.

---

6. IS NOT NULL Operator

Syntax

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;

Example

SELECT *
FROM Student
WHERE Email IS NOT NULL;

The above query displays students whose email addresses are available.

---

Comparison of Advanced WHERE Operators

Operator	Purpose	Example
BETWEEN	Selects values within a range.	Marks BETWEEN 80 AND 90
AND	All conditions must be true.	Class='XI' AND Marks>85
OR	At least one condition must be true.	Class='XI' OR Marks>85
NOT	Excludes matching records.	NOT Class='XI'
IS NULL	Finds missing values.	Email IS NULL
IS NOT NULL	Finds available values.	Email IS NOT NULL

---

Practical SQL Queries

Example 1

Display students whose marks are between 85 and 95.

SELECT *
FROM Student
WHERE Marks BETWEEN 85 AND 95;

---

Example 2

Display students of Class XII who scored more than 90 marks.

SELECT *
FROM Student
WHERE Class = 'XII'
AND Marks > 90;

---

Example 3

Display students who belong to Class XII or scored more than 90 marks.

SELECT *
FROM Student
WHERE Class = 'XII'
OR Marks > 90;

---

Example 4

Display students who do not belong to Class XII.

SELECT *
FROM Student
WHERE NOT Class = 'XII';

---

Example 5

Display students whose email addresses are not available.

SELECT *
FROM Student
WHERE Email IS NULL;

---

Example 6

Display students whose email addresses are available.

SELECT *
FROM Student
WHERE Email IS NOT NULL;

---

Solved Example 1

Display students whose marks are between 80 and 90.

Solution:

SELECT *
FROM Student
WHERE Marks BETWEEN 80 AND 90;

---

Solved Example 2

Display names of students from Class XI having marks greater than 85.

Solution:

SELECT Name
FROM Student
WHERE Class = 'XI'
AND Marks > 85;

---

Solved Example 3

Display records of students who either belong to Class XII or scored more than 90 marks.

Solution:

SELECT *
FROM Student
WHERE Class = 'XII'
OR Marks > 90;

---

Common Errors

Mistake	Correct Practice
Using = NULL	Use IS NULL instead.
Using != NULL	Use IS NOT NULL instead.
Using BETWEEN with values in the wrong order.	Write the smaller value first.
Confusing AND with OR.	Remember: AND requires all conditions; OR requires at least one.
Using NOT incorrectly.	Ensure that NOT is applied to the intended condition.

---

Interview Corner

---

---

Real-Life Applications of Advanced WHERE Clause

Organization	SQL Query	Purpose
School	SELECT * FROM Student WHERE Marks BETWEEN 80 AND 95;	Find students scoring within a specified range.
School	SELECT * FROM Student WHERE Class='XI' AND Marks > 90;	Find Class XI toppers.
Hospital	SELECT * FROM Patient WHERE Blood_Group='O+' OR Blood_Group='A+';	Retrieve patients with either blood group.
Library	SELECT * FROM Book WHERE NOT Category='Reference';	Display books except the Reference category.
College	SELECT * FROM Student WHERE Email IS NULL;	Find students whose email addresses are missing.
E-Commerce	SELECT * FROM Customer WHERE Mobile_No IS NOT NULL;	Retrieve customers with registered mobile numbers.

---

Solved Example 4

Display students whose marks are between 75 and 90.

Solution:

SELECT *
FROM Student
WHERE Marks BETWEEN 75 AND 90;

---

Solved Example 5

Display the names of students who belong to Class XII and have marks greater than or equal to 90.

Solution:

SELECT Name
FROM Student
WHERE Class = 'XII'
AND Marks >= 90;

---

Solved Example 6

Display students who belong to Class XI or have marks less than 80.

Solution:

SELECT *
FROM Student
WHERE Class = 'XI'
OR Marks < 80;

---

Solved Example 7

Display students whose email address is available.

Solution:

SELECT Name, Email
FROM Student
WHERE Email IS NOT NULL;

---

Solved Example 8

Display students who do not belong to Class XI.

Solution:

SELECT *
FROM Student
WHERE NOT Class = 'XI';

---

Competency-Based Questions

1. A school wants to prepare a merit list of students scoring between 85 and 100 marks. Write the SQL query.
2. Write an SQL query to display students of Class XI who have scored more than 90 marks.
3. A college wants to find students who have not submitted their email addresses. Which SQL query should be used?
4. Differentiate between the AND and OR operators with suitable examples.
5. Explain why IS NULL is used instead of = NULL in SQL.

---

Multiple Choice Questions

1. The BETWEEN operator is used to:
   • (a) Delete records
   • (b) Retrieve values within a specified range
   • (c) Update records
   • (d) Create tables
2. Which operator requires all conditions to be true?
   • (a) OR
   • (b) AND
   • (c) NOT
   • (d) BETWEEN
3. Which operator returns records when at least one condition is true?
   • (a) AND
   • (b) OR
   • (c) NOT
   • (d) IS NULL
4. Which operator is used to exclude matching records?
   • (a) BETWEEN
   • (b) AND
   • (c) NOT
   • (d) IS NULL
5. Which clause is used to find missing values?
   • (a) = NULL
   • (b) IS NULL
   • (c) NULL =
   • (d) EMPTY
6. Which clause retrieves records having values present?
   • (a) IS EMPTY
   • (b) IS NOT NULL
   • (c) NOT EMPTY
   • (d) != NULL
7. The BETWEEN operator includes:
   • (a) Only the first value
   • (b) Only the last value
   • (c) Both boundary values
   • (d) Neither boundary value
8. Which query displays students whose email is missing?
   • (a) SELECT * FROM Student WHERE Email = NULL;
   • (b) SELECT * FROM Student WHERE Email IS NULL;
   • (c) SELECT * FROM Student WHERE Email EMPTY;
   • (d) SELECT * FROM Student WHERE NULL = Email;
9. Which operator is commonly used to combine multiple conditions?
   • (a) AND
   • (b) CREATE
   • (c) ALTER
   • (d) DROP
10. Which SQL query displays students whose marks are between 80 and 90?
• (a) SELECT * FROM Student WHERE Marks BETWEEN 80 AND 90;
• (b) SELECT * FROM Student WHERE Marks = 80 TO 90;
• (c) SELECT * FROM Student WHERE Marks IN 80-90;
• (d) SELECT * FROM Student WHERE Marks RANGE 80,90;

---

Quick Revision

Operator	Purpose
BETWEEN	Selects values within a specified range (inclusive).
AND	Returns records only when all conditions are true.
OR	Returns records when at least one condition is true.
NOT	Excludes records matching the specified condition.
IS NULL	Finds records with missing values.
IS NOT NULL	Finds records containing values.

---

Important Points to Remember

• The BETWEEN operator includes both the starting and ending values.
• Use AND when all conditions must be satisfied.
• Use OR when any one condition is sufficient.
• The NOT operator excludes records that satisfy a condition.
• Never use = NULL or != NULL in SQL.
• Always use IS NULL and IS NOT NULL for NULL values.
• NULL represents missing or unknown data, not zero or an empty string.
• Advanced WHERE operators make SQL queries more precise and efficient.

---

CBSE Exam Tips

• Memorize the syntax of BETWEEN, AND, OR, NOT, IS NULL, and IS NOT NULL.
• Practice writing SQL queries based on real-life scenarios.
• Remember that BETWEEN includes both boundary values.
• Do not confuse AND with OR; understand their logical difference.
• Always use IS NULL instead of = NULL in SQL.
• Read competency-based questions carefully before selecting the appropriate operator.

---

Summary

The advanced WHERE clause provides powerful techniques for filtering data in MySQL. The BETWEEN operator retrieves values within a range, AND and OR combine multiple conditions, NOT excludes unwanted records, while IS NULL and IS NOT NULL are used to identify missing or available values. These operators are essential for writing efficient SQL queries and are frequently used in real-world database applications as well as CBSE Class 11 Informatics Practices examinations.