MariaDB Between Inclusive Both Endpoints

Published
Length
3 min read

Luis Felipe Dias

All posts

The mariadb between inclusive both endpoints operator filters rows within a set range. It matches values that equal the start boundary, the end boundary, or anything inside. Teams use this operator to pull reports across dates, prices, or user IDs. Still, broad database roles often let users query far wider ranges than needed. How the mariadb between inclusive both endpoints operator works In MariaDB, the range filter checks if a target value falls inside two limits. The engine treats the low

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

The mariadb between inclusive both endpoints operator filters rows within a set range. It matches values that equal the start boundary, the end boundary, or anything inside. Teams use this operator to pull reports across dates, prices, or user IDs. Still, broad database roles often let users query far wider ranges than needed.

How the mariadb between inclusive both endpoints operator works

In MariaDB, the range filter checks if a target value falls inside two limits. The engine treats the lower limit and the upper limit as part of the match. If a row matches the exact start value, MariaDB includes it in the result. If a row matches the exact end value, the engine includes that row too. Under the hood, the query planner translates this check into two comparison rules. It checks if the column is greater than or equal to the first boundary. At the same time, it checks if the column is less than or equal to the second boundary.

This logic applies to numbers, text strings, and date or time columns. For numbers, the engine compares the exact numeric values from lowest to highest. If you place the higher number first, the query returns an empty result set. The lower boundary must always come before the upper boundary in the query.

For text columns, the engine uses the character set and collation of the column. A case-insensitive collation will match both uppercase and lowercase letters at both endpoints. If the column uses a binary collation, the engine compares raw byte values instead. Trailing spaces can also change how text boundaries match during a range scan.

Handling date and timestamp boundaries in range queries

Date and timestamp columns cause the most confusion when teams run range queries. A plain date value without a time part defaults to midnight at the start of that day. Suppose an analyst runs a range check from the first day to the last day of a month. If the column stores full timestamps, the upper limit stops at midnight on the last day. Any event that happens later on that final day falls outside the range.

To capture the full final day, the upper boundary must include the very last second of that day. Teams also need to watch out for time zone shifts across sessions. Timestamp columns convert values from the session time zone to UTC for storage. Date and datetime columns do not perform this automatic time zone conversion. Mixing these types in a range check can shift the endpoints and drop valid rows.

Between Inclusive Both Endpoints: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Indexes play a major role in how fast these range queries run. If the filtered column has a B-tree index, MariaDB performs an index range scan. It jumps straight to the lower endpoint and reads forward until the upper endpoint. Without an index, the engine must read every single row in the table.

What native database roles solve and what they leave open

Native MariaDB grants control which users can log in and read specific tables. An admin can grant read rules on a table or a subset of columns. This setup stops unauthorized users from reading tables they should not touch. You can also set per-user limits on total queries or simultaneous database sessions.

Still, native grants only check permissions at the table or column level. Once a user has read permission on a table, they can run any range query. Native grants cannot force a user to query only a small date range. A developer fixing one record can still pull millions of rows across several years. Also, standard table grants do not hide sensitive fields inside those large result sets. If the range scan hits emails, phone numbers, or card numbers, the query returns them in plain text. Native database logs also require careful tuning to capture every single query without filling up disk space.

Check MySQL queries and results on the wire

MySQL grants control who logs in. They do not check single queries or mask sensitive columns once a session starts. With hoop.dev, you run an open-source proxy, a sidecar for your MySQL. It runs next to MySQL, RDS, or Aurora and reads the MySQL wire protocol.

Multi-statement batches and prepared queries are split and checked one by one by hoop.dev guardrails. When a rule refuses a query, MySQL returns a native error packet with your message. On the response path, hoop.dev data masking rewrites string values in returned rows that match your rules. Every query and decision is logged per resource. See the MIT-licensed project on GitHub.

Frequently asked questions

Does the MariaDB range operator include the start and end values?

Yes, the operator includes both the start value and the end value in results. A row that matches either boundary will appear in your final query output.

Why does my date range query miss records from the final day?

A date string without a time component defaults to midnight at the start of that day. Records with timestamps later on that final day are greater than the upper boundary.

What happens if the first boundary is larger than the second boundary?

MariaDB does not swap the boundaries for you when evaluating the range. The query will simply match zero rows and return an empty result set.

The query runs. The emails stay hidden.