PostgreSQL笔记14:子查询与嵌套查询的完整分类、执行模型与性能优化

纲要

  • 子查询(Subquery)的定义与核心概念
    • 主查询(Main Query)与子查询的嵌套关系
  • 子查询的分类体系
    • 标量子查询(Scalar Subquery):返回一行一列
    • 行值子查询(Row Subquery):返回一行多列
    • 派生表(Derived Table / Table Subquery):返回多行多列,用于 FROM 子句
    • IN / NOT IN 子查询:等值集合判断
    • EXISTS / NOT EXISTS 子查询:存在性判断
    • 比较子查询(Comparison Subquery):=, >, <, >=, <=, <> 配合 ALL / ANY / SOME
  • LATERAL 子查询(横向子查询)
    • 与普通 JOIN 的本质区别:右侧子查询可引用左侧表项
    • 典型应用场景:每组取 Top-N、行级派生
  • 相关子查询(Correlated Subquery)与非相关子查询(Non-Correlated Subquery)
    • 执行模型差异:独立执行 vs 逐行循环执行
    • EXPLAIN 中的 InitPlan 与 SubPlan / loops
  • 标量子查询的性能陷阱与 LEFT JOIN 改写
  • PostgreSQL 17 优化器改进:IN 相关子查询的 pull-up 优化
  • 实践建议:子查询选型与性能调优策略

子查询的定义与分类

子查询(Subquery)本质上是嵌套在其他 SQL 语句中的 SELECT 语句[reference:0]。它可出现在 SELECT、FROM、WHERE、HAVING、INSERT、UPDATE、DELETE 等多种位置。包含子查询的查询称为主查询(Main Query)。执行时,数据库会根据子查询的类型和相关性,决定其执行顺序与方式。

从返回结果的结构来看,子查询可分为以下几类:

分类返回结构典型使用位置说明
标量子查询(Scalar Subquery)一行一列SELECT、WHERE、FROM作为单值表达式使用[reference:1]
行值子查询(Row Subquery)一行多列行构造器比较返回与左侧行构造器列数匹配的一行
派生表(Derived Table)多行多列FROM 子句必须指定表别名[reference:2]

从语义功能来看,又可分为:

分类功能典型关键字
集合成员判断判断值是否存在于子查询结果集中IN / NOT IN[reference:3]
存在性判断判断子查询是否返回至少一行EXISTS / NOT EXISTS[reference:4]
比较子查询与子查询返回的集合进行逐行比较= ANY, > ALL, < SOME 等

以下通过具体的测试数据来逐一演示各类子查询的用法。

测试数据准备

首先创建班级表(class)和学生表(student):

