Data Filtering

Filtering Your Data

Most of the time, you don't need every single row from a table. You're usually looking for something specific, like customers from a certain city or products above a certain price. Filtering is how you narrow down your results to get just the data you need.

In SQL, the tool for this job is the WHERE clause. It acts like a filter for your rows, only letting through the ones that match a condition you set. You add it right after the FROM clause in your query.

SELECT column1, column2
FROM table_name
WHERE condition;

Think of it this way: SELECT chooses the columns (the vertical slice), and WHERE chooses the rows (the horizontal slice).

Making Comparisons

The heart of the WHERE clause is its condition, which almost always involves a comparison. You’re telling the database to compare a column's value in each row to something else. If the comparison is true, the row is included in your results.

Let’s imagine we have a small Employees table.

EmployeeID	FirstName	Department	Salary
1	Alice	Sales	70000
2	Bob	IT	85000
3	Charlie	Sales	62000
4	Diana	IT	94000

To find all employees in the Sales department, we use the equals operator (=).

SELECT FirstName, Department, Salary
FROM Employees
WHERE Department = 'Sales';

This query would return the rows for Alice and Charlie. Notice that text values like 'Sales' must be wrapped in single quotes. Numbers, on the other hand, do not need quotes.

Here are the standard comparison operators you can use:

Operator	Meaning	Example
`=`	Equal to	`Department = 'IT'`
`!=` or `<>`	Not equal to	`Department != 'IT'`
`>`	Greater than	`Salary > 80000`
`<`	Less than	`Salary < 90000`
`>=`	Greater than or equal to	`Salary >= 85000`
`<=`	Less than or equal to	`Salary <= 70000`

For example, if we want to find employees who earn more than $80,000, our query would look like this:

SELECT FirstName, Salary
FROM Employees
WHERE Salary > 80000;

Combining Conditions

Sometimes a single condition isn't enough. You might need to find employees who work in IT and earn more than $90,000. For this, you can use logical operators like AND, OR, and NOT to combine multiple comparisons.

The AND operator requires all conditions to be true for a row to be returned.

SELECT FirstName, Department, Salary
FROM Employees
WHERE Department = 'IT' AND Salary > 90000;

This query would only return the row for Diana, since she is the only employee who meets both criteria.

The OR operator is less strict. It returns a row if at least one of the conditions is true. Let's find employees who are in the 'Sales' department OR earn more than $90,000.

SELECT FirstName, Department, Salary
FROM Employees
WHERE Department = 'Sales' OR Salary > 90000;

This would return Alice, Charlie, and Diana. Alice and Charlie match the first condition (Department = 'Sales'), and Diana matches the second (Salary > 90000).

You can also use NOT to reverse a condition. It’s often used with other operators, which we’ll see next.

Matching Patterns

What if you don't know the exact value you're looking for? Maybe you need to find all employees whose first name starts with 'A', or find a product name that contains the word 'classic'. For this kind of partial matching, SQL provides the LIKE operator.

LIKE is used with wildcards, which are special characters that can stand in for other characters.

The two main wildcards are: • %: Represents zero, one, or multiple characters. • _: Represents a single character.

To find all employees whose first name starts with the letter 'A', you would use the % wildcard.

SELECT FirstName
FROM Employees
WHERE FirstName LIKE 'A%';

This would return 'Alice'. The pattern 'A%' means "starts with A, followed by any number of characters."

If you wanted to find names that end with 'b', you would use '%b'. This would return 'Bob'.

The single-character wildcard, _, is useful for when you know the length of the word. For example, to find all three-letter names that start with 'B', you could write:

SELECT FirstName
FROM Employees
WHERE FirstName LIKE 'B__';

This would return 'Bob', as B__ matches a 'B' followed by exactly two characters.

Filtering is a fundamental skill in SQL. By mastering the WHERE clause with its various operators, you can precisely target the data you need for any analysis.

Advanced SQL for Data Mastery