PostgreSQL

Appearance

PostgreSQL 行和数组比较操作详解 ​

PostgreSQL 提供了多种专门的比较结构，用于在多组值之间进行高效的比较操作。这些操作符不涉及子查询，是 SQL 标准的重要组成部分，其中数组相关的操作是 PostgreSQL 的特有扩展。

概述 ​

行和数组比较操作主要包含以下几种形式：

INFO

重要说明所有这些表达式形式都返回布尔值（TRUE、FALSE 或 NULL），并且遵循 SQL 关于 NULL 值的布尔组合规则。

1. IN 操作符 ​

基本语法 ​

sql
expression IN (value1, value2, value3, ...)

工作原理 ​

IN 操作符检查左侧表达式是否等于右侧值列表中的任何一个值。这是多个 OR 条件的简写形式。

实际应用示例 ​

示例 1：基本用法 - 查询特定部门的员工

sql
-- 创建示例表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(30),
    salary INTEGER
);

-- 插入测试数据
INSERT INTO employees (name, department, salary) VALUES
('张三', '技术部', 8000),
('李四', '销售部', 6000),
('王五', '人事部', 7000),
('赵六', '技术部', 9000),
('钱七', '财务部', 7500);

-- 查询技术部和销售部的员工
SELECT name, department, salary
FROM employees
WHERE department IN ('技术部', '销售部');

输出结果：

 name | department | salary
------+------------+--------
 张三 | 技术部     |   8000
 李四 | 销售部     |   6000
 赵六 | 技术部     |   9000

分析过程：

  • 这个查询等效于：WHERE department = '技术部' OR department = '销售部'
  • PostgreSQL 逐一检查每个员工的部门是否匹配列表中的任何值
  • 匹配成功的记录被包含在结果集中

示例 2：数值范围查询

sql
-- 查询薪资在特定范围内的员工
SELECT name, salary
FROM employees
WHERE salary IN (6000, 8000, 9000);

输出结果：

 name | salary
------+--------
 张三 |   8000
 李四 |   6000
 赵六 |   9000

NULL 值处理 ​

WARNING

NULL 值注意事项当涉及 NULL 值时，IN 操作的行为可能不符合直觉：

  • 如果左侧表达式为 NULL，结果为 NULL
  • 如果右侧包含 NULL 且没有匹配值，结果为 NULL

示例 3：NULL 值处理演示

sql
-- 添加包含 NULL 的数据
INSERT INTO employees (name, department, salary) VALUES
('孙八', NULL, 8500);

-- 测试 NULL 值情况
SELECT
    NULL IN (1, 2, 3) AS case1,           -- 结果: NULL
    1 IN (NULL, 2, 3) AS case2,           -- 结果: TRUE
    4 IN (NULL, 2, 3) AS case3;           -- 结果: NULL

输出结果：

 case1 | case2 | case3
-------+-------+-------
       | t     |

2. NOT IN 操作符 ​

基本语法 ​

sql
expression NOT IN (value1, value2, value3, ...)

工作原理 ​

NOT IN 检查左侧表达式是否与右侧值列表中的所有值都不相等。

实际应用示例 ​

示例 1：排除特定部门

sql
-- 查询除技术部和销售部外的所有员工
SELECT name, department, salary
FROM employees
WHERE department NOT IN ('技术部', '销售部');

输出结果：

 name | department | salary
------+------------+--------
 王五 | 人事部     |   7000
 钱七 | 财务部     |   7500

分析过程：

  • 等效于：WHERE department <> '技术部' AND department <> '销售部'
  • 只有当部门既不是技术部也不是销售部时，记录才会被选中

NULL 值的陷阱 ​

DANGER

危险 NOT IN 与 NULL 值的组合经常导致意外结果。如果列表中包含 NULL，且没有匹配值，结果将是 NULL 而不是 TRUE。

示例 2：NOT IN 的 NULL 陷阱

sql
-- 创建包含 NULL 的测试
SELECT
    1 NOT IN (2, 3, NULL) AS result1,     -- 期望 TRUE，实际 NULL
    1 NOT IN (1, 2, NULL) AS result2,     -- 结果 FALSE
    1 NOT IN (2, 3, 4) AS result3;        -- 结果 TRUE

输出结果：

 result1 | result2 | result3
---------+---------+---------
         | f       | t

安全的替代方案：

sql
-- 使用 NOT EXISTS 避免 NULL 问题
SELECT name, department
FROM employees e1
WHERE NOT EXISTS (
    SELECT 1 FROM unnest(ARRAY['技术部', '销售部']) AS dept
    WHERE dept = e1.department
);

3. ANY/SOME 数组操作 ​

基本语法 ​

sql
expression operator ANY (array_expression)
expression operator SOME (array_expression)

INFO

提示 SOME 是 ANY 的同义词，两者功能完全相同。

工作原理 ​

ANY 操作符使用指定的操作符将左侧表达式与数组中的每个元素进行比较，如果任何一个比较返回 TRUE，则整个表达式返回 TRUE。

实际应用示例 ​

示例 1：数值比较

