数据库-连接查询

mysql-连接查询

内连接

INNER JOIN：返回两个表中满足连接条件的匹配行。如果某行在另一个表中没有匹配，则不会出现在结果中。

外连接

LEFT JOIN/LEFT OUTER JOIN：返回左表中的所有行，即使右表中没有匹配行。如果右表中没有匹配，则右表列显示 NULL。

RIGHT JOIN/RIGHT OUTER JOIN：返回右表中的所有行，即使左表中没有匹配行。如果左表中没有匹配，则左表列显示 NULL。

FULL JOIN/FULL OUTER JOIN：返回左表和右表中的所有行。当某行在另一表中没有匹配时，另一表的列显示 NULL。注：MySQL 不直接支持 FULL OUTER JOIN，但可通过 UNION 模拟。

交叉连接

CROSS JOIN：返回两个表的笛卡尔积，即左表的每一行与右表的每一行组合。结果行数 = 左表行数 × 右表行数。

自连接

SELF JOIN：将表与自身连接，通常用于比较同一表中的行（如查找上下级关系）。注：在 SQL 中，没有名为 SELF JOIN 的独立命令或关键字。自连接（Self Join）只是一种概念，指的是将一张表与自身进行连接的操作，实现时仍然使用标准的 JOIN 语法（如 INNER JOIN、LEFT JOIN 等），关键在于为同一张表指定两个不同的别名。

例子：某公司数据库包含两张表：department（部门表）和 employee（员工表）。员工属于某个部门，且每个员工可能有一个经理（经理也是员工）。请根据以下表结构和数据，使用 SQL 完成各类连接查询，并展示结果。

-- 创建部门表
CREATE TABLE department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

-- 创建员工表
CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    manager_id INT,
    FOREIGN KEY (dept_id) REFERENCES department(dept_id) ON DELETE SET NULL,
    FOREIGN KEY (manager_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);

-- 插入部门数据
INSERT INTO department (dept_id, dept_name) VALUES
(1, '研发部'),
(2, '市场部'),
(3, '财务部'),
(4, '人事部');

-- 插入员工数据
INSERT INTO employee (emp_id, emp_name, dept_id, manager_id) VALUES
(1, '张三', 1, NULL),
(2, '李四', 1, 1),
(3, '王五', 2, NULL),
(4, '赵六', 2, 3),
(5, '钱七', NULL, NULL),
(6, '孙八', 4, NULL);

内连接：列出所有员工的姓名及其所属部门名称（只显示有部门的员工）。

SELECT e.emp_name, d.dept_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

外连接：

左外连接（LEFT JOIN）：列出所有员工及其所属部门，包括未分配部门的员工。

SELECT e.emp_name, d.dept_name
FROM employee e
LEFT JOIN department d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

右外连接（RIGHT JOIN）：列出所有部门及其员工，包括空部门。

SELECT e.emp_name, d.dept_name
FROM employee e
RIGHT JOIN department d ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

全外连接：列出所有员工和所有部门，显示员工与部门的归属关系，包括未分配部门的员工和空部门。

-- 左连接：所有员工
SELECT e.emp_name, d.dept_name
FROM employee e
LEFT JOIN department d ON e.dept_id = d.dept_id

UNION

-- 右连接（只取未匹配上的部门记录）
SELECT e.emp_name, d.dept_name
FROM employee e
RIGHT JOIN department d ON e.dept_id = d.dept_id
WHERE e.dept_id IS NULL
ORDER BY emp_name, dept_name;

交叉连接：生成所有员工与所有部门的可能组合（笛卡尔积）。

SELECT e.emp_name, d.dept_name
FROM employee e
CROSS JOIN department d
ORDER BY e.emp_id, d.dept_id;

自连接：列出每位员工及其经理的姓名（经理也是员工，通过 manager_id 关联）。

SELECT 
    e.emp_name AS 员工姓名,
    m.emp_name AS 经理姓名
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.emp_id
ORDER BY e.emp_id;