SQL BETWEEN 查询终极指南：从入门到精通
在结构化查询语言（SQL）的广阔世界中，筛选数据是开发者和数据分析师最常执行的任务之一。WHERE 子句为我们提供了强大的过滤能力，而在众多条件运算符中，BETWEEN 以其直观和高效的特性，在处理范围查询时脱颖而出。无论您是 SQL 新手还是经验丰富的开发者，深入理解 BETWEEN 运算符的 intricacies（复杂细节）、最佳实践和潜在陷阱，都将极大地提升您的数据查询效率和代码质量。
本文将作为一份详尽的指南，带您全面探索 BETWEEN 的世界。
1. BETWEEN 运算符的核心概念与语法
首先，让我们从最基础的部分开始。
BETWEEN 运算符用于选取介于两个值之间的数据范围内的值。这些值可以是数值、文本字符串或日期。
1.1 基本语法
BETWEEN 运算符的基本语法结构如下：
sql
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
关键点解析：
column_name: 您希望进行范围比较的列。value1: 范围的起始值（下限）。value2: 范围的结束值（上限）。
一个至关重要的特性是：BETWEEN 运算符是包含性的（Inclusive）。这意味着查询结果将包含等于 value1 和等于 value2 的记录，以及所有介于两者之间的值。
换句话说，column_name BETWEEN value1 AND value2 在逻辑上等同于：
sql
column_name >= value1 AND column_name <= value2
理解这一点是掌握 BETWEEN 的基石。
1.2 NOT BETWEEN 运算符
与 BETWEEN 相对的是 NOT BETWEEN，它用于选取指定范围之外的值。
语法如下：
sql
SELECT column_name(s)
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;
这在逻辑上等同于：
sql
column_name < value1 OR column_name > value2
NOT BETWEEN 对于筛选出“异常值”或不符合常规范围的数据非常有用。
2. BETWEEN 在不同数据类型上的应用
BETWEEN 的强大之处在于其通用性，它可以应用于多种数据类型。让我们通过一个贯穿全文的示例数据库来详细说明。
示例数据表：
假设我们有一个电子商务网站，包含以下两个核心表：
-
products表：product_id(INT, Primary Key)product_name(VARCHAR)price(DECIMAL)stock_quantity(INT)launch_date(DATETIME)
-
employees表：employee_id(INT, Primary Key)first_name(VARCHAR)last_name(VARCHAR)salary(DECIMAL)hire_date(DATE)
2.1 应用于数值类型 (Numeric)
这是 BETWEEN 最常见和最直观的用例。
场景：查询价格在 50 到 100 美元之间的所有产品。
sql
SELECT
product_id,
product_name,
price
FROM
products
WHERE
price BETWEEN 50.00 AND 100.00;
这个查询会返回所有 price 大于等于 50.00 且小于等于 100.00 的产品。如果某个产品的价格恰好是 50.00 或 100.00，它也会被包含在结果中。
场景：查找库存量不在 10 到 1000 之间的产品（可能是库存过低或过高）。
sql
SELECT
product_name,
stock_quantity
FROM
products
WHERE
stock_quantity NOT BETWEEN 10 AND 1000;
2.2 应用于日期和时间类型 (Date/Datetime)
BETWEEN 在处理日期范围时极为强大，是报表和数据分析的常用工具。
场景：查找在 2023 年第一季度入职的所有员工。
sql
SELECT
employee_id,
first_name,
last_name,
hire_date
FROM
employees
WHERE
hire_date BETWEEN '2023-01-01' AND '2023-03-31';
这个查询会返回所有 hire_date 在 2023-01-01 和 2023-03-31（包含这两天）之间的员工记录。
处理 DATETIME 或 TIMESTAMP 时的重要注意事项：
当列的数据类型包含时间部分（如 DATETIME 或 TIMESTAMP）时，BETWEEN 的行为需要格外小心。
场景：查询在 2023 年 11 月 27 日当天发布的所有产品。
一个常见的错误写法是：
sql
-- 这是一个有潜在问题的查询
SELECT product_name, launch_date
FROM products
WHERE launch_date BETWEEN '2023-11-27' AND '2023-11-27';
当 launch_date 是 DATETIME 类型时，'2023-11-27' 会被数据库解析为 '2023-11-27 00:00:00'。因此，上述查询实际上等同于：
sql
WHERE launch_date >= '2023-11-27 00:00:00' AND launch_date <= '2023-11-27 00:00:00'
这只会匹配到在当天零点零分零秒整发布的产品，而错过了当天其他时间发布的产品。
正确的处理方法：
方法一：明确指定时间的上限
sql
SELECT product_name, launch_date
FROM products
WHERE launch_date BETWEEN '2023-11-27 00:00:00' AND '2023-11-27 23:59:59';
这种方法在大多数情况下可行，但如果精度要求到毫秒，仍然可能存在遗漏。
方法二（推荐）：使用 >= 和 < 组合
这是处理日期时间范围查询的最佳实践，可以完全避免边界问题。
sql
SELECT product_name, launch_date
FROM products
WHERE launch_date >= '2023-11-27' AND launch_date < '2023-11-28';
这个查询的逻辑是“选择所有启动时间大于等于 11 月 27 日零点，并且严格小于 11 月 28 日零点的数据”，这精确地覆盖了 11 月 27 日的整整 24 小时，无论时间精度如何，都不会出错。
2.3 应用于字符串类型 (String/Text)
BETWEEN 也可以用于字符串，它会根据字符的字母顺序（或数据库的排序规则 Collation）进行比较。
场景：查找所有产品名称以 ‘A’, ‘B’, ‘C’ 开头的产品。
sql
SELECT
product_id,
product_name
FROM
products
WHERE
product_name BETWEEN 'A' AND 'Czzz'; -- 或者更精确地是 'D'
这个查询会返回所有 product_name 在字母排序上介于 ‘A’ 和 ‘Czzz’ 之间的产品。例如，’Apple’, ‘Banana’, ‘Camera’ 都会被匹配，但 ‘Desk’ 不会。使用 'Czzz' 是一个常见的技巧，用来包含所有以 ‘C’ 开头的单词。一个更严谨且易于理解的方法是：
sql
SELECT
product_id,
product_name
FROM
products
WHERE
product_name >= 'A' AND product_name < 'D';
这再次印证了 >= 和 < 组合在处理边界时的清晰性和准确性。
3. BETWEEN 的性能考量
性能是数据库查询中一个永恒的话题。BETWEEN 的性能如何？它能有效利用索引吗？
答案是：能！
当 BETWEEN 运算符作用于一个已建立索引的列时，数据库的查询优化器通常会选择索引范围扫描（Index Range Scan）。这是一种非常高效的数据访问方式。
工作原理：
- 数据库通过索引（通常是 B-Tree 索引）快速定位到范围的起始值 (
value1)。 - 然后，它沿着索引的叶子节点顺序扫描，直到遇到大于范围结束值 (
value2) 的第一个条目为止。 - 这个过程只访问了符合条件的数据块，避免了扫描整张表的巨大开销（全表扫描 Full Table Scan）。
示例：
假设 products 表的 price 列上有一个索引。执行以下查询：
sql
EXPLAIN SELECT product_name, price FROM products WHERE price BETWEEN 100 AND 200;
在大多数数据库（如 MySQL, PostgreSQL）的执行计划（Execution Plan）中，你会看到 type: range 或类似的描述，这表明查询正在高效地使用索引。
性能建议：
- 为范围查询的列创建索引：如果你经常在某个列上使用
BETWEEN（或其他范围操作符如>、<），为该列创建索引是提升性能最关键的一步。 - 注意数据类型匹配：确保
BETWEEN后面的value1和value2的数据类型与列的数据类型相匹配。例如，如果列是INT类型，但你提供了字符串'100'，数据库需要进行隐式类型转换，这可能会导致索引失效。
4. 常见的陷阱与最佳实践
虽然 BETWEEN 简单易用，但在实际使用中也存在一些容易被忽视的陷阱。
4.1 陷阱一：值的顺序颠倒
BETWEEN 语法要求 value1 必须小于或等于 value2。如果顺序颠倒，查询将永远不会返回任何结果。
sql
-- 错误示例：不会返回任何记录
SELECT * FROM products WHERE price BETWEEN 100 AND 50;
因为这个查询在逻辑上等同于 price >= 100 AND price <= 50，这个条件永远不可能为真。
最佳实践：始终确保 BETWEEN 的第一个值是范围的下限，第二个值是上限。
4.2 陷阱二：日期时间边界的模糊性
如前文所述，这是使用 BETWEEN 时最常见也最危险的陷阱。对 DATETIME 或 TIMESTAMP 列使用 BETWEEN 'yyyy-mm-dd' AND 'yyyy-mm-dd' 这样的形式，几乎总是错误的。
最佳实践：在处理包含时间的日期列时，强烈推荐使用 >= 和 < 的组合来定义一个“左闭右开”的区间，以确保逻辑的严谨性。
4.3 陷阱三：对 NULL 值的处理
如果 column_name 中包含 NULL 值，BETWEEN 和 NOT BETWEEN 都不会匹配到这些行。
sql
-- 以下两个查询都不会返回 price 为 NULL 的产品
SELECT * FROM products WHERE price BETWEEN 50 AND 100;
SELECT * FROM products WHERE price NOT BETWEEN 50 AND 100;
这是因为在 SQL 中，任何与 NULL 的比较（除了 IS NULL 和 IS NOT NULL）结果都是 UNKNOWN，WHERE 子句只会保留结果为 TRUE 的行。
最佳实践：如果业务逻辑需要处理 NULL 值，你需要显式地使用 IS NULL 或 IS NOT NULL 来进行判断。
例如，查询价格在 50 到 100 之间，或者价格未定义的产品：
sql
SELECT * FROM products
WHERE (price BETWEEN 50 AND 100) OR (price IS NULL);
5. BETWEEN 与其他子句的组合使用
BETWEEN 可以和 SQL 的其他子句灵活地结合，构建更复杂的查询。
5.1 结合 AND/OR
场景：查找价格在 20 到 40 美元之间，且库存大于 100 的产品。
sql
SELECT * FROM products
WHERE
(price BETWEEN 20.00 AND 40.00)
AND (stock_quantity > 100);
5.2 在 UPDATE 和 DELETE 中使用
BETWEEN 同样可以用于 UPDATE 或 DELETE 语句的 WHERE 子句中，以批量修改或删除特定范围的数据。
场景：为 2022 年入职的所有员工加薪 5%。
sql
UPDATE employees
SET salary = salary * 1.05
WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';
警告：在 UPDATE 或 DELETE 中使用 BETWEEN 时要格外小心。在执行前，强烈建议先用相同的 WHERE 子句编写一个 SELECT 查询，以确认将要被修改或删除的记录是否符合预期。
sql
-- 在执行 UPDATE 前，先运行这个 SELECT 来验证
SELECT * FROM employees
WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';
6. 结论：何时选择 BETWEEN？
通过以上的详细分析，我们可以总结出 BETWEEN 运算符的适用场景和指导原则。
选择使用 BETWEEN 的理由：
- 可读性强：
WHERE price BETWEEN 100 AND 200比WHERE price >= 100 AND price <= 200在语义上更清晰，代码更简洁，更能表达“在一个范围内”的业务意图。 - 包含性边界：当你的业务需求明确需要包含范围的两个端点时，
BETWEEN是完美的选择。这在处理整数 ID、薪资等级等方面非常常见。 - 高效性：当作用于索引列时，
BETWEEN具备出色的性能，能够执行高效的索引范围扫描。
谨慎使用或替换为 >= 和 < 的场景：
- 日期时间范围查询：为了避免由时间部分引起的边界问题，使用
column >= 'start_date' AND column < 'end_date_plus_one_day'的模式是更安全、更专业的选择。 - 需要“左闭右开”或“左开右闭”区间：
BETWEEN只能定义闭合区间 ([])。如果需要其他类型的区间，如[)或(]，则必须使用>、>=、<、<=的组合。
最终回顾：
SQL 的 BETWEEN 运算符是一个简单、直观且功能强大的工具，专门用于解决数据范围查询问题。掌握其核心的“包含性”原则，理解它在不同数据类型（尤其是日期时间）上的行为差异，并遵循为其列创建索引等性能最佳实践，你就能在日常的数据库操作中游刃有余。
记住，编写好的 SQL 不仅仅是为了得到正确的结果，更是为了写出清晰、高效、且无歧义的代码。BETWEEN 在这方面为我们提供了巨大的便利，但作为开发者，我们也有责任了解其背后的细节，以确保在各种复杂场景下都能做出最正确的选择。