sql
-- 创建数组比较示例
SELECT
    5 > ANY(ARRAY[1, 3, 7, 9]) AS greater_than_any,    -- TRUE (5 > 1, 5 > 3)
    5 < ANY(ARRAY[1, 3, 7, 9]) AS less_than_any,       -- TRUE (5 < 7, 5 < 9)
    5 = ANY(ARRAY[1, 3, 7, 9]) AS equal_to_any,        -- FALSE
    5 = ANY(ARRAY[1, 3, 5, 9]) AS equal_to_any2;       -- TRUE

输出结果：

 greater_than_any | less_than_any | equal_to_any | equal_to_any2
------------------+---------------+--------------+---------------
 t                | t             | f            | t

示例 2：实际业务场景 - 查找薪资高于任意目标值的员工

sql
-- 查找薪资高于数组中任意值的员工
SELECT name, salary
FROM employees
WHERE salary > ANY(ARRAY[6500, 7500, 8500]);

输出结果：

 name | salary
------+--------
 张三 |   8000
 王五 |   7000
 赵六 |   9000
 钱七 |   7500
 孙八 |   8500

分析过程：

  • 这个查询找出薪资大于 6500、7500 或 8500 中任意一个值的员工
  • 由于 7000 > 6500，王五符合条件
  • 由于 7500 > 6500，钱七也符合条件

示例 3：字符串模式匹配

sql
-- 查找名字包含特定字符的员工
SELECT name
FROM employees
WHERE name LIKE ANY(ARRAY['%三%', '%四%', '%五%']);

输出结果：

 name
------
 张三
 李四
 王五

性能对比 ​

4. ALL 数组操作 ​

基本语法 ​

sql
expression operator ALL (array_expression)

工作原理 ​

ALL 操作符要求左侧表达式与数组中的所有元素比较都返回 TRUE，整个表达式才返回 TRUE。

实际应用示例 ​

示例 1：数值范围验证

sql
-- 验证薪资是否大于所有指定值
SELECT
    name,
    salary,
    salary > ALL(ARRAY[5000, 6000, 7000]) AS above_all_thresholds
FROM employees;

输出结果：

 name | salary | above_all_thresholds
------+--------+----------------------
 张三 |   8000 | t
 李四 |   6000 | f
 王五 |   7000 | f
 赵六 |   9000 | t
 钱七 |   7500 | t
 孙八 |   8500 | t

分析过程：

  • 张三薪资 8000 > 5000 AND 8000 > 6000 AND 8000 > 7000，全部为真
  • 李四薪资 6000 = 6000（不大于），条件为假
  • 王五薪资 7000 = 7000（不大于），条件为假

示例 2：质量控制场景

sql
-- 创建产品质量表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    quality_scores INTEGER[]
);

INSERT INTO products (name, quality_scores) VALUES
('产品A', ARRAY[85, 90, 88, 92]),
('产品B', ARRAY[75, 80, 85, 78]),
('产品C', ARRAY[95, 98, 96, 94]);

-- 查找所有质量分数都高于80分的产品
SELECT name, quality_scores
FROM products
WHERE 80 < ALL(quality_scores);

输出结果：

 name |  quality_scores
------+-------------------
 产品A | {85,90,88,92}
 产品C | {95,98,96,94}

示例 3：空数组的特殊行为

sql
-- 空数组的 ALL 操作返回 TRUE
SELECT
    5 > ALL(ARRAY[]::INTEGER[]) AS empty_array_result,    -- TRUE
    5 < ALL(ARRAY[]::INTEGER[]) AS empty_array_result2;   -- TRUE

输出结果：

 empty_array_result | empty_array_result2
--------------------+---------------------
 t                  | t

INFO

逻辑解释空数组的 ALL 操作返回 TRUE，这符合数学逻辑中"对于空集合，任何命题都是真"的原理。

5. 行构造器比较 ​

基本语法 ​

sql
row_constructor operator row_constructor

支持的操作符 ​

行构造器比较支持以下操作符：=、<>、<、<=、>、>=

实际应用示例 ​

示例 1：坐标点比较

sql
-- 创建坐标点表
CREATE TABLE points (
    id SERIAL PRIMARY KEY,
    name VARCHAR(20),
    x INTEGER,
    y INTEGER
);

INSERT INTO points (name, x, y) VALUES
('原点', 0, 0),
('点A', 1, 2),
('点B', 2, 1),
('点C', 1, 2);

-- 使用行构造器进行坐标比较
SELECT name, x, y
FROM points
WHERE (x, y) = (1, 2);

输出结果：

 name | x | y
------+---+---
 点A  | 1 | 2
 点C  | 1 | 2

示例 2：字典序比较

sql
-- 创建学生成绩表
CREATE TABLE student_grades (
    id SERIAL PRIMARY KEY,
    name VARCHAR(20),
    math_score INTEGER,
    english_score INTEGER
);

INSERT INTO student_grades (name, math_score, english_score) VALUES
('小明', 85, 90),
('小红', 90, 85),
('小刚', 85, 95),
('小丽', 90, 90);

-- 按成绩进行字典序比较
SELECT name, math_score, english_score
FROM student_grades
WHERE (math_score, english_score) > (85, 90)
ORDER BY math_score, english_score;

