PostgreSQL教程FG030-PG SELECT查询进阶：多表连接（JOIN）全类型实操

目录大纲

Part01-基础概念与理论知识
- 1.1 JOIN的概念与类型
- 1.2 JOIN的工作原理
Part02-生产环境规划与建议
- 2.1 JOIN类型的选择原则
- 2.2 JOIN操作的性能考虑
Part03-生产环境项目实施方案
Part04-生产案例与实战讲解
- 4.1 多表连接实战案例
- 4.2 复杂连接查询优化
Part05-风哥经验总结与分享
- 5.1 JOIN操作的性能优化
- 5.2 常见问题与解决方案

Part01-基础概念与理论知识

1.1 JOIN的概念与类型

JOIN是PostgreSQL中用于将多个表的数据连接在一起的操作。PostgreSQL支持以下JOIN类型：

INNER JOIN：内连接，只返回两个表中匹配的行
LEFT JOIN（或LEFT OUTER JOIN）：左连接，返回左表的所有行和右表中匹配的行
RIGHT JOIN（或RIGHT OUTER JOIN）：右连接，返回右表的所有行和左表中匹配的行
FULL JOIN（或FULL OUTER JOIN）：全连接，返回两个表中的所有行，匹配的行合并，不匹配的行用NULL填充
CROSS JOIN：交叉连接，返回两个表的笛卡尔积
SELF JOIN：自连接，将表与自身连接

学习交流加群风哥微信: itpux-com

1.2 JOIN的工作原理

JOIN操作的工作原理是基于两个表之间的关联条件，将满足条件的行连接在一起。具体步骤如下：

根据连接条件，从两个表中选择满足条件的行
将这些行组合成新的结果集
返回组合后的结果集

不同类型的JOIN在处理不匹配的行时有所不同：

INNER JOIN：只返回匹配的行
LEFT JOIN：返回左表所有行，右表不匹配的行用NULL填充
RIGHT JOIN：返回右表所有行，左表不匹配的行用NULL填充
FULL JOIN：返回两个表的所有行，不匹配的行用NULL填充

更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 JOIN类型的选择原则

选择合适的JOIN类型需要考虑以下因素：

数据需求：根据业务需求确定需要哪些数据
表关系：了解表之间的关系（一对一、一对多、多对多）
性能考虑：不同JOIN类型的性能有所不同
结果集大小：考虑JOIN后的结果集大小

JOIN类型选择建议：

INNER JOIN：当只需要两个表中匹配的数据时使用
LEFT JOIN：当需要左表的所有数据，即使右表中没有匹配时使用
RIGHT JOIN：当需要右表的所有数据，即使左表中没有匹配时使用
FULL JOIN：当需要两个表的所有数据时使用
CROSS JOIN：当需要两个表的笛卡尔积时使用
SELF JOIN：当需要将表与自身进行比较时使用

风哥提示：在生产环境中，应根据实际业务需求选择合适的JOIN类型，避免使用不必要的JOIN操作。

2.2 JOIN操作的性能考虑

JOIN操作的性能影响因素：

表的大小：表越大，JOIN操作越慢
连接条件：连接条件的复杂度和索引情况
JOIN类型：不同JOIN类型的性能有所不同
查询复杂度：多个JOIN操作的组合

性能优化建议：

为连接条件创建索引
使用合适的JOIN类型
避免不必要的JOIN操作
合理使用子查询和临时表
监控JOIN操作的执行计划

更多学习教程公众号风哥教程itpux_com

Part03-生产环境项目实施方案

3.1 INNER JOIN的使用

3.1.1 基本INNER JOIN

— 创建部门表
CREATE TABLE fgedu_departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
manager VARCHAR(100)
);

— 创建员工表
CREATE TABLE fgedu_employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department_id INTEGER REFERENCES fgedu_departments(id),
salary DECIMAL(10, 2),
hire_date DATE
);

— 插入测试数据
INSERT INTO fgedu_departments (name, manager) VALUES
(‘技术部’, ‘张三’),
(‘市场部’, ‘李四’),
(‘财务部’, ‘王五’);

INSERT INTO fgedu_employees (name, department_id, salary, hire_date) VALUES
(‘赵六’, 1, 10000.00, ‘2024-01-01’),
(‘钱七’, 1, 12000.00, ‘2024-02-01’),
(‘孙八’, 2, 8000.00, ‘2024-03-01’),
(‘周九’, 2, 9000.00, ‘2024-04-01’),
(‘吴十’, 3, 9500.00, ‘2024-05-01’);

— 使用INNER JOIN查询员工和部门信息
SELECT
e.id AS employee_id,
e.name AS employee_name,
d.name AS department_name,
d.manager,
e.salary,
e.hire_date
FROM fgedu_employees e
INNER JOIN fgedu_departments d ON e.department_id = d.id;

3.1.2 多表INNER JOIN

— 创建项目表
CREATE TABLE fgedu_projects (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department_id INTEGER REFERENCES fgedu_departments(id),
start_date DATE,
end_date DATE
);

