1. Introduction
The BETWEEN operator in MySQL is a convenient feature that allows you to retrieve data within a specific date range using a simple query. For example, it is useful when retrieving monthly sales data or searching for users whose registration dates fall within a particular period.
However, when using BETWEEN, you must pay attention to how data types such as DATE and DATETIME are handled, as well as potential performance issues. In this article, we will explain everything from basic usage to advanced techniques in detail.
2. Basics of the MySQL BETWEEN Operator
2.1 Basic Syntax of BETWEEN
The BETWEEN operator is used to retrieve values within a specified range. It has the following basic syntax:
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
This query retrieves data where order_date is from January 1, 2024 to January 31, 2024. An important point is that BETWEEN includes both the start date and the end date.
2.2 BETWEEN vs. Comparison Operators (>= AND <=)
To achieve the same result, you can also combine comparison operators such as >= and <=.
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date <= '2024-01-31';
Advantages of BETWEEN:
- Simple syntax with high readability
Advantages of >= AND <=:
- Allows more precise control of range conditions (e.g., excluding specific times)
For example, when using BETWEEN on a DATETIME column, the inclusion of time information may result in unintended data being retrieved. We will explain this in detail in the next section.
3. Important Considerations When Using BETWEEN
3.1 Handling Columns That Include Time Information
Using BETWEEN on a DATETIME column may produce unexpected results.
SELECT * FROM users
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
In this query, only data up to 2024-01-31 00:00:00 is retrieved, which means records created after midnight on January 31 are excluded.
3.2 Correct Way to Specify Date Ranges
To solve this issue, an effective approach is to set the end date to be less than the next day.
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';
By using >= and < in this way, you can reliably retrieve all records for the entire day of January 31. 
4. BETWEEN and Performance Optimization
4.1 Relationship Between Indexes and BETWEEN
The BETWEEN operator performs efficiently when appropriate indexes are configured. However, if you use the DATE() function, the index may not be used, so caution is required.
-- Index will not be used (not recommended)
SELECT * FROM users
WHERE DATE(created_at) BETWEEN '2024-01-01' AND '2024-01-31';
Recommended query:
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';
4.2 Query Optimization Using EXPLAIN
To verify performance, it is helpful to use the EXPLAIN command.
EXPLAIN SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
This allows you to check the indexes used and the execution plan.
5. Common Mistakes and How to Fix Them
5.1 Retrieving an Unintended Range with BETWEEN
Even when using BETWEEN, unintended data may be included or excluded if time information is not considered. As a best practice, combining >= and < is recommended.
5.2 Queries That Disable Indexes
Conditions using functions such as DATE() or CAST() may disable index usage. Whenever possible, it is best to rewrite the query to compare values directly.
6. Frequently Asked Questions (FAQ)
Q1: Does BETWEEN include both the start and end dates?
→ Yes, BETWEEN includes both boundaries of the specified range.
Q2: Should I use BETWEEN or >= AND <=?
→ Gunakan BETWEEN untuk kondisi rentang sederhana. Jika Anda perlu memperhitungkan informasi waktu, disarankan menggunakan >= AND <.
Q3: Apakah penggunaan BETWEEN dapat memperlambat query?
→ Jika Anda menggunakan fungsi seperti DATE() atau CAST(), indeks mungkin tidak digunakan. Disarankan menggunakan perbandingan langsung.
7. Contoh Kuery Praktis untuk Penggunaan Dunia Nyata
7.1 Mengambil Data untuk Bulan Tertentu
WHERE created_at BETWEEN '2024-02-01' AND '2024-02-28';
7.2 Mengambil Data Hari Ini
WHERE created_at BETWEEN CURDATE() AND CURDATE() + INTERVAL 1 DAY;
7.3 Mengambil Data dari 30 Hari Terakhir
WHERE created_at BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE();
8. Ringkasan
- Operator
BETWEENmemungkinkan spesifikasi rentang tanggal yang sederhana , namun diperlukan kehati-hatian ekstra saat menangani tipeDATETIME. BETWEENmenyertakan tanggal mulai dan akhir , sehingga definisi rentang yang tepat sangat penting.- Pemanfaatan indeks sangat penting untuk optimasi kinerja , dan penggunaan fungsi
DATE()sebaiknya dihindari. - Menggunakan
>= AND <memungkinkan spesifikasi rentang yang lebih dapat diandalkan .
Ini mengakhiri penjelasan kami tentang poin-poin penting saat menggunakan operator BETWEEN di MySQL. Kami harap ini membantu Anda dalam pengembangan dunia nyata!
