Filtering Data
SQL Basics Course
Topic 9: Filtering Data
Learn how to find exactly the records you need using SQL filtering conditions.
Learning Objectives
- Use the WHERE clause
- Apply comparison operators
- Use AND, OR and NOT
- Filter ranges using BETWEEN
- Use IN and NOT IN
- Search text using LIKE
- Handle NULL values
WHERE Clause
The WHERE clause filters records based on conditions.
SELECT * FROM students
WHERE city = 'Delhi';
Comparison Operators
| Operator | Description |
|---|---|
| = | Equal To |
| != or <> | Not Equal To |
| > | Greater Than |
| < | Less Than |
| >= | Greater Than or Equal |
| <= | Less Than or Equal |
SELECT * FROM employees
WHERE salary > 50000;
AND Operator
SELECT * FROM students
WHERE city='Delhi'
AND age > 18;
Both conditions must be true.
OR Operator
SELECT * FROM students
WHERE city='Delhi'
OR city='Mumbai';
At least one condition must be true.
NOT Operator
SELECT * FROM students
WHERE NOT city='Delhi';
Returns records that do not match the condition.
BETWEEN
SELECT * FROM employees
WHERE salary BETWEEN 30000 AND 60000;
Filters values within a range.
IN Operator
SELECT * FROM students
WHERE city IN ('Delhi','Mumbai','Pune');
Checks multiple values in a single condition.
NOT IN Operator
SELECT * FROM students
WHERE city NOT IN ('Delhi','Mumbai');
LIKE Operator
SELECT * FROM students
WHERE name LIKE 'R%';
Returns names starting with R.
Wildcards
| Wildcard | Description |
|---|---|
| % | Any number of characters |
| _ | Exactly one character |
LIKE 'A%'
LIKE '_a%'
IS NULL
SELECT * FROM students
WHERE email IS NULL;
Finds rows where the value is missing.
IS NOT NULL
SELECT * FROM students
WHERE email IS NOT NULL;
Real World Example
SELECT name, salary
FROM employees
WHERE department='IT'
AND salary > 50000;
This retrieves IT employees earning more than 50,000.
Best Practices
- Always test conditions carefully.
- Use parentheses in complex queries.
- Filter only necessary records.
- Use indexes for frequently filtered columns.
- Check NULL values separately.
Practice Exercises
- Find students from Delhi.
- Find employees earning more than 40,000.
- Find cities using IN.
- Search names beginning with S.
- Find records with NULL email values.
Quiz
1. Which clause filters records?
2. What does BETWEEN do?
3. What does LIKE search for?
4. Difference between AND and OR?
5. How do you check NULL values?
Summary
- WHERE filters records.
- Comparison operators evaluate conditions.
- AND, OR and NOT combine conditions.
- BETWEEN filters ranges.
- LIKE searches patterns.
- IS NULL handles missing values.