Filtering Data in SQL:
A Beginner's Guide with Examples

What is Filtering in SQL?

Filtering in SQL means selecting specific rows from a table based on certain conditions. It’s like using a sieve to separate the grains of rice from the husk—only the data that meets your criteria will pass through.

SQL provides several ways to filter data, including:

• Basic Filtering with WHERE
• Filtering with Logical Operators (AND, OR, NOT)
• Filtering with Comparison Operators (=, >, <, >=, <=, !=)
• Filtering with IN and NOT IN
• Filtering with BETWEEN
• Filtering with LIKE for Pattern Matching
• Filtering with IS NULL and IS NOT NULL
• Filtering with HAVING

1. Basic Filtering with WHERE

The Problem:

You have a table of Bangladeshi citizens, and you want to find all the people who live in Dhaka.

The Solution:

Use the WHERE clause to filter rows based on a condition.

Example:

Suppose you have a table called Citizens:

ID	Name	City	Age
1	Ayesha Rahman	Dhaka	25
2	Rafiqul Islam	Chittagong	30
3	Fatima Akter	Dhaka	22
4	Karim Ahmed	Sylhet	35

To find all citizens living in Dhaka:

SELECT *
FROM Citizens
WHERE City = 'Dhaka';

Result:

ID	Name	City	Age
1	Ayesha Rahman	Dhaka	25
3	Fatima Akter	Dhaka	22

2. Filtering with Logical Operators (AND, OR, NOT)

The Problem:

You want to find all citizens who live in Dhaka and are older than 23.

The Solution:

Use the AND operator to combine multiple conditions.

Example:

SELECT *
FROM Citizens
WHERE City = 'Dhaka' AND Age > 23;

Result:

ID	Name	City	Age
1	Ayesha Rahman	Dhaka	25

The Problem:

You want to find all citizens who live in Dhaka or Chittagong.

The Solution:

Use the OR operator to filter rows that meet either condition.

Example:

SELECT *
FROM Citizens
WHERE City = 'Dhaka' OR City = 'Chittagong';

Result:

ID	Name	City	Age
1	Ayesha Rahman	Dhaka	25
2	Rafiqul Islam	Chittagong	30
3	Fatima Akter	Dhaka	22

The Problem:

You want to find all citizens who do not live in Sylhet.

The Solution:

Use the NOT operator to exclude rows that meet a condition.

Example:

SELECT *
FROM Citizens
WHERE NOT City = 'Sylhet';

Result:

ID	Name	City	Age
1	Ayesha Rahman	Dhaka	25
2	Rafiqul Islam	Chittagong	30
3	Fatima Akter	Dhaka	22

3. Filtering with Comparison Operators (=, >, <, >=, <=, !=)

The Problem:

You want to find all citizens who are older than 30.

The Solution:

Use the > operator to filter rows where the age is greater than 30.

Example:

SELECT *
FROM Citizens
WHERE Age > 30;

Result:

ID	Name	City	Age
4	Karim Ahmed	Sylhet	35

The Problem:

You want to find all citizens who are 30 years old or younger.

The Solution:

Use the <= operator to filter rows where the age is less than or equal to 30.

Example:

SELECT *
FROM Citizens
WHERE Age <= 30;

Result:

ID	Name	City	Age
1	Ayesha Rahman	Dhaka	25
2	Rafiqul Islam	Chittagong	30
3	Fatima Akter	Dhaka	22

4. Filtering with IN and NOT IN

The Problem:

You want to find all citizens who live in Dhaka or Chittagong.

The Solution:

Use the IN operator to filter rows where the city is in a list of values.

Example:

SELECT *
FROM Citizens
WHERE City IN ('Dhaka', 'Chittagong');

Result:

ID	Name	City	Age
1	Ayesha Rahman	Dhaka	25
2	Rafiqul Islam	Chittagong	30
3	Fatima Akter	Dhaka	22

The Problem:

You want to find all citizens who do not live in Dhaka or Chittagong.

The Solution:

Use the NOT IN operator to exclude rows where the city is in a list of values.

Example:

SELECT *
FROM Citizens
WHERE City NOT IN ('Dhaka', 'Chittagong');

Result:

ID	Name	City	Age
4	Karim Ahmed	Sylhet	35

5. Filtering with BETWEEN

