SQL IN 与 BETWEEN

2019年5月15日

意见反馈

范围和成员条件

在查询数据时，有时需要查询在一定范围内的值，或者属于某个集合成员的值。

IN和BETWEEN运算符允许您定义范围或成员条件。

IN 运算符

IN操作符是逻辑操作符，用于检查某值是否存在于一组值中。其基本语法如下:

SELECT column_list FROM table_name
WHERE column_name IN (value1, value1,...);

在这里，column_list是要获取的字段，如名称、年龄、国家等。

我们来看一些例子。

假设我们的数据库中有一个employees表，其中包含以下记录:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |   5000 |       4 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      5 | Martin Blank | 2008-06-24 |   5600 |    NULL |
+--------+--------------+------------+--------+---------+

如在demo数据库中，没有这个employees数据表，可按如下sql语句创建：

CREATE TABLE IF NOT EXISTS employees (
    emp_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    salary INT,
    dept_id INT
);
INSERT INTO employees (emp_name, hire_date, salary, dept_id) VALUES ('Ethan Hunt', '2001-05-01', 5000, 4);
INSERT INTO employees (emp_name, hire_date, salary, dept_id) VALUES ('Tony Montana', '2002-07-15', 6500, 1);
INSERT INTO employees (emp_name, hire_date, salary, dept_id) VALUES ('Sarah Connor', '2005-10-18', 8000, 5);
INSERT INTO employees (emp_name, hire_date, salary, dept_id) VALUES ('Rick Deckard', '2007-01-03', 7200, 3);
INSERT INTO employees (emp_name, hire_date, salary) VALUES ('Martin Blank', '2008-06-24', 5600);

下面的SQL语句返回dept_id为1或3的员工。

示例

SELECT * FROM employees WHERE dept_id IN (1, 3);

执行查询后，您将得到如下结果集:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
+--------+--------------+------------+--------+---------+

类似地，可以使用NOT IN运算符，它与IN正好相反。下面的SQL语句将返回除dept_id是1或3之外的所有员工。

示例

SELECT * FROM employees
WHERE dept_id NOT IN (1, 3);

执行查询后，这一次您将得到如下结果集:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |   5000 |       4 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
+--------+--------------+------------+--------+---------+

BETWEEN 运算符

有时，需要选取字段值在一定范围内的行，这种条件在处理数值时很常见。

要执行这种查询，可以使用BETWEEN运算符。它是一个逻辑运算符，允许您指定字段值范围，如下所示:

SELECT column1_name, column2_name, columnN_name
FROM table_name
WHERE column_name BETWEEN min_value AND max_value;

定义数值范围

下面的SQL语句将从employees表中返回工资在7000和9000之间的员工。

示例

SELECT * FROM employees 
WHERE salary BETWEEN 7000 AND 9000;

执行之后，您将得到如下输出:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
+--------+--------------+------------+--------+---------+

定义日期范围

当使用带日期或时间值的BETWEEN运算符时，使用CAST()函数显式地将值转换为所需的数据类型，以获得最佳结果。例如，如果在与日期的比较中使用’2016-12-31’之类的字符串，请将该字符串转换为日期，如下所示:

下面的SQL语句查询了2006年1月1日(即“2006年01月01日”)至2016年12月31日(即“2016-12-31”)期间雇佣的所有员工:

示例

SELECT * FROM employees WHERE hire_date
BETWEEN CAST('2006-01-01' AS DATE) AND CAST('2016-12-31' AS DATE);

执行之后，您将得到如下输出:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      5 | Martin Blank | 2008-06-24 |   5600 |    NULL |
+--------+--------------+------------+--------+---------+

定义字符串范围

虽然日期和数字范围是最常见的，但是您也可以构建字符串范围条件。下面的SQL语句查询全部名字以字母“O”和“Z”之间的任意一个字母开头的员工:

示例

SELECT * FROM employees
WHERE emp_name BETWEEN 'O' AND 'Z';

执行之后，您将得到如下输出:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
+--------+--------------+------------+--------+---------+