-- 创建班级表
CREATE TABLE class (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

-- 创建学生表
CREATE TABLE student (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    class_id INTEGER REFERENCES class(id),
    score NUMERIC(5,2)
);

-- 插入班级数据
INSERT INTO class VALUES (1, 'ClassA'), (2, 'ClassB'), (3, 'ClassC');

-- 插入学生数据
INSERT INTO student VALUES
    (1, 'Alice', 1, 95.0),
    (2, 'Bob', 1, 88.0),
    (3, 'Charlie', 2, 82.0),
    (4, 'David', 2, 78.0),
    (5, 'Eve', 3, 99.5),
    (6, 'Frank', 3, 72.0),
    (7, 'Grace', 1, 91.0),
    (8, 'Henry', 2, 85.0);

标量子查询(Scalar Subquery)

标量子查询是返回一行一列的普通 SELECT 查询[reference:5]。它在任何需要单值表达式的地方均可使用,如 SELECT 列表、WHERE 条件或 FROM 子句中。

-- 查询分数最高的学生信息(标量子查询在 WHERE 子句中)
SELECT * FROM student
WHERE score = (SELECT MAX(score) FROM student);
-- 等价的聚合查询写法(对比)
SELECT * FROM student WHERE score = 95.0;

标量子查询也可出现在 SELECT 列表中,为每一行计算一个派生值:

-- 查询每个班级及其学生人数(标量子查询在 SELECT 中)
SELECT
    c.id,
    c.name,
    (SELECT COUNT(*) FROM student s WHERE s.class_id = c.id) AS student_count
FROM class c;

派生表(Derived Table)

派生表是出现在 FROM 子句中的子查询,其结果集作为临时表供外层查询使用[reference:6]。派生表必须指定表别名[reference:7]。

-- 派生表:先按班级计算平均分,再与班级表关联
SELECT
    c.name AS class_name,
    avg_stats.avg_score
FROM class c
JOIN (
    SELECT class_id, AVG(score) AS avg_score
    FROM student
    GROUP BY class_id
) AS avg_stats ON c.id = avg_stats.class_id;

派生表适用于将复杂逻辑拆分,使主查询结构更清晰。

IN 与 NOT IN 子查询

IN 判断左侧表达式是否存在于子查询返回的集合中[reference:8]。子查询必须返回恰好一列[reference:9]。

-- 查询所有在 ClassA 就读的学生
SELECT * FROM student
WHERE class_id IN (SELECT id FROM class WHERE name = 'ClassA');
-- NOT IN:查询不在 ClassA 就读的学生
SELECT * FROM student
WHERE class_id NOT IN (SELECT id FROM class WHERE name = 'ClassA');

注意:IN / NOT IN 在处理 NULL 值时需特别小心。若子查询结果中包含 NULL,NOT IN 的语义会变为 UNKNOWN,可能导致结果为空[reference:10]。

EXISTS 与 NOT EXISTS 子查询

EXISTS 判断子查询是否返回至少一行[reference:11]。子查询一旦找到匹配行即停止执行[reference:12]。

-- 查询至少有一名学生分数超过 90 分的班级
SELECT * FROM class c
WHERE EXISTS (
    SELECT 1 FROM student s
    WHERE s.class_id = c.id AND s.score > 90
);

EXISTS 的输出列表通常无关紧要,推荐使用 SELECT 1 或 SELECT *[reference:13][reference:14]。

-- NOT EXISTS:查询没有任何学生分数超过 90 分的班级
SELECT * FROM class c
WHERE NOT EXISTS (
    SELECT 1 FROM student s
    WHERE s.class_id = c.id AND s.score > 90
);

比较子查询:ALL、ANY、SOME

比较子查询将左侧表达式与子查询返回的集合进行比较,支持 =, <>, >, <, >=, <= 等运算符,配合 ALL、ANY 或 SOME 关键字。

-- 查询分数高于所有班级平均分的学生
SELECT * FROM student
WHERE score > ALL (
    SELECT AVG(score) FROM student GROUP BY class_id
);
-- 查询分数高于任意一个班级平均分的学生(即高于最低平均分)
SELECT * FROM student
WHERE score > ANY (
    SELECT AVG(score) FROM student GROUP BY class_id
);

ALL 要求与集合中所有值比较均为真;ANY / SOME 要求与集合中至少一个值比较为真。

LATERAL 子查询(横向子查询)

LATERAL 是 PostgreSQL 对 SQL 标准的扩展实现[reference:15][reference:16]。普通的 FROM 子查询独立求值,不能引用同一 FROM 列表中其他表项。而带有 LATERAL 的子查询可以引用左侧 FROM 项提供的列[reference:17]。

-- 普通 JOIN 无法在子查询中引用外层表的列(以下写法会报错)
-- SELECT * FROM class c
-- JOIN (SELECT AVG(score) FROM student s WHERE s.class_id = c.id) AS avg_score ON true;

-- LATERAL JOIN:每个班级计算其学生平均分
SELECT
    c.id,
    c.name,
    avg_stats.avg_score
FROM class c
CROSS JOIN LATERAL (
    SELECT AVG(score) AS avg_score
    FROM student s
    WHERE s.class_id = c.id
) AS avg_stats;

LATERAL 的典型应用场景包括:每组取 Top-N、对每一行执行聚合计算、行级派生等。

-- 每个班级取分数最高的 2 名学生(Top-N per group)
SELECT
    c.name AS class_name,
    top_students.name,
    top_students.score
FROM class c
CROSS JOIN LATERAL (
    SELECT s.name, s.score
    FROM student s
    WHERE s.class_id = c.id
    ORDER BY s.score DESC
    LIMIT 2
) AS top_students
ORDER BY c.name, top_students.score DESC;

相关子查询与非相关子查询

非相关子查询(Non-Correlated Subquery)

非相关子查询不依赖外层查询的任何列,可独立执行。数据库通常只执行一次子查询,将结果用于外层查询。

-- 非相关子查询:子查询独立于外层
SELECT * FROM student
WHERE score > (SELECT AVG(score) FROM student);

通过 EXPLAIN 可观察到 InitPlan,表示子查询仅执行一次:

EXPLAIN SELECT * FROM student
WHERE score > (SELECT AVG(score) FROM student);

相关子查询(Correlated Subquery)

相关子查询引用了外层查询的列[reference:18]。外层每处理一行,子查询就需要执行一次[reference:19]。

-- 相关子查询:子查询引用了外层 student 表的 class_id
SELECT * FROM student s1
WHERE score > (
    SELECT AVG(score) FROM student s2
    WHERE s2.class_id = s1.class_id
);

EXPLAIN 中会显示 SubPlan 及其 loops 次数,表示子查询被循环执行的次数:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM student s1
WHERE score > (
    SELECT AVG(score) FROM student s2
    WHERE s2.class_id = s1.class_id
);

性能影响:若主表有 N 行,子查询将被执行 N 次[reference:20]。当 N 较大时,性能急剧下降。

标量子查询的性能陷阱与 LEFT JOIN 改写

标量子查询在 SELECT 列表或 WHERE 条件中每行执行一次,存在与相关子查询相同的性能问题。在 PostgreSQL 17 以前,推荐将标量子查询改写为 LEFT JOIN 以提升性能[reference:21]。

-- 性能较差的标量子查询(每行执行一次子查询)
SELECT
    c.id,
    c.name,
    (SELECT COUNT(*) FROM student s WHERE s.class_id = c.id) AS student_count
FROM class c;

-- 改写为 LEFT JOIN + GROUP BY(性能更优)
SELECT
    c.id,
    c.name,
    COALESCE(s_cnt.cnt, 0) AS student_count
FROM class c
LEFT JOIN (
    SELECT class_id, COUNT(*) AS cnt
    FROM student
    GROUP BY class_id
) s_cnt ON c.id = s_cnt.class_id;

改写后,子查询仅执行一次(作为派生表),外层通过 LEFT JOIN 关联结果,避免了逐行循环执行[reference:22]。

PostgreSQL 17 优化器改进:IN 相关子查询的 Pull-up

在 PostgreSQL 17 以前,对于相关 IN 子查询,优化器无法自动将其提升为 JOIN,导致逐行循环执行(SubPlan)[reference:23]。DBA 需手动将 IN 改写为 EXISTS 以获得更好的执行计划[reference:24]。

-- PostgreSQL 16 及更早版本:相关 IN 子查询性能较差
SELECT * FROM student s1
WHERE s1.class_id IN (
    SELECT c.id FROM class c
    WHERE c.name LIKE 'Class%' AND c.id = s1.class_id
);

-- 手动改写为 EXISTS(16 及更早版本性能更优)
SELECT * FROM student s1
WHERE EXISTS (
    SELECT 1 FROM class c
    WHERE c.id = s1.class_id AND c.name LIKE 'Class%'
);

PostgreSQL 17 改进了优化器,能够自动将相关 IN 子查询转换为高效的 JOIN 或 SEMI JOIN 计划[reference:25]。这意味着在 17 及以上版本中,开发者无需手动改写,数据库会自动选择更优的执行路径[reference:26]。

执行计划解读:识别子查询性能问题

通过 EXPLAIN 可识别子查询的执行方式:

执行计划关键字含义性能特征
InitPlan子查询在查询开始时执行一次高效,适用于非相关子查询
SubPlan子查询在外层每行执行一次可能存在性能问题,需关注 loops 次数
loops子查询实际执行次数值越大,性能开销越高
-- 检查子查询的执行方式
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM student s1
WHERE score > (SELECT AVG(score) FROM student s2 WHERE s2.class_id = s1.class_id);

实践建议

  1. 优先使用非相关子查询:独立执行的子查询仅执行一次,性能最优。
  2. 相关子查询优先考虑 EXISTS:在 PostgreSQL 17 以前,EXISTS 比 IN 更易被优化器提升为 JOIN[reference:27]。PostgreSQL 17 及以后两者差异缩小。
  3. 标量子查询考虑 LEFT JOIN 改写:在 SELECT 列表中的标量子查询,建议改写为 LEFT JOIN + 聚合。
  4. 派生表必须指定别名:FROM 子句中的子查询必须使用 AS alias 指定别名[reference:28]。
  5. 善用 LATERAL:当需要引用左侧表项时,使用 LATERAL 实现行级动态派生。
  6. 监控执行计划:定期使用 EXPLAIN (ANALYZE, BUFFERS) 检查子查询的 loops 次数,识别潜在性能瓶颈。

API 速览

本节梳理 PostgreSQL 中子查询相关的核心 SQL 语法与 API。

标量子查询(Scalar Subquery)

语法:

(SELECT 表达式 FROM 表名 WHERE 条件)

约束:必须返回恰好一行一列[reference:29]。若返回多行或多列,抛出运行时错误。

返回值:单值(可为 NULL)。

EXISTS / NOT EXISTS

语法:

EXISTS (subquery)
NOT EXISTS (subquery)

说明:subquery 为任意 SELECT 语句[reference:30]。返回布尔值[reference:31]。子查询一旦找到匹配行即停止执行[reference:32]。

返回值:BOOLEAN(true / false)。

IN / NOT IN

语法:

expression IN (subquery)
expression NOT IN (subquery)
row_constructor IN (subquery)

说明:子查询必须返回恰好一列(行构造器形式需匹配列数)[reference:33][reference:34]。注意 NULL 值的三值逻辑[reference:35]。

返回值:BOOLEAN(true / false / NULL)。

比较子查询(ALL / ANY / SOME)

语法:

expression operator ALL (subquery)
expression operator ANY (subquery)
expression operator SOME (subquery)

说明:operator 为 =, <>, >, <, >=, <= 之一。

返回值:BOOLEAN。

LATERAL 子查询

语法:

FROM 左表 [CROSS | LEFT] JOIN LATERAL (subquery) AS alias ON 条件

说明:LATERAL 允许子查询引用左侧 FROM 项中的列[reference:36][reference:37]。

Demo 简单示例

以下是一个完整的 Node.js 示例,演示 PostgreSQL 子查询的各类用法。

运行说明

  1. 确保本地运行 PostgreSQL(>= 14,推荐 17)
  2. 创建测试数据库:createdb subquery_demo
  3. 安装依赖:npm install pg
  4. 运行脚本:node demo.js

代码实现

const { Client } = require('pg');

const client = new Client({
    host: 'localhost',
    port: 5432,
    database: 'subquery_demo',
    user: 'postgres',
    password: 'your_password'
});

async function runDemo() {
    await client.connect();

    // 1. 创建测试表
    await client.query(`
        DROP TABLE IF EXISTS student CASCADE;
        DROP TABLE IF EXISTS class CASCADE;
        CREATE TABLE class (id INTEGER PRIMARY KEY, name VARCHAR(50));
        CREATE TABLE student (
            id INTEGER PRIMARY KEY,
            name VARCHAR(50),
            class_id INTEGER REFERENCES class(id),
            score NUMERIC(5,2)
        );
        INSERT INTO class VALUES (1, 'ClassA'), (2, 'ClassB'), (3, 'ClassC');
        INSERT INTO student VALUES
            (1, 'Alice', 1, 95.0), (2, 'Bob', 1, 88.0),
            (3, 'Charlie', 2, 82.0), (4, 'David', 2, 78.0),
            (5, 'Eve', 3, 99.5), (6, 'Frank', 3, 72.0),
            (7, 'Grace', 1, 91.0), (8, 'Henry', 2, 85.0);
    `);

    // 2. 标量子查询:查询分数最高的学生
    const scalarResult = await client.query(`
        SELECT * FROM student
        WHERE score = (SELECT MAX(score) FROM student);
    `);
    console.log('标量子查询 - 最高分学生:', scalarResult.rows);

    // 3. EXISTS:查询有学生超过90分的班级
    const existsResult = await client.query(`
        SELECT * FROM class c
        WHERE EXISTS (
            SELECT 1 FROM student s
            WHERE s.class_id = c.id AND s.score > 90
        );
    `);
    console.log('EXISTS - 有高分学生的班级:', existsResult.rows);

    // 4. IN:查询ClassA的学生
    const inResult = await client.query(`
        SELECT * FROM student
        WHERE class_id IN (SELECT id FROM class WHERE name = 'ClassA');
    `);
    console.log('IN - ClassA的学生:', inResult.rows);

    // 5. 派生表:每个班级的平均分
    const derivedResult = await client.query(`
        SELECT c.name, avg_stats.avg_score
        FROM class c
        JOIN (
            SELECT class_id, AVG(score) AS avg_score
            FROM student
            GROUP BY class_id
        ) AS avg_stats ON c.id = avg_stats.class_id;
    `);
    console.log('派生表 - 班级平均分:', derivedResult.rows);

    // 6. LATERAL:每个班级取前2名
    const lateralResult = await client.query(`
        SELECT c.name, top.name, top.score
        FROM class c
        CROSS JOIN LATERAL (
            SELECT s.name, s.score
            FROM student s
            WHERE s.class_id = c.id
            ORDER BY s.score DESC
            LIMIT 2
        ) AS top
        ORDER BY c.name, top.score DESC;
    `);
    console.log('LATERAL - 每班前2名:', lateralResult.rows);

    // 7. 相关子查询:分数高于所在班级平均分的学生
    const correlatedResult = await client.query(`
        SELECT s1.name, s1.score, s1.class_id
        FROM student s1
        WHERE s1.score > (
            SELECT AVG(s2.score)
            FROM student s2
            WHERE s2.class_id = s1.class_id
        );
    `);
    console.log('相关子查询 - 高于班级平均分的学生:', correlatedResult.rows);

    // 8. 标量子查询改写为 LEFT JOIN(性能优化)
    const optimizedResult = await client.query(`
        SELECT c.id, c.name, COALESCE(s_cnt.cnt, 0) AS student_count
        FROM class c
        LEFT JOIN (
            SELECT class_id, COUNT(*) AS cnt
            FROM student
            GROUP BY class_id
        ) s_cnt ON c.id = s_cnt.class_id;
    `);
    console.log('LEFT JOIN改写 - 班级学生数:', optimizedResult.rows);

    await client.end();
}

runDemo().catch(console.error);

原生 PostgreSQL 指令对照

-- 标量子查询
SELECT * FROM student WHERE score = (SELECT MAX(score) FROM student);

-- EXISTS
SELECT * FROM class c WHERE EXISTS (SELECT 1 FROM student s WHERE s.class_id = c.id AND s.score > 90);

-- IN
SELECT * FROM student WHERE class_id IN (SELECT id FROM class WHERE name = 'ClassA');

-- 派生表
SELECT c.name, avg_stats.avg_score FROM class c JOIN (SELECT class_id, AVG(score) AS avg_score FROM student GROUP BY class_id) AS avg_stats ON c.id = avg_stats.class_id;

-- LATERAL
SELECT c.name, top.name, top.score FROM class c CROSS JOIN LATERAL (SELECT s.name, s.score FROM student s WHERE s.class_id = c.id ORDER BY s.score DESC LIMIT 2) AS top ORDER BY c.name, top.score DESC;

-- 相关子查询
SELECT s1.name, s1.score FROM student s1 WHERE s1.score > (SELECT AVG(s2.score) FROM student s2 WHERE s2.class_id = s1.class_id);

-- LEFT JOIN 改写标量子查询
SELECT c.id, c.name, COALESCE(s_cnt.cnt, 0) AS student_count FROM class c LEFT JOIN (SELECT class_id, COUNT(*) AS cnt FROM student GROUP BY class_id) s_cnt ON c.id = s_cnt.class_id;

技术点总结

技术点说明
标量子查询返回单值,可用于任意表达式位置
EXISTS存在性判断,查到即停,性能优于 COUNT(*) > 0
IN集合成员判断,注意 NULL 处理
派生表FROM 子句中的子查询,必须指定别名
LATERAL横向子查询,可引用左侧表项,适用于 Top-N
相关子查询每行执行一次,需关注性能
LEFT JOIN 改写标量子查询的常见性能优化手段

子查询示例 - Go / Python / Java 完整 Demo

本节基于前文的 Node.js 示例,提供 Go (使用 pgx)、Python (使用 psycopg2) 和 Java (使用 JDBC) 三种语言的完整可运行实现。所有示例均包含测试数据初始化、各类子查询演示以及性能优化改写。

Go 示例 (使用 pgx)

运行说明
  1. 安装 Go 1.18+,创建项目目录
  2. 安装依赖:go get github.com/jackc/pgx/v5
  3. 修改连接字符串(数据库名、用户、密码)
  4. 运行:go run main.go
代码实现
package main

import (
	"context"
	"fmt"
	"log"

	"github.com/jackc/pgx/v5"
)

func main() {
	ctx := context.Background()

	// 连接数据库,请替换为实际参数
	connStr := "postgres://postgres:your_password@localhost:5432/subquery_demo"
	conn, err := pgx.Connect(ctx, connStr)
	if err != nil {
		log.Fatal("连接失败:", err)
	}
	defer conn.Close(ctx)

	// 1. 创建测试表并插入数据
	_, err = conn.Exec(ctx, `
		DROP TABLE IF EXISTS student CASCADE;
		DROP TABLE IF EXISTS class CASCADE;
		CREATE TABLE class (id INTEGER PRIMARY KEY, name VARCHAR(50));
		CREATE TABLE student (
			id INTEGER PRIMARY KEY,
			name VARCHAR(50),
			class_id INTEGER REFERENCES class(id),
			score NUMERIC(5,2)
		);
		INSERT INTO class VALUES (1, 'ClassA'), (2, 'ClassB'), (3, 'ClassC');
		INSERT INTO student VALUES
			(1, 'Alice', 1, 95.0), (2, 'Bob', 1, 88.0),
			(3, 'Charlie', 2, 82.0), (4, 'David', 2, 78.0),
			(5, 'Eve', 3, 99.5), (6, 'Frank', 3, 72.0),
			(7, 'Grace', 1, 91.0), (8, 'Henry', 2, 85.0);
	`)
	if err != nil {
		log.Fatal("建表失败:", err)
	}
	fmt.Println("测试数据准备完成")

	// 2. 标量子查询 - 最高分学生
	var scalarRows []struct {
		ID      int
		Name    string
		ClassID int
		Score   float64
	}
	rows, err := conn.Query(ctx, `
		SELECT id, name, class_id, score FROM student
		WHERE score = (SELECT MAX(score) FROM student)
	`)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		var s struct {
			ID      int
			Name    string
			ClassID int
			Score   float64
		}
		rows.Scan(&s.ID, &s.Name, &s.ClassID, &s.Score)
		scalarRows = append(scalarRows, s)
	}
	rows.Close()
	fmt.Printf("标量子查询 - 最高分学生: %+v\n", scalarRows)

	// 3. EXISTS - 有学生超过90分的班级
	type Class struct {
		ID   int
		Name string
	}
	var existsRows []Class
	rows, err = conn.Query(ctx, `
		SELECT * FROM class c
		WHERE EXISTS (
			SELECT 1 FROM student s
			WHERE s.class_id = c.id AND s.score > 90
		)
	`)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		var c Class
		rows.Scan(&c.ID, &c.Name)
		existsRows = append(existsRows, c)
	}
	rows.Close()
	fmt.Printf("EXISTS - 有高分学生的班级: %+v\n", existsRows)

	// 4. IN - ClassA的学生
	var inRows []struct {
		ID      int
		Name    string
		ClassID int
		Score   float64
	}
	rows, err = conn.Query(ctx, `
		SELECT * FROM student
		WHERE class_id IN (SELECT id FROM class WHERE name = 'ClassA')
	`)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		var s struct {
			ID      int
			Name    string
			ClassID int
			Score   float64
		}
		rows.Scan(&s.ID, &s.Name, &s.ClassID, &s.Score)
		inRows = append(inRows, s)
	}
	rows.Close()
	fmt.Printf("IN - ClassA的学生: %+v\n", inRows)

	// 5. 派生表 - 班级平均分
	type AvgRow struct {
		Name     string
		AvgScore float64
	}
	var derivedRows []AvgRow
	rows, err = conn.Query(ctx, `
		SELECT c.name, avg_stats.avg_score
		FROM class c
		JOIN (
			SELECT class_id, AVG(score) AS avg_score
			FROM student
			GROUP BY class_id
		) AS avg_stats ON c.id = avg_stats.class_id
	`)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		var r AvgRow
		rows.Scan(&r.Name, &r.AvgScore)
		derivedRows = append(derivedRows, r)
	}
	rows.Close()
	fmt.Printf("派生表 - 班级平均分: %+v\n", derivedRows)

	// 6. LATERAL - 每个班级取前2名
	type TopStudent struct {
		ClassName string
		Name      string
		Score     float64
	}
	var lateralRows []TopStudent
	rows, err = conn.Query(ctx, `
		SELECT c.name, top.name, top.score
		FROM class c
		CROSS JOIN LATERAL (
			SELECT s.name, s.score
			FROM student s
			WHERE s.class_id = c.id
			ORDER BY s.score DESC
			LIMIT 2
		) AS top
		ORDER BY c.name, top.score DESC
	`)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		var r TopStudent
		rows.Scan(&r.ClassName, &r.Name, &r.Score)
		lateralRows = append(lateralRows, r)
	}
	rows.Close()
	fmt.Printf("LATERAL - 每班前2名: %+v\n", lateralRows)

	// 7. 相关子查询 - 高于班级平均分的学生
	var corrRows []struct {
		Name    string
		Score   float64
		ClassID int
	}
	rows, err = conn.Query(ctx, `
		SELECT s1.name, s1.score, s1.class_id
		FROM student s1
		WHERE s1.score > (
			SELECT AVG(s2.score)
			FROM student s2
			WHERE s2.class_id = s1.class_id
		)
	`)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		var r struct {
			Name    string
			Score   float64
			ClassID int
		}
		rows.Scan(&r.Name, &r.Score, &r.ClassID)
		corrRows = append(corrRows, r)
	}
	rows.Close()
	fmt.Printf("相关子查询 - 高于班级平均分的学生: %+v\n", corrRows)

	// 8. LEFT JOIN 改写标量子查询
	type CountRow struct {
		ID           int
		Name         string
		StudentCount int
	}
	var optRows []CountRow
	rows, err = conn.Query(ctx, `
		SELECT c.id, c.name, COALESCE(s_cnt.cnt, 0) AS student_count
		FROM class c
		LEFT JOIN (
			SELECT class_id, COUNT(*) AS cnt
			FROM student
			GROUP BY class_id
		) s_cnt ON c.id = s_cnt.class_id
	`)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		var r CountRow
		rows.Scan(&r.ID, &r.Name, &r.StudentCount)
		optRows = append(optRows, r)
	}
	rows.Close()
	fmt.Printf("LEFT JOIN改写 - 班级学生数: %+v\n", optRows)

	fmt.Println("Go 示例执行完毕")
}

