首页 › 数据库综合论坛 › SQL中Join关联类型及实战案例小结

陈北海 發表於 2025-9-11 10:26:42

SQL中Join关联类型及实战案例小结

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、Join关联类型概述</a></li><li><a href="#_label1">二、内连接（INNER JOIN）</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">1. 技术原理</a></li><li><a href="#_lab2_1_1">2. 案例与代码实现</a></li></ul><li><a href="#_label2">三、左连接（LEFT JOIN/LEFT OUTER JOIN）</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_2">1. 技术原理</a></li><li><a href="#_lab2_2_3">2. 案例与代码实现</a></li></ul><li><a href="#_label3">四、右连接（RIGHT JOIN/RIGHT OUTER JOIN）</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_4">1. 技术原理</a></li><li><a href="#_lab2_3_5">2. 案例与代码实现</a></li></ul><li><a href="#_label4">五、全连接（FULL JOIN/FULL OUTER JOIN）</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_6">1. 技术原理</a></li><li><a href="#_lab2_4_7">2. 案例与代码实现</a></li></ul><li><a href="#_label5">六、交叉连接（CROSS JOIN）</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_8">1. 技术原理</a></li><li><a href="#_lab2_5_9">2. 案例与代码实现</a></li></ul><li><a href="#_label6">七、自连接（SELF JOIN）</a></li><ul class="second_class_ul"><li><a href="#_lab2_6_10">1. 技术原理</a></li><li><a href="#_lab2_6_11">2. 案例与代码实现</a></li></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>一、Join关联类型概述</h2>
<p>在SQL查询中，Join用于将两个或多个表中的记录根据关联字段组合起来，是关系型数据库中数据查询的核心操作之一。不同的Join类型决定了如何处理两个表中不匹配的记录，其在数据整合、多表查询场景中发挥着关键作用，是数据分析、业务系统开发中必须掌握的基础技术点。</p>
<p class="maodian"><a name="_label1"></a></p><h2>二、内连接（INNER JOIN）</h2>
<p class="maodian"><a name="_lab2_1_0"></a></p><p class="maodian"><a name="_lab2_2_2"></a></p><p class="maodian"><a name="_lab2_3_4"></a></p><p class="maodian"><a name="_lab2_4_6"></a></p><p class="maodian"><a name="_lab2_5_8"></a></p><p class="maodian"><a name="_lab2_6_10"></a></p><h3>1. 技术原理</h3>
<p>内连接是最常用的Join类型之一，它只返回两个表中关联字段匹配的记录。即只有当两个表中的行在连接条件上满足匹配关系时，才会被包含在结果集中，不匹配的行将被过滤掉。</p>
<p class="maodian"><a name="_lab2_1_1"></a></p><p class="maodian"><a name="_lab2_2_3"></a></p><p class="maodian"><a name="_lab2_3_5"></a></p><p class="maodian"><a name="_lab2_4_7"></a></p><p class="maodian"><a name="_lab2_5_9"></a></p><p class="maodian"><a name="_lab2_6_11"></a></p><h3>2. 案例与代码实现</h3>
<p><strong>案例场景</strong>：现有学生表（students）和成绩表（scores），需查询有成绩记录的学生信息及对应的成绩。</p>
<ul><li>学生表（students）结构：id（学生ID）、name（学生姓名）</li><li>成绩表（scores）结构：id（成绩ID）、student_id（关联学生ID）、score（成绩）</li></ul>
<p><strong>代码实现</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 创建学生表并插入数据
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);
INSERT INTO students (id, name) VALUES
(1, '张三'),
(2, '李四'),
(3, '王五');
-- 创建成绩表并插入数据
CREATE TABLE scores (
    id INT PRIMARY KEY,
    student_id INT,
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);
INSERT INTO scores (id, student_id, score) VALUES
(1, 1, 90),
(2, 2, 85);
-- 内连接查询
SELECT s.id, s.name, sc.score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id;</pre></div>
<p><strong>查询结果</strong>：</p>
id	name	score
1	张三	90
2	李四	85
<p class="maodian"><a name="_label2"></a></p><h2>三、左连接（LEFT JOIN/LEFT OUTER JOIN）</h2>
<h3>1. 技术原理</h3>
<p>左连接以左表为基准，返回左表中的所有记录，以及右表中与左表关联字段匹配的记录。如果右表中没有匹配的记录，则结果集中右表的对应字段将显示为NULL。</p>
<h3>2. 案例与代码实现</h3>
<p><strong>案例场景</strong>：使用上述学生表和成绩表，查询所有学生的信息及对应的成绩（包括没有成绩的学生）。</p>
<p><strong>代码实现</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 左连接查询
SELECT s.id, s.name, sc.score
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id;</pre></div>
<p><strong>查询结果</strong>：</p>
id	name	score
1	张三	90
2	李四	85
3	王五	NULL
<p class="maodian"><a name="_label3"></a></p><h2>四、右连接（RIGHT JOIN/RIGHT OUTER JOIN）</h2>
<h3>1. 技术原理</h3>
<p>右连接与左连接逻辑相反，以右表为基准，返回右表中的所有记录，以及左表中与右表关联字段匹配的记录。若左表中无匹配记录，左表对应字段显示为NULL。</p>
<h3>2. 案例与代码实现</h3>
<p><strong>案例场景</strong>：使用上述学生表和成绩表，查询所有成绩记录及对应的学生信息（若成绩表中有无效学生ID，也会显示）。</p>
<p><strong>代码实现</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 向成绩表插入一条无效学生ID的记录
INSERT INTO scores (id, student_id, score) VALUES
(3, 4, 70);
-- 右连接查询
SELECT s.id, s.name, sc.score
FROM students s
RIGHT JOIN scores sc ON s.id = sc.student_id;</pre></div>
<p><strong>查询结果</strong>：</p>
id	name	score
1	张三	90
2	李四	85
NULL	NULL	70
<p class="maodian"><a name="_label4"></a></p><h2>五、全连接（FULL JOIN/FULL OUTER JOIN）</h2>
<h3>1. 技术原理</h3>
<p>全连接返回左表和右表中的所有记录，当两个表中的记录匹配时，显示组合后的结果；当不匹配时，未匹配的一侧字段显示为NULL。需要注意的是，某些数据库（如MySQL）不直接支持FULL JOIN，可通过左连接和右连接结合UNION实现。</p>
<h3>2. 案例与代码实现</h3>
<p><strong>案例场景</strong>：查询所有学生和所有成绩记录的关联信息，包括没有成绩的学生和没有对应有效学生的成绩。</p>
<p><strong>代码实现（以支持FULL JOIN的数据库为例）</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT s.id, s.name, sc.score
FROM students s
FULL JOIN scores sc ON s.id = sc.student_id;</pre></div>
<p><strong>MySQL中实现全连接</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT s.id, s.name, sc.score
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
UNION
SELECT s.id, s.name, sc.score
FROM students s
RIGHT JOIN scores sc ON s.id = sc.student_id;</pre></div>
<p><strong>查询结果</strong>：</p>
id	name	score
1	张三	90
2	李四	85
3	王五	NULL
NULL	NULL	70
<p class="maodian"><a name="_label5"></a></p><h2>六、交叉连接（CROSS JOIN）</h2>
<h3>1. 技术原理</h3>
<p>交叉连接会返回两个表中所有可能的记录组合，即左表的每一行与右表的每一行都将形成一条新记录，结果集的行数为两个表行数的乘积，通常需要配合WHERE子句筛选有意义的数据。</p>
<h3>2. 案例与代码实现</h3>
<p><strong>案例场景</strong>：查询学生表和课程表（courses）的所有可能组合，用于生成学生选课初始列表。</p>
<ul><li>课程表（courses）结构：id（课程ID）、name（课程名称）</li></ul>
<p><strong>代码实现</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 创建课程表并插入数据
CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);
INSERT INTO courses (id, name) VALUES
(1, '数学'),
(2, '语文');
-- 交叉连接查询
SELECT s.name AS student_name, c.name AS course_name
FROM students s
CROSS JOIN courses c;</pre></div>
<p><strong>查询结果</strong>：</p>
student_name	course_name
张三	数学
张三	语文
李四	数学
李四	语文
王五	数学
王五	语文
<p class="maodian"><a name="_label6"></a></p><h2>七、自连接（SELF JOIN）</h2>
<h3>1. 技术原理</h3>
<p>自连接是指表与自身进行连接，将一张表当作两张不同的表来处理，通常用于查询表中具有层级关系或关联关系的记录，如员工表中查询员工及其所属领导信息。</p>
<h3>2. 案例与代码实现</h3>
<p><strong>案例场景</strong>：在员工表（employees）中，查询每个员工及其直接领导的姓名。</p>
<ul><li>员工表（employees）结构：id（员工ID）、name（员工姓名）、manager_id（领导ID，关联自身id）</li></ul>
<p><strong>代码实现</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 创建员工表并插入数据
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);
INSERT INTO employees (id, name, manager_id) VALUES
(1, '老板', NULL),
(2, '员工A', 1),
(3, '员工B', 1),
(4, '员工C', 2);
-- 自连接查询
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;</pre></div>
<p><strong>查询结果</strong>：</p>
employee_name	manager_name
老板	NULL
员工A	老板
员工B	老板
员工C	员工A

查看完整版本: SQL中Join关联类型及实战案例小结