Excerpt: Learn how to filter data in MySQL using WHERE, LIKE, and other operators to refine query results effectively and efficiently.

Filtering data is a fundamental part of querying databases. In MySQL, you can use the WHERE clause, LIKE operator, and other comparison and logical operators to narrow down results based on specific conditions. This article provides an overview of these filtering techniques with examples.

1. The WHERE Clause

The WHERE clause is used to specify conditions for filtering rows in a table.

Syntax:

SELECT column1, column2 FROM table_name WHERE condition;
    

Example:

SELECT * FROM users WHERE age > 30;
    

This query retrieves all users whose age is greater than 30.

2. The LIKE Operator

The LIKE operator is used for pattern matching in string fields.

Syntax:

SELECT column1 FROM table_name WHERE column1 LIKE pattern;
    

Wildcard Characters:

• %: Represents zero or more characters.
• _: Represents a single character.

Example:

SELECT name FROM users WHERE name LIKE 'A%';
    

This query retrieves all users whose names start with the letter “A”.

3. Using Comparison Operators

Comparison operators allow you to filter data based on specific criteria.

• =: Equal to
• != or <>: Not equal to
• >: Greater than
• <: Less than
• >=: Greater than or equal to
• <=: Less than or equal to

Example:

SELECT * FROM products WHERE price >= 100;
    

4. Using Logical Operators

Logical operators allow you to combine multiple conditions.

• AND: All conditions must be true.
• OR: At least one condition must be true.
• NOT: Negates a condition.

Example:

SELECT * FROM users WHERE age > 25 AND city = 'New York';
    

This query retrieves users older than 25 who live in New York.

5. IN and BETWEEN Operators

IN: Filters data by matching a list of values.

SELECT * FROM orders WHERE status IN ('Pending', 'Shipped');
    

BETWEEN: Filters data within a range of values.

SELECT * FROM sales WHERE date BETWEEN '2024-01-01' AND '2024-12-31';
    

6. IS NULL and IS NOT NULL

These operators filter rows with or without null values in a column.

Example:

SELECT * FROM employees WHERE manager_id IS NULL;
    

This query retrieves employees without a manager.

Conclusion

Filtering data in MySQL using WHERE, LIKE, and other operators is a powerful way to extract meaningful insights from your database. By combining these techniques, you can create complex queries to meet various data retrieval requirements.