表表达式详解

什么是表表达式

表表达式是 PostgreSQL 查询中的核心概念，它用于计算和生成一个表结构。表表达式包含一个必需的 FROM 子句，以及可选的 WHERE、GROUP BY 和 HAVING 子句。

表表达式的执行流程

表表达式中的各个子句按照以下顺序执行：

FROM 子句：确定数据源
WHERE 子句：过滤行
GROUP BY 子句：对行进行分组
HAVING 子句：过滤分组
窗口函数处理：计算窗口函数

FROM 子句详解

基本语法

FROM 子句是表表达式的起点，用于指定查询的数据源。

sql

FROM table_reference [, table_reference [, ...]]

单表查询示例

基本查询继承表查询

sql

-- 查询用户表中的所有数据
SELECT * FROM users;

-- 查询特定列
SELECT user_id, username, email FROM users;

sql

-- 查询父表及所有子表的数据（默认行为）
SELECT * FROM employees;

-- 仅查询父表的数据
SELECT * FROM ONLY employees;

-- 明确指定包含子表
SELECT * FROM employees *;

多表查询（交叉连接）

当在 FROM 子句中指定多个表时，PostgreSQL 会执行交叉连接（笛卡尔积）：

sql

-- 演示数据
CREATE TABLE colors (id INT, color TEXT);
INSERT INTO colors VALUES (1, 'red'), (2, 'blue');

CREATE TABLE sizes (id INT, size TEXT);
INSERT INTO sizes VALUES (1, 'small'), (2, 'large');

-- 交叉连接示例
SELECT * FROM colors, sizes;

输出结果： | id | color | id | size | |----|-------|----|-------| | 1 | red | 1 | small | | 1 | red | 2 | large | | 2 | blue | 1 | small | | 2 | blue | 2 | large |

交叉连接的性能注意事项

交叉连接会产生 N × M 行的结果集，其中 N 和 M 分别是两个表的行数。在处理大表时要特别小心，避免产生过大的结果集。

连接表操作

连接类型概述

PostgreSQL 支持多种连接类型，每种都有特定的用途：

连接类型	说明	使用场景
CROSS JOIN	笛卡尔积	生成所有可能的组合
INNER JOIN	内连接	返回两表中匹配的记录
LEFT JOIN	左外连接	保留左表所有记录
RIGHT JOIN	右外连接	保留右表所有记录
FULL JOIN	全外连接	保留两表所有记录

详细连接示例

首先创建示例数据：

sql

-- 创建示例表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name TEXT,
    dept_id INT
);

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name TEXT,
    budget DECIMAL
);

-- 插入测试数据
INSERT INTO employees VALUES
    (1, '张三', 10),
    (2, '李四', 20),
    (3, '王五', 10),
    (4, '赵六', NULL);

INSERT INTO departments VALUES
    (10, '技术部', 100000),
    (20, '销售部', 80000),
    (30, '人事部', 60000);

1. 交叉连接（CROSS JOIN）

sql

-- 语法形式1
SELECT e.name, d.dept_name
FROM employees e CROSS JOIN departments d;

-- 语法形式2（等效）
SELECT e.name, d.dept_name
FROM employees e, departments d;

分析过程：

交叉连接产生两表的笛卡尔积
结果包含 4 × 3 = 12 行
每个员工与每个部门都会产生一条记录

2. 内连接（INNER JOIN）

sql

-- 使用 ON 条件
SELECT e.name, d.dept_name, d.budget
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- 使用 USING 简化语法
SELECT e.name, d.dept_name, d.budget
FROM employees e
INNER JOIN departments d USING (dept_id);

-- 自然连接
SELECT e.name, d.dept_name, d.budget
FROM employees e
NATURAL INNER JOIN departments d;

输出结果： | name | dept_name | budget | |------|-----------|--------| | 张三 | 技术部 | 100000 | | 李四 | 销售部 | 80000 | | 王五 | 技术部 | 100000 |

分析过程：

只返回两表中 dept_id 匹配的记录
赵六因为 dept_id 为 NULL，不参与匹配
人事部因为没有对应员工，也不在结果中

3. 左外连接（LEFT JOIN）

sql

SELECT e.name, d.dept_name, d.budget
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

