MySQL联结详解：自联结、自然联结、内外部联结及交叉联结

自联结

自联结是指一个表与其自身进行联结，通常用于替代复杂的子查询。例如，假设需要找出数学成绩为100分的学生的其他科目成绩：

新手可能会这样写：

SELECT student_id FROM scores WHERE subject = 'mathematics' AND score = 100;

然后使用这个学生的ID来查询其他成绩：

SELECT * FROM scores WHERE student_id = (SELECT student_id FROM scores WHERE subject = 'mathematics' AND score = 100);

而使用自联结的方式可以更简洁地实现这一需求：

SELECT s1.* FROM scores AS s1 JOIN scores AS s2 ON s1.student_id = s2.student_id WHERE s2.subject = 'mathematics' AND s2.score = 100;

自然联结

自然联结是一种特殊的联结方式，它基于两表中共有的列自动进行联结，避免了重复列的出现。虽然方便，但不建议过度依赖自然联结，因为它可能隐藏联结条件，导致不易理解的查询。

SELECT * FROM table1 NATURAL JOIN table2; -- 类似于 INNER JOIN SELECT * FROM table1 NATURAL LEFT JOIN table2; -- 类似于 LEFT JOIN SELECT * FROM table1 NATURAL RIGHT JOIN table2; -- 类似于 RIGHT JOIN

内部联结（等值联结）

内部联结是最常用的联结类型之一，它仅返回满足联结条件的行。内部联结可以通过INNER JOIN或简单的JOIN关键字来实现：

SELECT a.column1, b.column2 FROM tableA a JOIN tableB b ON a.common_column = b.common_column;

如果联结条件是基于同名列，则可以使用USING子句简化语法：

SELECT t1.id, t2.name FROM table1 t1 JOIN table2 t2 USING (common_column);

外部联结

外部联结分为左外部联结、右外部联结和全外部联结，它们分别处理不同类型的非匹配行：

• 左外部联结：即使右表中没有匹配的行，左表中的所有行也会出现在结果集中，未匹配的右表列显示为NULL。
• 右外部联结：与左外部联结相反，确保右表中的所有行都在结果集中，左表中未匹配的列显示为NULL。
• 全外部联结：返回所有表中的所有行，无论是否有匹配，结合了左外部联结和右外部联结的特点。

SELECT a.column1, b.column2 FROM tableA a LEFT JOIN tableB b ON a.common_column = b.common_column; SELECT a.column1, b.column2 FROM tableA a RIGHT JOIN tableB b ON a.common_column = b.common_column; SELECT a.column1, b.column2 FROM tableA a FULL OUTER JOIN tableB b ON a.common_column = b.common_column;

交叉联结

交叉联结（CROSS JOIN）不基于任何联结条件，而是生成两个表的笛卡尔积，即每一个表的每一行都与另一个表的每一行组合。

SELECT * FROM tableA CROSS JOIN tableB;

UNION和UNION ALL

UNION操作符用于合并两个或多个SELECT语句的结果集，自动去除重复的行；而UNION ALL则保留所有的行，包括重复行。

SELECT column1, column2 FROM table1 UNION ALL SELECT column1, column2 FROM table2;

关于联结的示意图

为了更好地理解不同类型的联结，可以参考以下示意图：