Filtering Data with WHERE Clause

Welcome to the next step in your SQL journey!

In the last chapter, we learned how to retrieve data using the SELECT statement. But what if you want only specific records —like students who scored more than 90 or are enrolled in a specific course?

That’s where the powerful WHERE clause comes in. It allows you to filter the data based on conditions.

🔍 What is the WHERE Clause?

The WHERE clause is used to filter rows that meet a specific condition. It is used with statements like:

• SELECT
• UPDATE
• DELETE

📌 Only rows that match the condition will be affected or returned.

✅ Basic Syntax

 SELECT column1, column2 FROM table_name WHERE condition ;  

🧪 Our Example Table: students

We’ll continue using the same students table:

 CREATE TABLE students 
( id INT , 
name VARCHAR ( 50 ), 
age INT , 
course VARCHAR ( 30 ), 
marks DECIMAL ( 5 , 2 ) );  

Sample data:

id	name	age	course	marks
1	Rahul Sharma	21	SQL	85.50
2	Anjali Mehta	22	SQL	91.75
3	Vikas Gupta	23	SQL	88.00
4	Riya Jain	20	Python	92.50
5	Aman Verma	24	SQL	76.00

🎯 1. Filtering with Numeric Conditions

 SELECT * FROM students WHERE marks >90 ;  

🔎 Result:

name	marks
Anjali Mehta	91.75
Riya Jain	92.50

Other operators:

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

📘 2. Filtering with Text (String) Conditions

 SELECT * FROM students WHERE course = 'SQL' ;  

You must use quotes for string values.

🧠 3. Filtering with Multiple Conditions ( AND , OR )

 SELECT * FROM students WHERE course = 'SQL' AND marks >85 ;  

✅ Both conditions must be true.

 SELECT * FROM students WHERE age <22 marks="" or="">90 ;  

✅ Either condition can be true.

🧲 4. Using NOT for Opposite Conditions

 SELECT * FROM students WHERE NOT course = 'SQL' ;  

This will return students not in the SQL course .

🎯 5. Filtering with BETWEEN

 SELECT * FROM students WHERE marks BETWEEN 85 AND 90 ;  

Includes boundary values too (i.e., 85 and 90).

🔢 6. Filtering with IN (Multiple Values)

 SELECT * FROM students WHERE course IN ( 'SQL' , 'Python' );  

This is cleaner than multiple OR statements.

❌ 7. Filtering NULL Values with IS NULL or IS NOT NULL

 SELECT * FROM students WHERE marks IS NOT NULL ;  

Use this when you want to include/exclude empty values.

🔠 8. Filtering with LIKE (Pattern Matching)

Used for text search:

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

This finds names that start with A .

Common LIKE patterns:

Pattern	Meaning
'A%'	Starts with A
'%a'	Ends with a
'%an%'	Contains "an" anywhere
'_'	Single character wildcard

🧪 Real Life Queries for Practice

Try writing and running the following queries:

1. List all students who are enrolled in the "SQL" course and scored more than 80.
2. Find students whose names end with "a".
3. Select students aged between 21 and 23 .
4. Get all students who do not belong to the SQL course .

🚧 Common Mistakes to Avoid

Mistake	Fix
WHERE age = 22.0	Use correct data type
WHERE name = Rahul	Should be 'Rahul' (string in quotes)
WHERE marks = NULL	Use IS NULL , not =

📌 Summary Table

Keyword	Use
WHERE	Filters data based on a condition
AND / OR	Combine multiple conditions
BETWEEN	Check within a range
IN	Check for multiple matches
LIKE	Pattern matching in strings
IS NULL	Check for missing values

💬 Final Thought

The WHERE clause is your main tool for precision when working with databases. Whether you're retrieving, updating, or deleting — always make your WHERE condition specific to avoid mistakes.