PostgreSQL 比较函数和操作符详解

概述

比较操作符和函数是 PostgreSQL 中最基础且使用频率最高的功能之一。它们用于比较数据值、过滤查询结果、建立查询条件等。掌握这些操作符对于编写高效的 SQL 查询至关重要。

基本比较操作符

操作符概览

PostgreSQL 提供了完整的比较操作符集合，适用于所有具有自然顺序的数据类型：

操作符	描述	返回类型	示例
`<`	小于	boolean	`5 < 10 → true`
`>`	大于	boolean	`10 > 5 → true`
`<=`	小于或等于	boolean	`5 <= 5 → true`
`>=`	大于或等于	boolean	`10 >= 5 → true`
`=`	等于	boolean	`5 = 5 → true`
`<>`	不等于（标准 SQL）	boolean	`5 <> 3 → true`
`!=`	不等于（别名）	boolean	`5 != 3 → true`

INFO

<> 是 SQL 标准的"不等于"操作符，而 != 是一个别名，在解析阶段会被转换为 <>。

实际应用示例

场景一：员工薪资查询系统

假设我们有一个员工表，需要进行各种薪资筛选：

sql

-- 创建员工表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

-- 插入测试数据
INSERT INTO employees (name, department, salary, hire_date) VALUES
    ('张三', '技术部', 8000.00, '2020-01-15'),
    ('李四', '销售部', 6500.00, '2019-03-20'),
    ('王五', '技术部', 12000.00, '2018-06-10'),
    ('赵六', '人事部', 7000.00, '2021-02-28'),
    ('钱七', '技术部', 9500.00, '2020-08-05');

查询高薪员工（薪资大于 8000）：

sql

SELECT name, department, salary
FROM employees
WHERE salary > 8000;

输出结果：

 name | department | salary
------+------------+---------
 王五  | 技术部      | 12000.00
 钱七  | 技术部      | 9500.00

分析过程：

salary > 8000 使用大于操作符筛选薪资超过 8000 的员工
返回两条记录，符合条件的员工信息

场景二：日期范围查询

查询 2020 年入职的员工：

sql

SELECT name, hire_date
FROM employees
WHERE hire_date >= '2020-01-01' AND hire_date <= '2020-12-31';

输出结果：

 name | hire_date
------+------------
 张三  | 2020-01-15
 钱七  | 2020-08-05

跨类型比较

PostgreSQL 支持相关数据类型之间的比较：

sql

-- 整数与大整数比较
SELECT 100 > 50::bigint AS result;  -- true

-- 数值与小数比较
SELECT 10 = 10.0 AS result;         -- true

-- 文本比较（按字典序）
SELECT 'apple' < 'banana' AS result; -- true

TIP

当比较不同但相关的数据类型时，PostgreSQL 会自动将"不太通用"的类型转换为"更通用"的类型。

比较谓词

BETWEEN 范围查询

BETWEEN 是最常用的范围查询谓词，包含端点值。

语法格式

sql

expression BETWEEN low_value AND high_value
-- 等价于
expression >= low_value AND expression <= high_value

实际应用示例

查询薪资在 7000 到 10000 之间的员工：

sql

SELECT name, salary
FROM employees
WHERE salary BETWEEN 7000 AND 10000;

输出结果：

 name | salary
------+--------
 张三  | 8000.00
 赵六  | 7000.00
 钱七  | 9500.00

使用 NOT BETWEEN：

sql

SELECT name, salary
FROM employees
WHERE salary NOT BETWEEN 7000 AND 10000;

输出结果：

 name | salary
------+---------
 李四  | 6500.00
 王五  | 12000.00

BETWEEN SYMMETRIC 特性

BETWEEN SYMMETRIC 会自动对端点值进行排序：

sql

-- 传统 BETWEEN（返回 false，因为 3 > 1）
SELECT 2 BETWEEN 3 AND 1 AS traditional;  -- false

-- SYMMETRIC BETWEEN（自动排序，等价于 2 BETWEEN 1 AND 3）
SELECT 2 BETWEEN SYMMETRIC 3 AND 1 AS symmetric;  -- true

WARNING

在 BETWEEN 语法中使用 AND 可能与逻辑运算符 AND 产生歧义。如果需要复杂的子表达式，请使用括号。