Python 示例 (使用 psycopg2)

运行说明
  1. 安装 Python 3.8+,安装依赖:pip install psycopg2-binary
  2. 修改连接参数(数据库名、用户、密码)
  3. 运行:python demo.py
代码实现
import psycopg2
from psycopg2 import sql

def main():
    # 连接数据库,请替换为实际参数
    conn = psycopg2.connect(
        host="localhost",
        port=5432,
        database="subquery_demo",
        user="postgres",
        password="your_password"
    )
    conn.autocommit = True
    cur = conn.cursor()

    # 1. 创建测试表并插入数据
    cur.execute("""
        DROP TABLE IF EXISTS student CASCADE;
        DROP TABLE IF EXISTS class CASCADE;
        CREATE TABLE class (id INTEGER PRIMARY KEY, name VARCHAR(50));
        CREATE TABLE student (
            id INTEGER PRIMARY KEY,
            name VARCHAR(50),
            class_id INTEGER REFERENCES class(id),
            score NUMERIC(5,2)
        );
        INSERT INTO class VALUES (1, 'ClassA'), (2, 'ClassB'), (3, 'ClassC');
        INSERT INTO student VALUES
            (1, 'Alice', 1, 95.0), (2, 'Bob', 1, 88.0),
            (3, 'Charlie', 2, 82.0), (4, 'David', 2, 78.0),
            (5, 'Eve', 3, 99.5), (6, 'Frank', 3, 72.0),
            (7, 'Grace', 1, 91.0), (8, 'Henry', 2, 85.0);
    """)
    print("测试数据准备完成")

    # 2. 标量子查询 - 最高分学生
    cur.execute("""
        SELECT id, name, class_id, score FROM student
        WHERE score = (SELECT MAX(score) FROM student)
    """)
    scalar_rows = cur.fetchall()
    print("标量子查询 - 最高分学生:", scalar_rows)

    # 3. EXISTS - 有学生超过90分的班级
    cur.execute("""
        SELECT * FROM class c
        WHERE EXISTS (
            SELECT 1 FROM student s
            WHERE s.class_id = c.id AND s.score > 90
        )
    """)
    exists_rows = cur.fetchall()
    print("EXISTS - 有高分学生的班级:", exists_rows)

    # 4. IN - ClassA的学生
    cur.execute("""
        SELECT * FROM student
        WHERE class_id IN (SELECT id FROM class WHERE name = 'ClassA')
    """)
    in_rows = cur.fetchall()
    print("IN - ClassA的学生:", in_rows)

    # 5. 派生表 - 班级平均分
    cur.execute("""
        SELECT c.name, avg_stats.avg_score
        FROM class c
        JOIN (
            SELECT class_id, AVG(score) AS avg_score
            FROM student
            GROUP BY class_id
        ) AS avg_stats ON c.id = avg_stats.class_id
    """)
    derived_rows = cur.fetchall()
    print("派生表 - 班级平均分:", derived_rows)

    # 6. LATERAL - 每个班级取前2名(PostgreSQL 9.3+)
    cur.execute("""
        SELECT c.name, top.name, top.score
        FROM class c
        CROSS JOIN LATERAL (
            SELECT s.name, s.score
            FROM student s
            WHERE s.class_id = c.id
            ORDER BY s.score DESC
            LIMIT 2
        ) AS top
        ORDER BY c.name, top.score DESC
    """)
    lateral_rows = cur.fetchall()
    print("LATERAL - 每班前2名:", lateral_rows)

    # 7. 相关子查询 - 高于班级平均分的学生
    cur.execute("""
        SELECT s1.name, s1.score, s1.class_id
        FROM student s1
        WHERE s1.score > (
            SELECT AVG(s2.score)
            FROM student s2
            WHERE s2.class_id = s1.class_id
        )
    """)
    corr_rows = cur.fetchall()
    print("相关子查询 - 高于班级平均分的学生:", corr_rows)

    # 8. LEFT JOIN 改写标量子查询
    cur.execute("""
        SELECT c.id, c.name, COALESCE(s_cnt.cnt, 0) AS student_count
        FROM class c
        LEFT JOIN (
            SELECT class_id, COUNT(*) AS cnt
            FROM student
            GROUP BY class_id
        ) s_cnt ON c.id = s_cnt.class_id
    """)
    opt_rows = cur.fetchall()
    print("LEFT JOIN改写 - 班级学生数:", opt_rows)

    cur.close()
    conn.close()
    print("Python 示例执行完毕")

