mysql full outer join

-- MySQL本身并不直接支持FULL OUTER JOIN，但可以通过UNION结合LEFT JOIN和RIGHT JOIN来实现类似的效果。

-- 示例表1: employees
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 示例表2: departments
CREATE TABLE departments (
    id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

-- 插入一些示例数据
INSERT INTO employees (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO departments (id, department_name) VALUES (2, 'HR'), (3, 'Engineering'), (4, 'Marketing');

-- 模拟FULL OUTER JOIN
SELECT e.id AS employee_id, e.name, d.id AS department_id, d.department_name
FROM employees e
LEFT JOIN departments d ON e.id = d.id

UNION

SELECT e.id AS employee_id, e.name, d.id AS department_id, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.id = d.id;

-- 解释:
-- 1. 第一部分使用LEFT JOIN获取所有employees记录，并匹配departments中的记录。
-- 2. 第二部分使用RIGHT JOIN获取所有departments记录，并匹配employees中的记录。
-- 3. 使用UNION将两个结果集合并，从而模拟FULL OUTER JOIN的效果。