sql

-- 正确的复杂表达式写法
SELECT * FROM employees
WHERE salary BETWEEN (base_salary * 1.2) AND (base_salary * 1.8);

NULL 值处理

IS NULL 和 IS NOT NULL

NULL 值在数据库中表示"未知"或"缺失"的数据，需要特殊的处理方式。

sql

-- 添加包含 NULL 值的测试数据
INSERT INTO employees (name, department, salary, hire_date) VALUES
    ('孙八', '技术部', NULL, '2021-05-15');

-- 查询薪资未确定的员工
SELECT name, salary
FROM employees
WHERE salary IS NULL;

输出结果：

 name | salary
------+--------
 孙八  |

查询有薪资记录的员工：

sql

SELECT name, salary
FROM employees
WHERE salary IS NOT NULL;

DANGER

错误用法： 永远不要使用 expression = NULL

sql

-- 错误！这总是返回 NULL，而不是 true 或 false
SELECT name FROM employees WHERE salary = NULL;

-- 正确！使用 IS NULL
SELECT name FROM employees WHERE salary IS NULL;

IS DISTINCT FROM 和 IS NOT DISTINCT FROM

这两个谓词将 NULL 视为可比较的普通值：

sql

-- 创建测试表
CREATE TABLE test_nulls (
    id INT,
    value INT
);

INSERT INTO test_nulls VALUES
    (1, 10),
    (2, NULL),
    (3, 10),
    (4, NULL);

-- 标准等于比较（NULL 参与时返回 NULL）
SELECT id, value = 10 AS standard_eq
FROM test_nulls;

输出结果：

 id | standard_eq
----+-------------
  1 | t
  2 |
  3 | t
  4 |

使用 IS DISTINCT FROM：

sql

-- 将 NULL 视为可比较值
SELECT id, value IS DISTINCT FROM 10 AS distinct_from
FROM test_nulls;

输出结果：

 id | distinct_from
----+---------------
  1 | f
  2 | t
  3 | f
  4 | t

分析过程：

IS DISTINCT FROM 将 NULL 视为普通值
当 value 为 NULL 时，NULL IS DISTINCT FROM 10 返回 true
当 value 为 10 时，10 IS DISTINCT FROM 10 返回 false

布尔值测试

PostgreSQL 提供了专门的布尔值测试谓词：

sql

-- 创建布尔测试表
CREATE TABLE bool_test (
    id INT,
    flag BOOLEAN
);

INSERT INTO bool_test VALUES
    (1, TRUE),
    (2, FALSE),
    (3, NULL);

-- 测试各种布尔谓词
SELECT
    id,
    flag,
    flag IS TRUE AS is_true,
    flag IS FALSE AS is_false,
    flag IS UNKNOWN AS is_unknown
FROM bool_test;

输出结果：

 id | flag | is_true | is_false | is_unknown
----+------+---------+----------+------------
  1 | t    | t       | f        | f
  2 | f    | f       | t        | f
  3 |      | f       | f        | t

比较函数

PostgreSQL 提供了实用的比较相关函数：

num_nonnulls() 和 num_nulls()

这些函数在数据质量检查中非常有用：

sql

-- 统计每行中非 NULL 字段的数量
SELECT
    name,
    num_nonnulls(name, department, salary, hire_date) AS non_null_count,
    num_nulls(name, department, salary, hire_date) AS null_count
FROM employees;

输出结果：

 name | non_null_count | null_count
------+----------------+------------
 张三  | 4              | 0
 李四  | 4              | 0
 王五  | 4              | 0
 赵六  | 4              | 0
 钱七  | 4              | 0
 孙八  | 3              | 1

实际应用：数据完整性检查

sql

-- 查找数据不完整的记录
SELECT
    name,
    CASE
        WHEN num_nulls(name, department, salary, hire_date) > 0
        THEN '数据不完整'
        ELSE '数据完整'
    END AS data_status
FROM employees
WHERE num_nulls(name, department, salary, hire_date) > 0;

高级应用场景

复杂条件组合

在实际业务中，我们经常需要组合多个比较条件：

sql

-- 查找技术部门中薪资高于平均水平的资深员工
SELECT
    e1.name,
    e1.salary,
    e1.hire_date