输出结果：

 name | math_score | english_score
------+------------+---------------
 小刚 |         85 |            95
 小红 |         90 |            85
 小丽 |         90 |            90

分析过程：

  1. 首先比较 math_score：如果不相等，以此决定结果
  2. 如果 math_score 相等，则比较 english_score
  3. (85, 95) > (85, 90) 为真，因为第二个元素 95 > 90
  4. (90, 85) > (85, 90) 为真，因为第一个元素 90 > 85

IS DISTINCT FROM 和 IS NOT DISTINCT FROM ​

这两个操作符专门处理包含 NULL 值的行比较。

示例 3：NULL 安全的行比较

sql
-- 创建包含 NULL 的测试数据
CREATE TABLE test_nulls (
    id SERIAL PRIMARY KEY,
    a INTEGER,
    b INTEGER
);

INSERT INTO test_nulls (a, b) VALUES
(1, 2),
(NULL, 2),
(1, NULL),
(NULL, NULL);

-- 标准比较 vs DISTINCT 比较
SELECT
    a, b,
    (a, b) = (1, 2) AS standard_equal,
    (a, b) IS NOT DISTINCT FROM (1, 2) AS distinct_equal,
    (a, b) IS DISTINCT FROM (1, 2) AS distinct_not_equal
FROM test_nulls;

输出结果：

 a | b | standard_equal | distinct_equal | distinct_not_equal
---+---+----------------+----------------+--------------------
 1 | 2 | t              | t              | f
   | 2 | f              | f              | t
 1 |   |                | f              | t
   |   |                | f              | t

TIP

使用建议当需要进行包含 NULL 值的行比较时，推荐使用 IS [NOT] DISTINCT FROM，因为它们总是返回明确的布尔值，不会返回 NULL。

6. 复合类型比较 ​

基本概念 ​

复合类型比较用于比较包含多个字段的复杂数据类型，如用户定义的类型或记录类型。

实际应用示例 ​

示例 1：地址类型比较

sql
-- 创建复合类型
CREATE TYPE address AS (
    street VARCHAR(50),
    city VARCHAR(30),
    postal_code VARCHAR(10)
);

-- 创建用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(30),
    home_address address,
    work_address address
);

-- 插入测试数据
INSERT INTO users (name, home_address, work_address) VALUES
('张三', ('中山路123号', '北京', '100001'), ('建国门大街456号', '北京', '100002')),
('李四', ('解放路789号', '上海', '200001'), ('南京路321号', '上海', '200002'));

-- 比较地址是否相同
SELECT name,
       home_address = work_address AS same_city_work,
       (home_address).city = (work_address).city AS same_city
FROM users;

输出结果：

 name | same_city_work | same_city
------+----------------+-----------
 张三 | f              | t
 李四 | f              | t

特殊的二进制比较操作符 ​

PostgreSQL 为复合类型提供了特殊的二进制比较操作符：*=、*<>、*<、*<=、*>、*>=

示例 2：二进制比较

sql
-- 创建测试数据
CREATE TYPE coord AS (x INTEGER, y INTEGER);

CREATE TABLE shapes (
    id SERIAL PRIMARY KEY,
    center coord
);

INSERT INTO shapes (center) VALUES
((1, 2)),
((1, 2)),
((2, 1));

-- 标准比较 vs 二进制比较
SELECT
    s1.id AS id1,
    s2.id AS id2,
    s1.center = s2.center AS standard_equal,
    s1.center *= s2.center AS binary_equal
FROM shapes s1, shapes s2
WHERE s1.id < s2.id;

性能优化建议 ​

1. 索引策略 ​

sql
-- 为经常用于 IN 查询的列创建索引
CREATE INDEX idx_department ON employees(department);

-- 对于数组操作，考虑使用 GIN 索引
CREATE INDEX idx_quality_scores ON products USING GIN(quality_scores);

2. 查询优化 ​

优化示例：

sql
-- 不推荐：可能遇到 NULL 问题
SELECT * FROM employees WHERE department NOT IN (
    SELECT department FROM temp_excluded_departments
);

-- 推荐：使用 NOT EXISTS
SELECT * FROM employees e WHERE NOT EXISTS (
    SELECT 1 FROM temp_excluded_departments t
    WHERE t.department = e.department
);

-- 推荐：使用 COALESCE 处理 NULL
SELECT * FROM employees
WHERE COALESCE(department, '未知') NOT IN ('技术部', '销售部');

总结 ​

PostgreSQL 的行和数组比较操作提供了强大而灵活的数据比较能力：

操作符用途特点最佳应用场景
IN值列表匹配简洁的多值比较状态筛选、分类查询
NOT IN排除值列表需注意 NULL 陷阱排除特定条件
ANY/SOME数组任意匹配PostgreSQL 扩展数组数据查询
ALL数组全部匹配严格的条件检查质量控制、阈值验证
行构造器多字段比较支持字典序比较坐标、复合条件
复合类型结构化数据比较面向对象的比较复杂数据结构

WARNING

重要提醒在使用这些操作符时，特别注意 NULL 值的处理，选择合适的操作符和处理策略，避免意外的查询结果。