How do you use the BETWEEN operator in SQL?

The BETWEEN operator in SQL is used to filter results within a specified range, inclusive of the boundary values. It works with numerical, date/time, and string data types, allowing you to check if a value falls between two endpoints. The basic syntax is WHERE column_name BETWEEN value1 AND value2. For example, SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31' retrieves orders placed in January 2023. The operator simplifies writing range-based conditions by replacing separate >= and <= comparisons.

Common use cases include filtering dates, numerical ranges, or alphabetical ranges. For numerical data, BETWEEN can select values like prices between $10 and $50. With dates, ensure the format matches your database’s expectations (e.g., YYYY-MM-DD). For strings, BETWEEN 'A' AND 'D' would include values starting with A, B, C, or D, based on alphabetical sorting. The operator can also be combined with AND/OR for more complex queries. For instance, combining a date range with a status condition: SELECT * FROM tasks WHERE due_date BETWEEN '2023-05-01' AND '2023-05-31' AND status = 'pending'. Performance-wise, BETWEEN can leverage indexes on the filtered column if available, making it efficient for large datasets.

Common mistakes include incorrectly ordering the range values (e.g., BETWEEN 50 AND 10 returns nothing) or misunderstanding inclusivity. For datetime fields, time components can cause unexpected results. For example, BETWEEN '2023-12-01' AND '2023-12-31' might exclude records from December 31 if the time is midnight. To avoid this, use BETWEEN '2023-12-01' AND '2023-12-31 23:59:59'. Additionally, BETWEEN is not suitable for non-continuous ranges (e.g., excluding weekends). In such cases, use explicit AND/OR logic. Always validate the data type compatibility—mixing dates and strings, for example, may lead to errors or silent misbehavior.