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.
4. BETWEEN Operator
The BETWEEN operator is used to select data within a specific range of values, including upper and lower bound values. This is especially useful when we want to get data between two specific values.
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;This statement will take all products priced between 5 and 10, including the price of 5 and 10 itself.
5. IS NULL Operator
The IS NULL operator is used to check for columns that have no value or contain NULL values. The use of this operator is important for finding empty or unfilled data.
SELECT cust_name
FROM customers
WHERE cust_email IS NULL;This statement will display all the names of customers who don’t have an email address listed.
A NULL value is different from a value of 0 or an empty string; NULL indicates no data or has not been filled in at all.
Conclusion
The WHERE clause in SQL serves to filter the data by specifying certain conditions that must be met. It allows users to retrieve a piece of data from a large table.
By using operators such as equal to (=), not equal to (<> and !=), as well as comparison operators (<, >, <=, >=), users can perform various tests against the value. The BETWEEN operator is used to filter data within a specific range of values, while the IS NULL operator is used to check if there is an empty (NULL) value in the column.
The proper use of WHERE clauses can improve query performance and ensure that only relevant data is retrieved, making it easier to analyze and make decisions. Best practices include using appropriate operators and avoiding filtering at the application level to maintain system efficiency.