— 插入测试数据
INSERT INTO fgedu_projects (name, department_id, start_date, end_date) VALUES
(‘项目A’, 1, ‘2024-01-01’, ‘2024-06-30’),
(‘项目B’, 1, ‘2024-02-01’, ‘2024-07-31’),
(‘项目C’, 2, ‘2024-03-01’, ‘2024-08-31’),
(‘项目D’, 3, ‘2024-04-01’, ‘2024-09-30’);

— 多表INNER JOIN
SELECT
e.name AS employee_name,
d.name AS department_name,
p.name AS project_name,
p.start_date,
p.end_date
FROM fgedu_employees e
INNER JOIN fgedu_departments d ON e.department_id = d.id
INNER JOIN fgedu_projects p ON d.id = p.department_id;

from PostgreSQL视频:www.itpux.com

3.2 OUTER JOIN的使用

3.2.1 LEFT JOIN

— LEFT JOIN：返回左表所有行，右表匹配的行
SELECT
d.name AS department_name,
d.manager,
e.name AS employee_name,
e.salary
FROM fgedu_departments d
LEFT JOIN fgedu_employees e ON d.id = e.department_id;

— LEFT JOIN with WHERE子句
SELECT
d.name AS department_name,
e.name AS employee_name,
e.salary
FROM fgedu_departments d
LEFT JOIN fgedu_employees e ON d.id = e.department_id AND e.salary > 10000;

3.2.2 RIGHT JOIN

— RIGHT JOIN：返回右表所有行，左表匹配的行
SELECT
d.name AS department_name,
e.name AS employee_name,
e.salary
FROM fgedu_departments d
RIGHT JOIN fgedu_employees e ON d.id = e.department_id;

3.2.3 FULL JOIN

— FULL JOIN：返回两个表的所有行
SELECT
d.name AS department_name,
e.name AS employee_name,
e.salary
FROM fgedu_departments d
FULL JOIN fgedu_employees e ON d.id = e.department_id;

学习交流加群风哥QQ113257174

3.3 CROSS JOIN与SELF JOIN的使用

3.3.1 CROSS JOIN

— CROSS JOIN：返回两个表的笛卡尔积
SELECT
d.name AS department_name,
e.name AS employee_name
FROM fgedu_departments d
CROSS JOIN fgedu_employees e;

— 使用CROSS JOIN生成日期序列
CREATE TABLE fgedu_dates (
date DATE
);

INSERT INTO fgedu_dates (date) VALUES
(‘2026-04-01’),
(‘2026-04-02’),
(‘2026-04-03’);

CREATE TABLE fgedu_hours (
hour INTEGER
);

INSERT INTO fgedu_hours (hour) VALUES (9), (10), (11), (14), (15), (16);

— 生成时间槽
SELECT
d.date,
h.hour,
d.date + (h.hour || ‘:00:00’)::time AS time_slot
FROM fgedu_dates d
CROSS JOIN fgedu_hours h
ORDER BY d.date, h.hour;

3.3.2 SELF JOIN

— SELF JOIN：将表与自身连接
— 创建员工表（包含上级ID）
CREATE TABLE fgedu_employees_hierarchy (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
manager_id INTEGER REFERENCES fgedu_employees_hierarchy(id),
department VARCHAR(100),
salary DECIMAL(10, 2)
);

— 插入测试数据
INSERT INTO fgedu_employees_hierarchy (name, manager_id, department, salary) VALUES
(‘张三’, NULL, ‘技术部’, 20000.00),
(‘李四’, 1, ‘技术部’, 15000.00),
(‘王五’, 1, ‘技术部’, 12000.00),
(‘赵六’, NULL, ‘市场部’, 18000.00),
(‘钱七’, 4, ‘市场部’, 10000.00);

— 使用SELF JOIN查询员工和其上级
SELECT
e.name AS employee_name,
e.department,
e.salary,
m.name AS manager_name
FROM fgedu_employees_hierarchy e
LEFT JOIN fgedu_employees_hierarchy m ON e.manager_id = m.id;

Part04-生产案例与实战讲解

4.1 多表连接实战案例

4.1.1 订单系统查询

— 创建客户表
CREATE TABLE fgedu_customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20)
);

— 创建订单表
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES fgedu_customers(id),
order_date DATE NOT NULL,
total_amount DECIMAL(10, 2)
);

— 创建订单项目表
CREATE TABLE fgedu_order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES fgedu_orders(id),
product_name VARCHAR(100),
quantity INTEGER,
unit_price DECIMAL(10, 2)
);

— 插入测试数据
INSERT INTO fgedu_customers (name, email, phone) VALUES
(‘张三’, ‘zhangsan@fgedu.net.cn’, ‘13800138001’),
(‘李四’, ‘lisi@fgedu.net.cn’, ‘13800138002’),
(‘王五’, ‘wangwu@fgedu.net.cn’, ‘13800138003’);

INSERT INTO fgedu_orders (customer_id, order_date, total_amount) VALUES
(1, ‘2026-04-01’, 2000.00),
(1, ‘2026-04-05’, 1500.00),
(2, ‘2026-04-02’, 3000.00),
(3, ‘2026-04-03’, 2500.00);