输出结果： | name | dept_name | budget | |------|-----------|--------| | 张三 | 技术部 | 100000 | | 李四 | 销售部 | 80000 | | 王五 | 技术部 | 100000 | | 赵六 | NULL | NULL |

分析过程：

保留左表（employees）的所有记录
右表中没有匹配的记录用 NULL 填充
赵六的记录被保留，但部门信息为 NULL

4. 右外连接（RIGHT JOIN）

sql

SELECT e.name, d.dept_name, d.budget
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

输出结果： | name | dept_name | budget | |------|-----------|--------| | 张三 | 技术部 | 100000 | | 王五 | 技术部 | 100000 | | 李四 | 销售部 | 80000 | | NULL | 人事部 | 60000 |

5. 全外连接（FULL JOIN）

sql

SELECT e.name, d.dept_name, d.budget
FROM employees e
FULL JOIN departments d ON e.dept_id = d.dept_id;

输出结果： | name | dept_name | budget | |------|-----------|--------| | 张三 | 技术部 | 100000 | | 王五 | 技术部 | 100000 | | 李四 | 销售部 | 80000 | | 赵六 | NULL | NULL | | NULL | 人事部 | 60000 |

连接条件的区别

ON vs WHERE 的重要区别

sql

-- 在 ON 子句中添加条件
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id AND d.budget > 90000;

-- 在 WHERE 子句中添加条件
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.budget > 90000;

点击查看结果对比

使用 ON 条件的结果： | name | dept_name | |------|-----------| | 张三 | 技术部 | | 李四 | NULL | | 王五 | 技术部 | | 赵六 | NULL |

使用 WHERE 条件的结果： | name | dept_name | |------|-----------| | 张三 | 技术部 | | 王五 | 技术部 |

分析过程：

ON 条件在连接过程中执行，影响连接的匹配逻辑
WHERE 条件在连接完成后执行，过滤最终结果
对于外连接，这个区别尤为重要

表别名的使用

表别名让查询更简洁，在自连接中必不可少：

sql

-- 基本别名使用
SELECT e.name, d.dept_name
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.dept_id;

-- AS 关键字可省略
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- 自连接示例：查找同部门的员工对
SELECT e1.name AS employee1, e2.name AS employee2
FROM employees e1
JOIN employees e2 ON e1.dept_id = e2.dept_id AND e1.emp_id < e2.emp_id;

子查询作为数据源

sql

-- 基本子查询
SELECT avg_budget.dept_name, avg_budget.avg_sal
FROM (
    SELECT d.dept_name, AVG(e.salary) as avg_sal
    FROM departments d
    JOIN employees e ON d.dept_id = e.dept_id
    GROUP BY d.dept_name
) AS avg_budget
WHERE avg_budget.avg_sal > 50000;

-- VALUES 列表作为数据源
SELECT names.first, names.last, names.age
FROM (VALUES
    ('张', '三', 25),
    ('李', '四', 30),
    ('王', '五', 28)
) AS names(first, last, age)
WHERE names.age > 26;

表函数的使用

sql

-- 基本表函数
CREATE OR REPLACE FUNCTION get_employees_by_dept(dept_id INT)
RETURNS TABLE(emp_id INT, name TEXT, salary DECIMAL) AS $$
BEGIN
    RETURN QUERY
    SELECT e.emp_id, e.name, e.salary
    FROM employees e
    WHERE e.dept_id = get_employees_by_dept.dept_id;
END;
$$ LANGUAGE plpgsql;

-- 使用表函数
SELECT * FROM get_employees_by_dept(10);

-- 结合 WITH ORDINALITY 添加行号
SELECT row_num, emp_id, name
FROM get_employees_by_dept(10) WITH ORDINALITY AS t(emp_id, name, salary, row_num);

LATERAL 子查询

LATERAL 允许子查询引用前面 FROM 项的列：

sql

-- 为每个部门查找工资最高的员工
SELECT d.dept_name, top_emp.name, top_emp.salary
FROM departments d,
LATERAL (
    SELECT e.name, e.salary
    FROM employees e
    WHERE e.dept_id = d.dept_id
    ORDER BY e.salary DESC
    LIMIT 1
) AS top_emp;

