mysql left join 和join的区别

-- 示例表创建语句
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

-- 插入示例数据
INSERT INTO employees (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO departments (id, name, manager_id) VALUES
(1, 'HR', 1),
(2, 'Engineering', 2);

-- 使用 JOIN (INNER JOIN)
SELECT e.name AS employee_name, d.name AS department_name
FROM employees e
JOIN departments d ON e.id = d.manager_id;

-- 解释: 上述查询将返回所有员工和他们管理的部门，只有当员工确实管理某个部门时才会显示结果。
-- 结果:
-- employee_name | department_name
-- --------------|----------------
-- Alice         | HR
-- Bob           | Engineering

-- 使用 LEFT JOIN
SELECT e.name AS employee_name, d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.id = d.manager_id;

-- 解释: 上述查询将返回所有员工，即使他们没有管理任何部门。对于没有管理任何部门的员工，部门名称将为 NULL。
-- 结果:
-- employee_name | department_name
-- --------------|----------------
-- Alice         | HR
-- Bob           | Engineering
-- Charlie       | NULL