INSERT INTO fgedu_order_items (order_id, product_name, quantity, unit_price) VALUES
(1, ‘手机’, 1, 1500.00),
(1, ‘耳机’, 2, 250.00),
(2, ‘平板’, 1, 1500.00),
(3, ‘电脑’, 1, 3000.00),
(4, ‘键盘’, 2, 500.00),
(4, ‘鼠标’, 1, 1500.00);

— 多表连接查询：客户订单详情
SELECT
c.name AS customer_name,
c.email,
c.phone,
o.id AS order_id,
o.order_date,
o.total_amount,
oi.product_name,
oi.quantity,
oi.unit_price,
oi.quantity * oi.unit_price AS item_total
FROM fgedu_customers c
INNER JOIN fgedu_orders o ON c.id = o.customer_id
INNER JOIN fgedu_order_items oi ON o.id = oi.order_id
ORDER BY c.name, o.order_date, oi.product_name;

— 左连接查询：所有客户的订单情况
SELECT
c.name AS customer_name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM fgedu_customers c
LEFT JOIN fgedu_orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC;

4.1.2 库存管理查询

— 创建产品表
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(10, 2)
);

— 创建库存表
CREATE TABLE fgedu_inventory (
product_id INTEGER REFERENCES fgedu_products(id),
warehouse_id INTEGER,
quantity INTEGER,
PRIMARY KEY (product_id, warehouse_id)
);

— 创建仓库表
CREATE TABLE fgedu_warehouses (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
location VARCHAR(100)
);

— 插入测试数据
INSERT INTO fgedu_products (name, category, price) VALUES
(‘手机’, ‘电子产品’, 5999.00),
(‘电脑’, ‘电子产品’, 9999.00),
(‘平板’, ‘电子产品’, 3999.00),
(‘耳机’, ‘电子产品’, 999.00),
(‘键盘’, ‘电脑配件’, 1999.00);

INSERT INTO fgedu_warehouses (name, location) VALUES
(‘仓库A’, ‘北京’),
(‘仓库B’, ‘上海’),
(‘仓库C’, ‘广州’);

INSERT INTO fgedu_inventory (product_id, warehouse_id, quantity) VALUES
(1, 1, 50),
(1, 2, 30),
(2, 1, 20),
(2, 3, 15),
(3, 2, 40),
(4, 1, 100),
(4, 2, 80),
(4, 3, 60);

— 多表连接查询：产品库存情况
SELECT
p.name AS product_name,
p.category,
p.price,
w.name AS warehouse_name,
w.location,
i.quantity
FROM fgedu_products p
LEFT JOIN fgedu_inventory i ON p.id = i.product_id
LEFT JOIN fgedu_warehouses w ON i.warehouse_id = w.id
ORDER BY p.category, p.name, w.name;

— 计算每个产品的总库存
SELECT
p.name AS product_name,
SUM(i.quantity) AS total_quantity
FROM fgedu_products p
LEFT JOIN fgedu_inventory i ON p.id = i.product_id
GROUP BY p.id, p.name
ORDER BY total_quantity DESC;

风哥提示：在复杂的多表连接查询中，合理使用表别名可以提高查询的可读性。

4.2 复杂连接查询优化

4.2.1 使用索引优化JOIN

— 为连接列创建索引
CREATE INDEX idx_employees_department_id ON fgedu_employees(department_id);
CREATE INDEX idx_orders_customer_id ON fgedu_orders(customer_id);
CREATE INDEX idx_order_items_order_id ON fgedu_order_items(order_id);

— 分析表以更新统计信息
ANALYZE fgedu_employees;
ANALYZE fgedu_orders;
ANALYZE fgedu_order_items;

— 查看查询执行计划
EXPLAIN ANALYZE
SELECT
e.name AS employee_name,
d.name AS department_name,
d.manager
FROM fgedu_employees e
INNER JOIN fgedu_departments d ON e.department_id = d.id
WHERE e.salary > 10000;

4.2.2 使用子查询优化JOIN

— 使用子查询减少JOIN的数据量
SELECT
c.name AS customer_name,
o.order_date,
o.total_amount,
oi.item_count
FROM fgedu_customers c
INNER JOIN fgedu_orders o ON c.id = o.customer_id
INNER JOIN (
SELECT
order_id,
COUNT(*) AS item_count
FROM fgedu_order_items
GROUP BY order_id
) oi ON o.id = oi.order_id
WHERE o.order_date >= ‘2026-04-01’;

— 使用CTE（公共表表达式）优化复杂查询
WITH order_summary AS (
SELECT
order_id,
COUNT(*) AS item_count,
SUM(quantity * unit_price) AS calculated_total
FROM fgedu_order_items
GROUP BY order_id
)
SELECT
c.name AS customer_name,
o.id AS order_id,
o.order_date,
o.total_amount,
os.item_count,
os.calculated_total,
CASE
WHEN o.total_amount = os.calculated_total THEN ‘匹配’
ELSE ‘不匹配’
END AS amount_match
FROM fgedu_customers c
INNER JOIN fgedu_orders o ON c.id = o.customer_id
INNER JOIN order_summary os ON o.id = os.order_id;