MySQL(20)INNER JOIN和OUTER JOIN的区别是什么？

INNER JOIN 和 OUTER JOIN 是 SQL 中用于将两个或多个表的数据合并到一个结果集中的两种不同的 JOIN 类型。它们的主要区别在于它们如何处理没有匹配记录的行。

INNER JOIN

INNER JOIN 只返回在两个表中都有匹配记录的行。也就是说，只有当表A和表B中的行在连接条件上匹配时，这些行才会包含在结果集中。

示例

假设我们有两个表 employees 和 departments，结构如下：

-- 创建表 employees
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_position VARCHAR(100),
    emp_salary DECIMAL(10, 2),
    hire_date DATE,
    dept_id INT
);

-- 创建表 departments
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL
);

-- 插入示例数据
INSERT INTO employees (emp_name, emp_position, emp_salary, hire_date, dept_id)
VALUES 
('John Doe', 'Manager', 75000.00, '2023-10-01', 1),
('Jane Smith', 'Developer', 60000.00, '2023-09-01', 2),
('Alice Johnson', 'Analyst', 50000.00, '2023-08-01', NULL);

INSERT INTO departments (dept_name)
VALUES 
('HR'),
('Engineering');

使用 INNER JOIN 将只返回在 employees 和 departments 中都有匹配 dept_id 的行：

SELECT 
    e.emp_id,
    e.emp_name,
    e.emp_position,
    e.emp_salary,
    e.hire_date,
    d.dept_name
FROM 
    employees e
INNER JOIN 
    departments d ON e.dept_id = d.dept_id;

结果：

emp_id | emp_name   | emp_position | emp_salary | hire_date  | dept_name
-------|------------|--------------|------------|------------|-----------
1      | John Doe   | Manager      | 75000.00   | 2023-10-01 | HR
2      | Jane Smith | Developer    | 60000.00   | 2023-09-01 | Engineering

OUTER JOIN

OUTER JOIN 返回包括在连接条件上没有匹配的行的所有记录。OUTER JOIN 分为三种类型：

1. LEFT JOIN (LEFT OUTER JOIN): 返回左表中的所有记录，即使右表中没有匹配的记录。
2. RIGHT JOIN (RIGHT OUTER JOIN): 返回右表中的所有记录，即使左表中没有匹配的记录。
3. FULL OUTER JOIN: 返回左表和右表中的所有记录，其中没有匹配的部分显示为 NULL。（MySQL 不直接支持 FULL OUTER JOIN）

LEFT JOIN 示例

LEFT JOIN 将返回左表 employees 中的所有记录，即使右表 departments 中没有匹配的记录：

SELECT 
    e.emp_id,
    e.emp_name,
    e.emp_position,
    e.emp_salary,
    e.hire_date,
    d.dept_name
FROM 
    employees e
LEFT JOIN 
    departments d ON e.dept_id = d.dept_id;

结果：

emp_id | emp_name      | emp_position | emp_salary | hire_date  | dept_name
-------|---------------|--------------|------------|------------|-----------
1      | John Doe      | Manager      | 75000.00   | 2023-10-01 | HR
2      | Jane Smith    | Developer    | 60000.00   | 2023-09-01 | Engineering
3      | Alice Johnson | Analyst      | 50000.00   | 2023-08-01 | NULL

RIGHT JOIN 示例

RIGHT JOIN 将返回右表 departments 中的所有记录，即使左表 employees 中没有匹配的记录：

SELECT 
    e.emp_id,
    e.emp_name,
    e.emp_position,
    e.emp_salary,
    e.hire_date,
    d.dept_name
FROM 
    employees e
RIGHT JOIN 
    departments d ON e.dept_id = d.dept_id;

结果：

emp_id | emp_name   | emp_position | emp_salary | hire_date  | dept_name
-------|------------|--------------|------------|------------|-----------
1      | John Doe   | Manager      | 75000.00   | 2023-10-01 | HR
2      | Jane Smith | Developer    | 60000.00   | 2023-09-01 | Engineering
NULL   | NULL       | NULL         | NULL       | NULL       | Sales

FULL OUTER JOIN 示例

MySQL不直接支持 FULL OUTER JOIN，可以通过 UNION ALL 和 LEFT JOIN / RIGHT JOIN 组合来模拟 FULL OUTER JOIN：

SELECT 
    e.emp_id,
    e.emp_name,
    e.emp_position,
    e.emp_salary,
    e.hire_date,
    d.dept_name
FROM 
    employees e
LEFT JOIN 
    departments d ON e.dept_id = d.dept_id
UNION ALL
SELECT 
    e.emp_id,
    e.emp_name,
    e.emp_position,
    e.emp_salary,
    e.hire_date,
    d.dept_name
FROM 
    employees e
RIGHT JOIN 
    departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

结果：

emp_id | emp_name      | emp_position | emp_salary | hire_date  | dept_name
-------|---------------|--------------|------------|------------|-----------
1      | John Doe      | Manager      | 75000.00   | 2023-10-01 | HR
2      | Jane Smith    | Developer    | 60000.00   | 2023-09-01 | Engineering
3      | Alice Johnson | Analyst      | 50000.00   | 2023-08-01 | NULL
NULL   | NULL          | NULL         | NULL       | NULL       | Sales

小结

• INNER JOIN: 仅返回两个表中匹配的记录。
• LEFT JOIN: 返回左表中的所有记录，即使右表中没有匹配的记录。
• RIGHT JOIN: 返回右表中的所有记录，即使左表中没有匹配的记录。
• FULL OUTER JOIN: 返回左表和右表中的所有记录，其中没有匹配的部分显示为 NULL。（MySQL 需要通过 UNION ALL 和 LEFT JOIN / RIGHT JOIN 组合来模拟）

通过以上代码示例，可以清楚地理解 INNER JOIN 和 OUTER JOIN 的区别及其使用方法。