{"msg":"操作成功","code":200,"data":{"createBy":"admin","createTime":"2020-03-18 21:54:22","updateBy":"admin","updateTime":"2025-06-04 12:38:46","remark":null,"id":22,"articleTitle":"MySQL（四）关联查询JOIN","articleUrl":"mysql_join","articleThumbnail":"https://www.asumimoe.com/imgfiles/20220908/9e192744fab244d39d3b15fca035c9f7.jpg","articleFlag":"0","draftStatus":"1","reprintStatement":"0","articleSummary":"关联查询（Join）是SQL中用于从多个表中组合数据的强大工具。在MySQL中，常见的关联查询包括：内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）、全外连接（FULL OUTER JOIN）、交叉连接（CROSS JOIN）、自连接（SELF JOIN）等。","articleContent":"关联查询（Join）是SQL中用于从多个表中组合数据的强大工具。在MySQL中，常见的关联查询包括：\n\n- 内连接（INNER JOIN）：返回两个表中匹配的行。\n\n- 左连接（LEFT JOIN）：返回左表的所有行，以及右表中匹配的行（如果右表无匹配，则结果为NULL）。\n\n- 右连接（RIGHT JOIN）：返回右表的所有行，以及左表中匹配的行（如果左表无匹配，则结果为NULL）。\n\n- 全外连接（FULL OUTER JOIN）：MySQL不直接支持，但可以通过UNION LEFT JOIN和RIGHT JOIN实现。\n\n- 交叉连接（CROSS JOIN）：返回两个表的笛卡尔积。\n\n- 自连接（SELF JOIN）：表与自身连接。\n\n## 创建示例数据表\n\n```sql\n-- 创建部门表\nCREATE TABLE departments (\n    dept_id INT PRIMARY KEY AUTO_INCREMENT,\n    dept_name VARCHAR(50) NOT NULL,\n    location VARCHAR(100)\n);\n\n-- 创建员工表\nCREATE TABLE employees (\n    emp_id INT PRIMARY KEY AUTO_INCREMENT,\n    emp_name VARCHAR(50) NOT NULL,\n    salary DECIMAL(10, 2),\n    hire_date DATE,\n    dept_id INT,\n    manager_id INT,\n    FOREIGN KEY (dept_id) REFERENCES departments(dept_id),\n    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)\n);\n\n-- 创建项目表\nCREATE TABLE projects (\n    project_id INT PRIMARY KEY AUTO_INCREMENT,\n    project_name VARCHAR(100) NOT NULL,\n    budget DECIMAL(15, 2),\n    start_date DATE,\n    end_date DATE\n);\n\n-- 创建员工项目关联表（多对多关系）\nCREATE TABLE employee_projects (\n    emp_id INT,\n    project_id INT,\n    role VARCHAR(50),\n    PRIMARY KEY (emp_id, project_id),\n    FOREIGN KEY (emp_id) REFERENCES employees(emp_id),\n    FOREIGN KEY (project_id) REFERENCES projects(project_id)\n);\n\n-- 插入部门数据\nINSERT INTO departments (dept_name, location) VALUES\n('技术部', 'A栋101'),\n('市场部', 'B栋201'),\n('财务部', 'C栋301'),\n('人事部', 'D栋401');\n\n-- 插入员工数据\nINSERT INTO employees (emp_name, salary, hire_date, dept_id, manager_id) VALUES\n('张三', 15000.00, '2020-01-15', 1, NULL),\n('李四', 12000.00, '2020-03-10', 1, 1),\n('王五', 18000.00, '2019-06-20', 2, NULL),\n('赵六', 9000.00, '2021-02-01', 1, 2),\n('钱七', 14000.00, '2020-11-05', 3, NULL),\n('孙八', 11000.00, '2021-04-15', 2, 3);\n\n-- 插入项目数据\nINSERT INTO projects (project_name, budget, start_date, end_date) VALUES\n('网站重构', 50000.00, '2023-01-01', '2023-06-30'),\n('市场推广', 30000.00, '2023-02-15', '2023-08-31'),\n('财务系统', 80000.00, '2023-03-10', '2023-12-31');\n\n-- 插入员工项目关联数据\nINSERT INTO employee_projects (emp_id, project_id, role) VALUES\n(1, 1, '技术负责人'),\n(2, 1, '开发工程师'),\n(4, 1, '测试工程师'),\n(3, 2, '项目经理'),\n(6, 2, '市场专员'),\n(5, 3, '财务负责人'),\n(1, 3, '系统架构师');\n```\n\n## 1. 内连接 (INNER JOIN)\n\n内连接返回两个表中匹配的行。\n\n### 基本内连接\n\n```sql\n-- 获取所有员工及其部门信息\nSELECT \n    e.emp_name AS '员工姓名',\n    d.dept_name AS '部门名称',\n    d.location AS '部门位置'\nFROM employees e\nINNER JOIN departments d ON e.dept_id = d.dept_id;\n```\n\n### 多表内连接\n\n```sql\n-- 获取员工、部门和项目信息\nSELECT \n    e.emp_name AS '员工姓名',\n    d.dept_name AS '部门名称',\n    p.project_name AS '项目名称',\n    ep.role AS '项目角色'\nFROM employees e\nINNER JOIN departments d ON e.dept_id = d.dept_id\nINNER JOIN employee_projects ep ON e.emp_id = ep.emp_id\nINNER JOIN projects p ON ep.project_id = p.project_id;\n```\n\n## 2. 左连接 (LEFT JOIN)\n\n左连接返回左表的所有行，即使右表中没有匹配的行。\n\n```sql\n-- 获取所有部门及该部门下的员工（即使部门没有员工）\nSELECT \n    d.dept_name AS '部门名称',\n    e.emp_name AS '员工姓名'\nFROM departments d\nLEFT JOIN employees e ON d.dept_id = e.dept_id;\n```\n\n## 3. 右连接 (RIGHT JOIN)\n\n右连接返回右表的所有行，即使左表中没有匹配的行。\n\n```sql\n-- 获取所有员工及其部门（即使员工没有分配部门）\nSELECT \n    e.emp_name AS '员工姓名',\n    d.dept_name AS '部门名称'\nFROM departments d\nRIGHT JOIN employees e ON d.dept_id = e.dept_id;\n```\n\n## 4. 全外连接 (FULL OUTER JOIN)\n\nMySQL不直接支持FULL OUTER JOIN，但可以通过组合LEFT JOIN和RIGHT JOIN实现。\n\n```sql\n-- 获取所有部门和所有员工（包括没有部门的员工和没有员工的部门）\nSELECT \n    d.dept_name AS '部门名称',\n    e.emp_name AS '员工姓名'\nFROM departments d\nLEFT JOIN employees e ON d.dept_id = e.dept_id\nUNION\nSELECT \n    d.dept_name AS '部门名称',\n    e.emp_name AS '员工姓名'\nFROM departments d\nRIGHT JOIN employees e ON d.dept_id = e.dept_id;\n```\n\n## 5. 交叉连接 (CROSS JOIN)\n\n交叉连接返回两个表的笛卡尔积（所有可能的组合）。\n\n```sql\n-- 获取所有可能的员工-部门组合\nSELECT \n    e.emp_name AS '员工姓名',\n    d.dept_name AS '部门名称'\nFROM employees e\nCROSS JOIN departments d;\n```\n\n## 6. 自连接 (SELF JOIN)\n\n自连接用于表与自身关联，常用于层级数据（如经理-下属关系）。\n\n```sql\n-- 获取员工及其经理信息\nSELECT \n    e.emp_name AS '员工姓名',\n    m.emp_name AS '经理姓名'\nFROM employees e\nLEFT JOIN employees m ON e.manager_id = m.emp_id;\n```\n\n## 7. 多对多关系查询\n\n```sql\n-- 获取项目及其参与人员\nSELECT \n    p.project_name AS '项目名称',\n    e.emp_name AS '参与员工',\n    ep.role AS '项目角色'\nFROM projects p\nINNER JOIN employee_projects ep ON p.project_id = ep.project_id\nINNER JOIN employees e ON ep.emp_id = e.emp_id\nORDER BY p.project_name, e.emp_name;\n```\n\n## 8. 复杂关联查询示例\n\n```sql\n-- 获取各部门项目参与情况统计\nSELECT \n    d.dept_name AS '部门名称',\n    COUNT(DISTINCT e.emp_id) AS '员工总数',\n    COUNT(DISTINCT p.project_id) AS '参与项目数',\n    SUM(p.budget) AS '项目总预算'\nFROM departments d\nLEFT JOIN employees e ON d.dept_id = e.dept_id\nLEFT JOIN employee_projects ep ON e.emp_id = ep.emp_id\nLEFT JOIN projects p ON ep.project_id = p.project_id\nGROUP BY d.dept_id\nORDER BY d.dept_name;\n```\n\n## 9. 使用WHERE子句的隐式连接\n\n```sql\n-- 获取技术部所有员工（隐式连接）\nSELECT \n    e.emp_name AS '员工姓名',\n    e.salary AS '薪资'\nFROM employees e, departments d\nWHERE e.dept_id = d.dept_id \n  AND d.dept_name = '技术部';\n```\n\n## 关联查询性能优化技巧\n\n1. **使用合适的JOIN类型**：根据需求选择最精确的JOIN类型\n\n2. **添加索引**：在连接字段上创建索引\n\n   ```sql\n   CREATE INDEX idx_dept_id ON employees(dept_id);\n   CREATE INDEX idx_manager_id ON employees(manager_id);\n   ```\n\n3. **限制结果集**：使用WHERE子句尽早过滤数据\n\n4. **避免SELECT ***：只选择需要的列\n\n5. **使用EXPLAIN分析**：查看查询执行计划\n\n   ```sql\n   EXPLAIN SELECT ...;\n   ```\n\n## 常见问题解决方案\n\n### 问题1：处理NULL值\n\n```sql\n-- 使用COALESCE处理NULL值\nSELECT \n    e.emp_name AS '员工姓名',\n    COALESCE(m.emp_name, '无') AS '经理姓名'\nFROM employees e\nLEFT JOIN employees m ON e.manager_id = m.emp_id;\n```\n\n### 问题2：避免重复结果\n\n```sql\n-- 使用DISTINCT消除重复\nSELECT DISTINCT\n    d.dept_name\nFROM departments d\nJOIN employees e ON d.dept_id = e.dept_id;\n```\n\n### 问题3：使用USING简化连接条件\n\n当连接字段名称相同时：\n\n```sql\nSELECT \n    e.emp_name,\n    d.dept_name\nFROM employees e\nJOIN departments d USING (dept_id);\n```\n","categoryId":4,"viewCount":874,"categoryName":"MySQL","author":"球接子","authorAvatar":null,"tagIds":[2,17],"tagNames":["MySQL","数据库"]}}