SQL IN, BETWEEN and ORDER BY Clause (CBSE Class 12 Computer Science - 083)

SQL provides several clauses that make data retrieval more efficient. Instead of writing lengthy conditions, the IN clause allows matching multiple values, the BETWEEN clause selects records within a specified range, and the ORDER BY clause sorts the output in ascending or descending order.

Learning Objectives

• Use the IN clause to compare multiple values.
• Use the BETWEEN clause to retrieve values within a range.
• Sort records using ORDER BY.
• Combine these clauses with the WHERE clause.

---

Sample Student Table

StudentID	Name	Gender	Class	Section	City	Marks	DeptID
101	Riya	F	XII	A	Jaipur	95	1
102	Aman	M	XII	B	Delhi	91	2
103	Priya	F	XII	A	Jaipur	88	1
104	Rahul	M	XII	C	Mumbai	76	3
105	Neha	F	XII	B	Delhi	84	2
106	Arjun	M	XII	A	Jaipur	92	1
107	Simran	F	XII	C	Mumbai	81	3
108	Karan	M	XII	B	Delhi	69	2

---

IN Clause

The IN clause is used to check whether a value matches any value in a given list. It is a shorter and more readable alternative to multiple OR conditions.

---

Syntax

SELECT column_name
FROM table_name
WHERE column_name IN (value1, value2, value3);

---

Example 1 : Students from Jaipur or Delhi

SELECT Name, City
FROM Student
WHERE City IN ('Jaipur','Delhi');

Output

Name	City
Riya	Jaipur
Aman	Delhi
Priya	Jaipur
Neha	Delhi
Arjun	Jaipur
Karan	Delhi

---

Equivalent Query Using OR

SELECT Name, City
FROM Student
WHERE City='Jaipur'
OR City='Delhi';

---

Example 2 : Students of Section A or B

SELECT Name, Section
FROM Student
WHERE Section IN ('A','B');

---

Example 3 : Students Belonging to Departments 1 and 3

SELECT Name, DeptID
FROM Student
WHERE DeptID IN (1,3);

---

NOT IN Clause

The NOT IN clause selects records that do not match any value in the specified list.

Syntax

SELECT *
FROM Student
WHERE City NOT IN ('Delhi');

---

BETWEEN Clause

The BETWEEN clause selects records whose values lie within a specified range. The boundary values are included in the result.

---

Syntax

SELECT column_name
FROM table_name
WHERE column_name
BETWEEN value1 AND value2;

---

Example 1 : Students Scoring Between 80 and 90 Marks

SELECT Name, Marks
FROM Student
WHERE Marks BETWEEN 80 AND 90;

Output

Name	Marks
Priya	88
Neha	84
Simran	81

---

Example 2 : Student IDs Between 103 and 107

SELECT *
FROM Student
WHERE StudentID BETWEEN 103 AND 107;

---

Example 3 : Marks Between 70 and 95

SELECT Name, Marks
FROM Student
WHERE Marks BETWEEN 70 AND 95;

---

NOT BETWEEN

The NOT BETWEEN clause retrieves records outside the specified range.

Example

SELECT *
FROM Student
WHERE Marks NOT BETWEEN 80 AND 90;

---

ORDER BY Clause

The ORDER BY clause sorts the query result in ascending or descending order.

By default, SQL sorts data in ascending (ASC) order.

---

Syntax

SELECT column_name
FROM table_name
ORDER BY column_name;

---

Ascending Order (ASC)

Example

SELECT Name, Marks
FROM Student
ORDER BY Marks;

or

SELECT Name, Marks
FROM Student
ORDER BY Marks ASC;

Output

Name	Marks
Karan	69
Rahul	76
Simran	81
Neha	84
Priya	88
Aman	91
Arjun	92
Riya	95

---

Descending Order (DESC)

SELECT Name, Marks
FROM Student
ORDER BY Marks DESC;

---

Sorting Alphabetically

SELECT *
FROM Student
ORDER BY Name;

---

Sorting by Multiple Columns

SELECT *
FROM Student
ORDER BY City ASC,
Marks DESC;

The data is first sorted by City. Within the same city, records are sorted by Marks in descending order.

---

Combining WHERE and ORDER BY

SELECT Name, Marks
FROM Student
WHERE City='Jaipur'
ORDER BY Marks DESC;

---

Comparison of Clauses

Clause	Purpose
IN	Matches values from a list.
BETWEEN	Selects values within a range.
ORDER BY	Sorts query results.

---

Common Errors

• Using BETWEEN with the lower and upper values in the wrong order.
• Forgetting that BETWEEN includes both boundary values.
• Using DESC instead of ASC unintentionally.
• Writing text values without quotation marks.
• Using multiple OR conditions instead of the simpler IN clause.

---

Exam Tips

---

Frequently Asked Questions (FAQs)

1. What is the purpose of the IN clause?

The IN clause checks whether a value matches any value in a specified list.

2. Is BETWEEN inclusive?

Yes. BETWEEN includes both the starting and ending values.

3. Which clause is used to sort records?

The ORDER BY clause is used to sort query results.

4. What is the default sorting order in SQL?

The default sorting order is ascending (ASC).

5. Can ORDER BY be used with WHERE?

Yes. Records are first filtered using WHERE and then sorted using ORDER BY.

---

Summary

• The IN clause compares a value against multiple values in a list.
• The BETWEEN clause retrieves records within a specified range, including both boundary values.
• The ORDER BY clause sorts query results in ascending or descending order.
• IN simplifies multiple OR conditions.
• These clauses are frequently used together with the WHERE clause to retrieve meaningful data.