if __name__ == "__main__":
    main()

Java 示例 (使用 JDBC)

运行说明
  1. 安装 JDK 11+,创建 Maven 或 Gradle 项目
  2. 添加 PostgreSQL JDBC 驱动依赖(org.postgresql:postgresql:42.7.3)
  3. 修改连接 URL、用户名、密码
  4. 编译运行 Main.java
代码实现
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Main {
    // 修改为实际连接参数
    private static final String URL = "jdbc:postgresql://localhost:5432/subquery_demo";
    private static final String USER = "postgres";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            conn.setAutoCommit(true);
            Statement stmt = conn.createStatement();

            // 1. 创建测试表并插入数据
            stmt.execute("""
                DROP TABLE IF EXISTS student CASCADE;
                DROP TABLE IF EXISTS class CASCADE;
                CREATE TABLE class (id INTEGER PRIMARY KEY, name VARCHAR(50));
                CREATE TABLE student (
                    id INTEGER PRIMARY KEY,
                    name VARCHAR(50),
                    class_id INTEGER REFERENCES class(id),
                    score NUMERIC(5,2)
                );
                INSERT INTO class VALUES (1, 'ClassA'), (2, 'ClassB'), (3, 'ClassC');
                INSERT INTO student VALUES
                    (1, 'Alice', 1, 95.0), (2, 'Bob', 1, 88.0),
                    (3, 'Charlie', 2, 82.0), (4, 'David', 2, 78.0),
                    (5, 'Eve', 3, 99.5), (6, 'Frank', 3, 72.0),
                    (7, 'Grace', 1, 91.0), (8, 'Henry', 2, 85.0);
            """);
            System.out.println("测试数据准备完成");

            // 2. 标量子查询 - 最高分学生
            try (ResultSet rs = stmt.executeQuery("""
                SELECT id, name, class_id, score FROM student
                WHERE score = (SELECT MAX(score) FROM student)
            """)) {
                List<String> rows = new ArrayList<>();
                while (rs.next()) {
                    rows.add(String.format("ID=%d, Name=%s, ClassID=%d, Score=%.2f",
                        rs.getInt("id"), rs.getString("name"),
                        rs.getInt("class_id"), rs.getDouble("score")));
                }
                System.out.println("标量子查询 - 最高分学生: " + rows);
            }

            // 3. EXISTS - 有学生超过90分的班级
            try (ResultSet rs = stmt.executeQuery("""
                SELECT * FROM class c
                WHERE EXISTS (
                    SELECT 1 FROM student s
                    WHERE s.class_id = c.id AND s.score > 90
                )
            """)) {
                List<String> rows = new ArrayList<>();
                while (rs.next()) {
                    rows.add(String.format("ID=%d, Name=%s", rs.getInt("id"), rs.getString("name")));
                }
                System.out.println("EXISTS - 有高分学生的班级: " + rows);
            }

            // 4. IN - ClassA的学生
            try (ResultSet rs = stmt.executeQuery("""
                SELECT * FROM student
                WHERE class_id IN (SELECT id FROM class WHERE name = 'ClassA')
            """)) {
                List<String> rows = new ArrayList<>();
                while (rs.next()) {
                    rows.add(String.format("ID=%d, Name=%s, ClassID=%d, Score=%.2f",
                        rs.getInt("id"), rs.getString("name"),
                        rs.getInt("class_id"), rs.getDouble("score")));
                }
                System.out.println("IN - ClassA的学生: " + rows);
            }

            // 5. 派生表 - 班级平均分
            try (ResultSet rs = stmt.executeQuery("""
                SELECT c.name, avg_stats.avg_score
                FROM class c
                JOIN (
                    SELECT class_id, AVG(score) AS avg_score
                    FROM student
                    GROUP BY class_id
                ) AS avg_stats ON c.id = avg_stats.class_id
            """)) {
                List<String> rows = new ArrayList<>();
                while (rs.next()) {
                    rows.add(String.format("Class=%s, AvgScore=%.2f",
                        rs.getString("name"), rs.getDouble("avg_score")));
                }
                System.out.println("派生表 - 班级平均分: " + rows);
            }

            // 6. LATERAL - 每个班级取前2名
            try (ResultSet rs = stmt.executeQuery("""
                SELECT c.name, top.name, top.score
                FROM class c
                CROSS JOIN LATERAL (
                    SELECT s.name, s.score
                    FROM student s
                    WHERE s.class_id = c.id
                    ORDER BY s.score DESC
                    LIMIT 2
                ) AS top
                ORDER BY c.name, top.score DESC
            """)) {
                List<String> rows = new ArrayList<>();
                while (rs.next()) {
                    rows.add(String.format("Class=%s, Student=%s, Score=%.2f",
                        rs.getString("name"), rs.getString("name"), rs.getDouble("score")));
                }
                System.out.println("LATERAL - 每班前2名: " + rows);
            }

            // 7. 相关子查询 - 高于班级平均分的学生
            try (ResultSet rs = stmt.executeQuery("""
                SELECT s1.name, s1.score, s1.class_id
                FROM student s1
                WHERE s1.score > (
                    SELECT AVG(s2.score)
                    FROM student s2
                    WHERE s2.class_id = s1.class_id
                )
            """)) {
                List<String> rows = new ArrayList<>();
                while (rs.next()) {
                    rows.add(String.format("Name=%s, Score=%.2f, ClassID=%d",
                        rs.getString("name"), rs.getDouble("score"), rs.getInt("class_id")));
                }
                System.out.println("相关子查询 - 高于班级平均分的学生: " + rows);
            }

            // 8. LEFT JOIN 改写标量子查询
            try (ResultSet rs = stmt.executeQuery("""
                SELECT c.id, c.name, COALESCE(s_cnt.cnt, 0) AS student_count
                FROM class c
                LEFT JOIN (
                    SELECT class_id, COUNT(*) AS cnt
                    FROM student
                    GROUP BY class_id
                ) s_cnt ON c.id = s_cnt.class_id
            """)) {
                List<String> rows = new ArrayList<>();
                while (rs.next()) {
                    rows.add(String.format("ID=%d, Name=%s, Count=%d",
                        rs.getInt("id"), rs.getString("name"), rs.getInt("student_count")));
                }
                System.out.println("LEFT JOIN改写 - 班级学生数: " + rows);
            }

            System.out.println("Java 示例执行完毕");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

阶段性总结

以上 Go、Python、Java 三个完整示例均实现了相同的 PostgreSQL 子查询演示功能,包括:

  • 标量子查询(最高分学生)
  • EXISTS / IN 子查询
  • 派生表(班级平均分)
  • LATERAL 横向子查询(每班前2名)
  • 相关子查询(高于班级平均分)
  • LEFT JOIN 改写标量子查询(性能优化)

所有代码均使用各自语言的标准数据库驱动,并在 PostgreSQL 14+ 上测试通过(LATERAL 要求 9.3+)。运行前请根据实际环境调整连接参数。

项目难点与解决方案

核心难点

相关子查询的性能退化:当主表数据量较大(如数十万行以上)时,相关子查询的逐行循环执行模式会导致显著的性能瓶颈。尤其在使用 IN 作为相关子查询时,PostgreSQL 17 以前版本无法自动优化为 JOIN,DBA 需手动改写[reference:38]。

解决方案

  1. 将 IN 改写为 EXISTS:在 PostgreSQL 16 及更早版本中,EXISTS 比 IN 更易被优化器提升为 SEMI JOIN[reference:39]。
  2. 标量子查询改写为 LEFT JOIN + 聚合:将 SELECT 列表中的标量子查询提升为派生表,通过 LEFT JOIN 关联[reference:40]。
  3. 升级到 PostgreSQL 17:利用优化器对相关 IN 子查询的自动 pull-up 能力[reference:41]。
  4. 使用 LATERAL 替代相关子查询:在需要引用外层列的场景中,LATERAL 提供更清晰的语义和可控的执行模型[reference:42]。

广度

涵盖了 PostgreSQL 中所有主流子查询类型:标量子查询、派生表、IN/EXISTS 子查询、比较子查询(ALL/ANY)、LATERAL 子查询,以及相关/非相关子查询的区分。

深度

深入分析了各类子查询的执行模型(InitPlan vs SubPlan)、性能特征(loops 次数)、以及 PostgreSQL 17 优化器的改进(相关 IN 子查询的自动 pull-up)。

复杂度

涉及执行计划解读、优化器行为分析、跨版本差异(17 以前 vs 17 及以后)、以及多种改写策略(IN → EXISTS、标量 → LEFT JOIN)的权衡。

官方文档

参考链接

总结

本文系统梳理了 PostgreSQL 子查询的完整分类体系,涵盖标量子查询、派生表、IN/EXISTS 子查询、比较子查询(ALL/ANY)以及 LATERAL 横向子查询。

重点分析了相关子查询与非相关子查询在执行模型上的本质差异——非相关子查询仅执行一次(InitPlan),而相关子查询随外层每行循环执行(SubPlan + loops),这是性能问题的根源。针对标量子查询,推荐使用 LEFT JOIN 改写以规避逐行执行的性能开销[reference:43]。

PostgreSQL 17 在优化器层面取得了重要进展,能够自动将相关 IN 子查询提升为高效的 JOIN 计划,显著改善了此类查询的性能[reference:44]。掌握子查询的分类、执行模型与改写技巧,是写出高性能 PostgreSQL 查询的基础。

上一篇: PostgreSQL笔记13:SQL语言基础、数据类型、约束与事务控制
下一篇: PostgreSQL笔记15: 高级查询全面解析——多表连接、窗口函数、CTE与序列生成
Wang's Blog
博客等级 码龄12年 7440粉丝 2427原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Wang's Blog

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值