FROM employees e1
WHERE e1.department = '技术部'
  AND e1.salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = '技术部'
      AND e2.salary IS NOT NULL
  )
  AND e1.hire_date < '2020-01-01';

范围查询优化

使用比较操作符进行范围查询时的最佳实践：

sql

-- 创建索引以优化范围查询
CREATE INDEX idx_employees_salary ON employees(salary);
CREATE INDEX idx_employees_hire_date ON employees(hire_date);

-- 高效的范围查询
EXPLAIN ANALYZE
SELECT name, salary
FROM employees
WHERE salary BETWEEN 7000 AND 15000
  AND hire_date >= '2019-01-01';

动态条件构建

在应用程序中构建动态查询条件：

sql

-- 模拟应用程序的动态查询需求
-- 假设用户可以选择薪资范围和部门
DO $$
DECLARE
    min_salary DECIMAL := 8000;
    max_salary DECIMAL := NULL;  -- NULL 表示不限制上限
    dept VARCHAR := '技术部';
BEGIN
    -- 构建动态查询
    IF max_salary IS NULL THEN
        RAISE NOTICE '查询条件：薪资 >= % AND 部门 = %', min_salary, dept;
    ELSE
        RAISE NOTICE '查询条件：薪资 BETWEEN % AND % AND 部门 = %',
                     min_salary, max_salary, dept;
    END IF;
END $$;

性能考虑

索引优化

sql

-- 为经常用于比较的列创建索引
CREATE INDEX idx_employees_salary_dept ON employees(department, salary);

-- 部分索引：只为特定条件创建索引
CREATE INDEX idx_high_salary ON employees(salary)
WHERE salary > 10000;

查询计划分析

使用 EXPLAIN 分析比较操作的性能：

sql

-- 分析范围查询的执行计划
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM employees
WHERE salary BETWEEN 8000 AND 12000;

最佳实践总结

TIP

比较操作符使用建议

优先使用标准 SQL 语法：使用 <> 而不是 !=
正确处理 NULL 值：使用 IS NULL 而不是 = NULL
选择合适的谓词：对于范围查询优先使用 BETWEEN
索引优化：为频繁比较的列创建合适的索引
类型一致性：尽量保证比较双方的数据类型一致

WARNING

常见陷阱

NULL 值比较陷阱：NULL = NULL 返回 NULL 而不是 true
BETWEEN 的包含性：BETWEEN 包含端点值
类型转换开销：避免不必要的跨类型比较
复杂表达式：在 BETWEEN 中使用复杂表达式时要加括号

通过掌握这些比较函数和操作符，你可以编写出更加高效和准确的 PostgreSQL 查询。在实际应用中，合理使用这些工具能够显著提升数据查询和处理的效率。

PostgreSQL 比较函数和操作符详解 ​

概述 ​

基本比较操作符 ​

操作符概览 ​

实际应用示例 ​

场景一：员工薪资查询系统 ​

场景二：日期范围查询 ​

跨类型比较 ​

比较谓词 ​

BETWEEN 范围查询 ​

语法格式 ​

实际应用示例 ​

BETWEEN SYMMETRIC 特性 ​

NULL 值处理 ​

IS NULL 和 IS NOT NULL ​

IS DISTINCT FROM 和 IS NOT DISTINCT FROM ​

布尔值测试 ​

比较函数 ​

num_nonnulls() 和 num_nulls() ​

实际应用：数据完整性检查 ​

高级应用场景 ​

复杂条件组合 ​

范围查询优化 ​

动态条件构建 ​

性能考虑 ​

索引优化 ​

查询计划分析 ​

最佳实践总结 ​

PostgreSQL 比较函数和操作符详解

概述

基本比较操作符

操作符概览

实际应用示例

场景一：员工薪资查询系统

场景二：日期范围查询

跨类型比较

比较谓词

BETWEEN 范围查询

语法格式

实际应用示例

BETWEEN SYMMETRIC 特性

NULL 值处理

IS NULL 和 IS NOT NULL

IS DISTINCT FROM 和 IS NOT DISTINCT FROM

布尔值测试

比较函数

num_nonnulls() 和 num_nulls()

实际应用：数据完整性检查

高级应用场景

复杂条件组合

范围查询优化

动态条件构建

性能考虑

索引优化

查询计划分析

最佳实践总结