在实际应用中，数据通常分布在多个表中，连接查询是获取相关数据的关键技术。
连接类型概述
- INNER JOIN：内连接，返回两个表中匹配的记录
- LEFT JOIN：左连接，返回左表所有记录和右表匹配记录
- RIGHT JOIN：右连接，返回右表所有记录和左表匹配记录
- FULL OUTER JOIN：全外连接（MySQL不支持，需用UNION模拟）
- CROSS JOIN：交叉连接，返回笛卡尔积
创建示例数据
-- 员工表
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
dept_id INT,
salary DECIMAL(10,2)
);
-- 部门表
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50),
location VARCHAR(100)
);
-- 插入数据
INSERT INTO departments VALUES
(1, '技术部', '北京'),
(2, '市场部', '上海'),
(3, '人事部', '广州'),
(4, '财务部', '深圳');
INSERT INTO employees VALUES
(101, '张三', 1, 15000.00),
(102, '李四', 1, 18000.00),
(103, '王五', 2, 12000.00),
(104, '赵六', 2, 14000.00),
(105, '钱七', NULL, 16000.00),
(106, '孙八', 5, 13000.00); -- 部门ID不存在
INNER JOIN内连接
-- 基本内连接
SELECT
e.emp_id,
e.emp_name,
e.salary,
d.dept_name,
d.location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- 多表内连接
-- 假设有第三个表：项目表 projects
CREATE TABLE projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(100),
emp_id INT
);
INSERT INTO projects VALUES
(1, '电商系统开发', 101),
(2, '移动App开发', 102),
(3, '市场推广活动', 103);
-- 三表连接
SELECT
e.emp_name,
d.dept_name,
p.project_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN projects p ON e.emp_id = p.emp_id;
-- 带条件的连接
SELECT
e.emp_name,
e.salary,
d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 13000
ORDER BY e.salary DESC;
LEFT JOIN左连接
-- 左连接：所有员工，包括没有部门的
SELECT
e.emp_id,
e.emp_name,
COALESCE(d.dept_name, '未分配') AS dept_name,
COALESCE(d.location, '未知') AS location
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-- 查找没有部门的员工
SELECT
e.emp_id,
e.emp_name,
e.salary
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;
-- 左连接统计每个部门员工数
SELECT
d.dept_name,
COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY employee_count DESC;
-- 员工和项目左连接（员工可能有多个项目）
SELECT
e.emp_name,
p.project_name
FROM employees e
LEFT JOIN projects p ON e.emp_id = p.emp_id;
其他连接类型
-- RIGHT JOIN右连接
SELECT
d.dept_name,
e.emp_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
-- 查找没有员工的部门
SELECT
d.dept_name,
d.location
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;
-- 自连接：员工和经理关系
ALTER TABLE employees ADD COLUMN manager_id INT;
UPDATE employees SET manager_id = 101 WHERE emp_id IN (102, 103);
UPDATE employees SET manager_id = 103 WHERE emp_id = 104;
-- 查询员工及其经理
SELECT
e.emp_name AS employee_name,
m.emp_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
-- CROSS JOIN交叉连接
SELECT
e.emp_name,
d.dept_name
FROM employees e
CROSS JOIN departments d
LIMIT 10;
-- 模拟FULL OUTER JOIN
SELECT
e.emp_name,
d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT
e.emp_name,
d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
提示： 这是一个重要的概念，需要特别注意理解和掌握。
注意： 这是一个常见的错误点，请避免犯同样的错误。
评论
请 登录 后发表评论