How to Filter Data in MySQL Using the WHERE Clause

In database management, the ability to filter data properly is very important. The stored data is usually very large and comes from various sources and types. However, often we only need certain data according to our needs.

This filtering process helps improve work efficiency, reduce data access time, and make it easier to manage and analyze relevant data.

In MySQL, WHERE clauses are an important element in this process. This clause allows users to set certain conditions when retrieving data with SELECT statements so that only eligible data will be displayed.

What is the WHERE clause?

WHERE clauses are the parts of SQL statements that serve to filter data by specifying specific conditions or criteria. This clause is usually used in conjunction with SELECT statements, so only rows that meet the conditions will be taken from the table.

In other words, WHERE helps limit the data retrieved based on the desired conditions, whether it’s based on a specific value, a range of values, or other attributes that match the needs of the query.

In MySQL, WHERE can be used with various operators to set filtering criteria, such as =, <>, BETWEEN, LIKE, and others, so that users have flexibility in determining data relevant to their needs.

How to Use WHERE Clauses in SELECT Statements

The WHERE clause is placed after the FROM clause in the SELECT statement. The goal is to set the terms or criteria that must be met by the rows taken from the table.

Here is the basic syntax for the WHERE clause in a SELECT statement:

SELECT column_name1,  column_name2,  ...
FROM table_name
WHERE condition;

In this syntax:

• SELECT is used to select the columns you want to display.
• FROM indicates a table of data sources.
• WHERE specifies the conditions that must be met for the row to be displayed.

Suppose we have a table named products with columns prod_name and prod_price. If we want to display only products that cost (prod_price) 2.50, we can use a WHERE clause like this:

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;

Output Results:

+---------------+-------------+
| prod_name     | prod_price  |
+---------------+-------------+
| Carrots       |       2.50  |
| TNT (1 stick) |       2.50  |
+---------------+-------------+

In this example, the SELECT statement will only display rows from the products table where the prod_price column has a value of 2.50. So, only products that meet these criteria will appear in the query results.

Operator Klausa WHERE di MySQL

The WHERE clause in MySQL has various operators that help users filter the data as per certain criteria. Here are some of the operators that are frequently used in the WHERE clause:

1. Equality Operator (=)

The = operator is used to check if the value in the column is the same as the specified value. This is a basic way to filter data based on equity.

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;

This statement will take all products at exactly 2.50 prices.

2. Inequality Operator (<> and !=)

The <> and != operators are used to filter data that does not match the specified value. Both function the same, i.e. to check for inequality.

SELECT prod_name, prod_price
FROM products
WHERE prod_price <> 2.50;

This statement will show all products that are priced less than 2.50.

You can use <> or != as per your preference, as they both give the same result.

3. Larger and Smaller Operators (<, >, <=, >=)

This operator is used to filter data based on values that are greater or less than the specified value.

• <: Less than a certain value
• >: Greater than a certain value
• <=: Less than or equal to a certain value
• >=: Greater than or equal to a certain value

SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10;

This statement will display all products priced under 10.