-- 使用 LEFT JOIN 处理空部门
SELECT d.dept_name, COALESCE(top_emp.name, '无员工') as top_employee
FROM departments d
LEFT JOIN LATERAL (
    SELECT e.name
    FROM employees e
    WHERE e.dept_id = d.dept_id
    ORDER BY e.salary DESC
    LIMIT 1
) AS top_emp ON true;

WHERE 子句详解

基本过滤操作

sql

WHERE search_condition

WHERE 子句在 FROM 子句处理完成后执行，用于过滤不符合条件的行。

常用过滤条件示例

sql

-- 比较操作
SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE dept_id = 10;
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2023-12-31';

-- 模式匹配
SELECT * FROM employees WHERE name LIKE '张%';
SELECT * FROM employees WHERE email ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

-- 列表匹配
SELECT * FROM employees WHERE dept_id IN (10, 20, 30);
SELECT * FROM employees WHERE dept_id NOT IN (SELECT dept_id FROM departments WHERE budget < 70000);

-- NULL 值处理
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE phone IS NOT NULL;

-- 逻辑组合
SELECT * FROM employees
WHERE (dept_id = 10 OR dept_id = 20)
  AND salary > 40000
  AND hire_date > '2020-01-01';

子查询在 WHERE 中的应用

sql

-- 标量子查询
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 存在性检查
SELECT d.dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);

-- 相关子查询
SELECT e1.name, e1.salary, e1.dept_id
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

GROUP BY 和 HAVING 子句

GROUP BY 基础概念

GROUP BY 子句将具有相同值的行分组在一起，通常与聚合函数配合使用。

sql

SELECT select_list
FROM table_name
[WHERE condition]
GROUP BY grouping_column_reference [, ...]
[HAVING group_condition]

基本分组示例

sql

-- 按部门统计员工数量
SELECT dept_id, COUNT(*) as employee_count
FROM employees
GROUP BY dept_id;

-- 按部门统计平均工资
SELECT d.dept_name, AVG(e.salary) as avg_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

-- 多列分组
SELECT dept_id,
       EXTRACT(YEAR FROM hire_date) as hire_year,
       COUNT(*) as count
FROM employees
GROUP BY dept_id, EXTRACT(YEAR FROM hire_date)
ORDER BY dept_id, hire_year;

聚合函数详解

函数	功能	示例
COUNT(*)	统计行数	`COUNT(*)`
COUNT(column)	统计非 NULL 值数量	`COUNT(salary)`
SUM()	求和	`SUM(salary)`
AVG()	求平均值	`AVG(salary)`
MIN()/MAX()	最小值/最大值	`MIN(hire_date)`
STRING_AGG()	字符串聚合	`STRING_AGG(name, ',

表表达式详解 ​

什么是表表达式 ​

FROM 子句详解 ​

基本语法 ​

单表查询示例 ​

多表查询（交叉连接） ​

连接表操作 ​

连接类型概述 ​

详细连接示例 ​

1. 交叉连接（CROSS JOIN） ​

2. 内连接（INNER JOIN） ​

3. 左外连接（LEFT JOIN） ​

4. 右外连接（RIGHT JOIN） ​

5. 全外连接（FULL JOIN） ​

连接条件的区别 ​

ON vs WHERE 的重要区别 ​

表别名的使用 ​

子查询作为数据源 ​

表函数的使用 ​

LATERAL 子查询 ​

WHERE 子句详解 ​

基本过滤操作 ​

常用过滤条件示例 ​

子查询在 WHERE 中的应用 ​

GROUP BY 和 HAVING 子句 ​

GROUP BY 基础概念 ​

基本分组示例 ​

聚合函数详解 ​

表表达式详解

什么是表表达式

FROM 子句详解

基本语法

单表查询示例

多表查询（交叉连接）

连接表操作

连接类型概述

详细连接示例

1. 交叉连接（CROSS JOIN）

2. 内连接（INNER JOIN）

3. 左外连接（LEFT JOIN）

4. 右外连接（RIGHT JOIN）

5. 全外连接（FULL JOIN）

连接条件的区别

ON vs WHERE 的重要区别

表别名的使用

子查询作为数据源

表函数的使用

LATERAL 子查询

WHERE 子句详解

基本过滤操作

常用过滤条件示例

子查询在 WHERE 中的应用

GROUP BY 和 HAVING 子句

GROUP BY 基础概念

基本分组示例

聚合函数详解