The Problem:

You want to find all citizens who are between 25 and 35 years old.

The Solution:

Use the BETWEEN operator to filter rows where the age is within a range.

Example:

SELECT *
FROM Citizens
WHERE Age BETWEEN 25 AND 35;

Result:

ID	Name	City	Age
1	Ayesha Rahman	Dhaka	25
2	Rafiqul Islam	Chittagong	30
4	Karim Ahmed	Sylhet	35

6. Filtering with LIKE for Pattern Matching

The Problem:

You want to find all citizens whose names start with "A".

The Solution:

Use the LIKE operator with the wildcard % to match patterns.

Example:

SELECT *
FROM Citizens
WHERE Name LIKE 'A%';

Result:

ID	Name	City	Age
1	Ayesha Rahman	Dhaka	25

The Problem:

You want to find all citizens whose names contain "Ahmed".

The Solution:

Use the LIKE operator with % on both sides of the pattern.

Example:

SELECT *
FROM Citizens
WHERE Name LIKE '%Ahmed%';

Result:

ID	Name	City	Age
4	Karim Ahmed	Sylhet	35

7. Filtering with IS NULL and IS NOT NULL

The Problem:

You have a table of citizens, and some rows have missing city information. You want to find all citizens whose city is not recorded.

The Solution:

Use the IS NULL operator to filter rows where the city is missing.

Example:

Suppose the Citizens table has a missing city for one record:

ID	Name	City	Age
1	Ayesha Rahman	Dhaka	25
2	Rafiqul Islam	Chittagong	30
3	Fatima Akter	NULL	22
4	Karim Ahmed	Sylhet	35

To find citizens with missing city information:

SELECT *
FROM Citizens
WHERE City IS NULL;

Result:

ID	Name	City	Age
3	Fatima Akter	NULL	22

The Problem:

You want to find all citizens whose city information is recorded.

The Solution:

Use the IS NOT NULL operator to filter rows where the city is not missing.

Example:

SELECT *
FROM Citizens
WHERE City IS NOT NULL;

Result:

ID	Name	City	Age
1	Ayesha Rahman	Dhaka	25
2	Rafiqul Islam	Chittagong	30
4	Karim Ahmed	Sylhet	35

8. Filtering with HAVING

The Problem:

You want to find cities with more than one citizen.

The Solution:

Use the HAVING clause to filter groups of rows after aggregation.

Example:

Suppose you want to find cities with more than one citizen:

SELECT City, COUNT(*) AS NumberOfCitizens
FROM Citizens
GROUP BY City
HAVING COUNT(*) > 1;

Result:

City	NumberOfCitizens
Dhaka	2

The Problem:

You want to find cities where the average age of citizens is greater than 25.

The Solution:

Use the HAVING clause to filter groups based on aggregate functions.

Example:

SELECT City, AVG(Age) AS AverageAge
FROM Citizens
GROUP BY City
HAVING AVG(Age) > 25;

Result:

City	AverageAge
Chittagong	30
Sylhet	35

9. WHERE vs HAVING: Key Differences

When to Use WHERE:

• Purpose: Filters individual rows before grouping or aggregation.
• Use Case: Use WHERE when you want to filter rows based on specific conditions, such as finding all citizens in Dhaka or those older than 30.
• Example:

  SELECT *
  FROM Citizens
  WHERE City = 'Dhaka';

When to Use HAVING:

• Purpose: Filters groups of rows after aggregation.
• Use Case: Use HAVING when you want to filter groups based on aggregate functions, such as finding cities with more than one citizen or where the average age is greater than 25.
• Example:

  SELECT City, COUNT(*) AS NumberOfCitizens
  FROM Citizens
  GROUP BY City
  HAVING COUNT(*) > 1;

Key Differences:

Feature	WHERE	HAVING
Applies to	Individual rows	Groups of rows (after aggregation)
Used with	Non-aggregated columns	Aggregated columns (e.g., COUNT, SUM, AVG)
Execution	Filters rows before grouping	Filters groups after grouping

Conclusion

Whether you’re searching for people in Dhaka, finding names that start with "A", excluding missing data, or filtering groups with HAVING, SQL provides powerful tools to get the job done. By mastering these filtering techniques, you’ll be able to extract meaningful insights from your data.