SQL WHERE Clause - Filtering Data

SQL WHERE Clause

Imagine walking into a school library and asking for *every* book. Not practical, right? Instead, you specify — “Books by R.K. Narayan” or “Books for Class 10.” SQL works the same way. The WHERE clause lets you filter records and get only what matters.

Syntax of WHERE Clause

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Sample Table – Students

We’ll use this familiar table for examples:

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10),
  age INT,
  city VARCHAR(30)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A', 15, 'Delhi'),
(2, 'Diya Iyer', '9B', 14, 'Chennai'),
(3, 'Rohit Menon', '10A', 15, 'Kochi'),
(4, 'Sneha Patil', '8C', 13, 'Pune'),
(5, 'Mehul Agarwal', '9B', 14, 'Delhi');

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

1. Simple WHERE Clause

Use case: A school administrator wants to retrieve a list of all students enrolled in class '9B' to prepare a class roster.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name and class columns from the students table where the class is exactly '9B'.

SELECT name, class FROM students
WHERE class = '9B';

Result: Result contains the names and class details of students who are enrolled in class '9B'.

Query Result

name	class
Diya Iyer	9B
Mehul Agarwal	9B

2. WHERE with Numeric Condition

Use case: A school administrator wants to retrieve a list of all students who are 15 years old to assign them to an age-specific program.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name and age columns from the students table where the age is equal to 15.

SELECT name, age FROM students
WHERE age = 15;

Result description: Result contains the names and ages of students who are exactly 15 years old.

Query Result

name	age
Aarav Sharma	15
Rohit Menon	15

3. Using AND with WHERE

Use case: A school administrator wants to get the names of students who are enrolled in class '9B' and reside in the city of Delhi for location-specific communication or reporting.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name column from the students table where the city is 'Delhi' and the class is '9B'.

SELECT name FROM students
WHERE city = 'Delhi' AND class = '9B';

Result description: Result contains the names of students who belong to class '9B' and live in Delhi.

Query Result

name
Mehul Agarwal

4. Using OR with WHERE

Use case: A school administrator wants to generate a list of students who are from the cities of Delhi or Pune for city-wise reporting or regional event planning.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name and city columns from the students table where the city is either 'Delhi' or 'Pune'.

SELECT name, city FROM students
WHERE city = 'Delhi' OR city = 'Pune';

Result description: Result contains the names and city information of students who reside in either Delhi or Pune.

Query Result

name	city
Aarav Sharma	Delhi
Mehul Agarwal	Delhi
Sneha Patil	Pune

5. Using NOT

Use case: A school administrator wants to get a list of students who are not from the city of Delhi to organize regional events for out-of-town students.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name and city columns from the students table where the city is not equal to 'Delhi'.

SELECT name, city FROM students
WHERE NOT city = 'Delhi';

Result description: Result contains the names and city information of all students who do not belong to Delhi.

Query Result

name	city
Diya Iyer	Chennai
Rohit Menon	Kochi
Sneha Patil	Pune

6. Using BETWEEN

Use case: A school administrator wants to retrieve a list of students who are aged between 14 and 15 to plan age-specific academic programs.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name and age columns from the students table where the age is between 14 and 15, inclusive.

SELECT name, age FROM students
WHERE age BETWEEN 14 AND 15;

Result description: Result contains the names and ages of students who are either 14 or 15 years old.

Query Result

name	age
Aarav Sharma	15
Diya Iyer	14
Rohit Menon	15
Mehul Agarwal	14

7. Using IN

Use case: A school administrator wants to identify students who are from the cities of Delhi or Chennai in order to organize city-specific events or communications.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name and city columns from the students table where the city is either 'Delhi' or 'Chennai'.

SELECT name, city FROM students
WHERE city IN ('Delhi', 'Chennai');

Result description: Result contains the names and city information of students who belong to Delhi or Chennai.

Query Result

name	city
Aarav Sharma	Delhi
Diya Iyer	Chennai
Mehul Agarwal	Delhi

8. Using LIKE for Pattern Matching

Use case: A school administrator wants to find all students whose names start with the letter 'S' for organizing name-based reports or communications.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name column from the students table where the name begins with the letter 'S', using the LIKE operator with the pattern 'S%'.

SELECT name FROM students
WHERE name LIKE 'S%';

Result description: Result contains the names of all students whose names start with the letter 'S'.

Query Result

name
Sneha Patil

9. WHERE with Comparison Operators

• = (equal)
• != or <> (not equal)
• < (less than)
• > (greater than)
• <=, >=

Example: Students younger than 14

Use case: A school administrator wants to retrieve a list of all students who are younger than 14 years old to identify students eligible for a junior program.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune
5	Mehul Agarwal	9B	14	Delhi

SQL query description: SQL query to select the name and age columns from the students table where the age is less than 14.

SELECT name, age FROM students
WHERE age < 14;

Result description: Result contains the names and ages of students whose age is less than 14.

Query Result

name	age
Sneha Patil	13

Best Practices

• Use quotes for string values, but not for numbers.
• Use parentheses to group conditions when mixing AND/OR.
• Preview with SELECT before applying updates or deletes.

Summary

The WHERE clause is your precision tool in SQL. Whether you’re isolating top scorers, filtering students by class, or narrowing records by location — WHERE helps you focus on what truly matters.