详细介绍：mysql表的链接

在 MySQL 中，表连接（JOIN）是用于从多个表中根据关联字段提取数据的核心操作。根据业务需求不同，主要分为内连接、外连接、交叉连接、自连接 四类，以下是详细说明和示例：

准备示例表

先创建两个测试表，方便后续演示：

sql

-- 商品分类表
CREATE TABLE category (
  cid INT PRIMARY KEY,
  cname VARCHAR(50) NOT NULL -- 分类名称
);
-- 商品表
CREATE TABLE product (
  pid INT PRIMARY KEY,
  pname VARCHAR(50) NOT NULL,
  price DECIMAL(10,2),
  cid INT, -- 关联分类表的cid
  FOREIGN KEY (cid) REFERENCES category(cid)
);
-- 插入测试数据
INSERT INTO category VALUES (1, '电子产品'), (2, '生活用品'), (3, '食品');
INSERT INTO product VALUES
(1, '手机', 2999.00, 1),
(2, '牙刷', 9.90, 2),
(3, '面包', 5.80, 3),
(4, '耳机', 199.00, 1),
(5, '毛巾', 15.90, 2),
(6, '薯片', 8.50, NULL); -- 无分类的商品

一、内连接（INNER JOIN）

核心逻辑

只返回两个表中关联字段匹配成功的记录（交集），是最常用的连接方式。

语法

sql

-- 标准写法
SELECT 字段列表
FROM 表1
INNER JOIN 表2 ON 表1.关联字段 = 表2.关联字段;
-- 简化写法（等价）
SELECT 字段列表
FROM 表1, 表2
WHERE 表1.关联字段 = 表2.关联字段;

示例

查询所有有分类的商品及其分类名称：

sql

SELECT p.pid, p.pname, p.price, c.cname
FROM product p
INNER JOIN category c ON p.cid = c.cid;

结果

pid	pname	price	cname
1	手机	2999.00	电子产品
2	牙刷	9.90	生活用品
3	面包	5.80	食品
4	耳机	199.00	电子产品
5	毛巾	15.90	生活用品

二、外连接（OUTER JOIN）

返回一个表的所有记录 + 另一个表匹配的记录，未匹配的字段显示 NULL，分为左外连接、右外连接、全外连接（MySQL 不直接支持全外连接，需变通实现）。

1. 左外连接（LEFT JOIN / LEFT OUTER JOIN）

核心逻辑

返回左表的所有记录，右表匹配的记录显示，未匹配则右表字段为 NULL。

语法

sql

SELECT 字段列表
FROM 左表
LEFT JOIN 右表 ON 左表.关联字段 = 右表.关联字段;

示例

查询所有商品（包括无分类的）及其分类名称：

sql

SELECT p.pid, p.pname, p.price, c.cname
FROM product p
LEFT JOIN category c ON p.cid = c.cid;

结果

pid	pname	price	cname
1	手机	2999.00	电子产品
2	牙刷	9.90	生活用品
3	面包	5.80	食品
4	耳机	199.00	电子产品
5	毛巾	15.90	生活用品
6	薯片	8.50	NULL

2. 右外连接（RIGHT JOIN / RIGHT OUTER JOIN）

核心逻辑

返回右表的所有记录，左表匹配的记录显示，未匹配则左表字段为 NULL。

语法

sql

SELECT 字段列表
FROM 左表
RIGHT JOIN 右表 ON 左表.关联字段 = 右表.关联字段;

示例

查询所有分类（包括无商品的分类）及其商品：

sql

-- 先插入一个无商品的分类
INSERT INTO category VALUES (4, '玩具');
-- 右连接查询
SELECT p.pid, p.pname, c.cname
FROM product p
RIGHT JOIN category c ON p.cid = c.cid;

结果

pid	pname	cname
1	手机	电子产品
4	耳机	电子产品
2	牙刷	生活用品
5	毛巾	生活用品
3	面包	食品
NULL	NULL	玩具

3. 全外连接（FULL JOIN）

MySQL 不直接支持 FULL JOIN，需通过 LEFT JOIN + UNION + RIGHT JOIN 实现，返回两个表的所有记录，未匹配的字段为 NULL。

示例

sql

SELECT p.pid, p.pname, c.cname
FROM product p
LEFT JOIN category c ON p.cid = c.cid
UNION
SELECT p.pid, p.pname, c.cname
FROM product p
RIGHT JOIN category c ON p.cid = c.cid;

三、交叉连接（CROSS JOIN）

核心逻辑

返回两个表的笛卡尔积（所有组合），无关联条件时慎用（数据量 = 表 1 行数 × 表 2 行数）。

语法

sql

-- 写法1
SELECT 字段列表
FROM 表1
CROSS JOIN 表2;
-- 写法2（等价）
SELECT 字段列表
FROM 表1, 表2;

示例

sql

-- 查询分类和商品的所有组合（仅演示，实际少用）
SELECT c.cname, p.pname
FROM category c
CROSS JOIN product p
LIMIT 5; -- 限制结果数

四、自连接（SELF JOIN）

核心逻辑

将表自身作为另一个表连接，需给表起别名，用于查询表内层级关系（如部门上下级、分类父子级）。

示例

先修改分类表，增加父分类字段：

sql

-- 新增父分类字段
ALTER TABLE category ADD COLUMN parent_cid INT;
-- 更新数据：电子产品/生活用品是一级分类，手机/耳机属于电子产品子分类
UPDATE category SET parent_cid = NULL WHERE cid IN (1,2,3,4);
INSERT INTO category VALUES (5, '手机配件', NULL, 1), (6, '快充头', NULL, 5);
-- 自连接查询子分类和父分类名称
SELECT c1.cname AS 子分类, c2.cname AS 父分类
FROM category c1
LEFT JOIN category c2 ON c1.parent_cid = c2.cid;

五、连接的注意事项

1. 关联字段类型一致：关联的字段需为相同 / 兼容类型（如 INT 和 INT，VARCHAR 和 VARCHAR）。
2. 使用别名简化语句：多表连接时给表起别名（如 product p），避免字段名冲突。
3. 索引优化：关联字段（如 product.cid）建议建立索引，提升连接查询效率。
4. 避免笛卡尔积：内连接 / 外连接必须加 ON 条件，否则等价于交叉连接。
5. NULL 值处理：外连接中未匹配的字段为 NULL，可通过 IFNULL() 替换：

   sql

   SELECT p.pname, IFNULL(c.cname, '未分类') AS cname
   FROM product p
   LEFT JOIN category c ON p.cid = c.cid;

以上是 MySQL 表连接的核心用法，实际开发中需根据业务场景选择合适的连接方式，优先使用内连接和左 / 右外连接，避免不必要的笛卡尔积和全外连接。