在SQL中,连接3个表的查询是常见的操作,下面我将介绍几种常用的连接方式及其示例。
假设我们有3个表:
- employees
(员工表)
- departments
(部门表)
- projects
(项目表)
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
dept_id INT,
salary DECIMAL(10,2)
);
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100),
location VARCHAR(100)
);
CREATE TABLE projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(100),
emp_id INT,
start_date DATE,
end_date DATE
);
SELECT
e.emp_id,
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_id,
e.emp_name,
d.dept_name,
p.project_name
FROM
employees e
LEFT JOIN
departments d ON e.dept_id = d.dept_id
LEFT JOIN
projects p ON e.emp_id = p.emp_id;
SELECT
e.emp_id,
e.emp_name,
d.dept_name,
p.project_name
FROM
employees e
INNER JOIN
departments d ON e.dept_id = d.dept_id
LEFT JOIN
projects p ON e.emp_id = p.emp_id;
SELECT
e.emp_id,
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
WHERE
d.location = 'New York'
AND p.start_date > '2023-01-01';
SELECT
d.dept_name,
COUNT(e.emp_id) AS employee_count,
COUNT(p.project_id) AS project_count,
AVG(e.salary) AS avg_salary
FROM
departments d
LEFT JOIN
employees e ON d.dept_id = e.dept_id
LEFT JOIN
projects p ON e.emp_id = p.emp_id
GROUP BY
d.dept_name;
SELECT
e.emp_id,
e.emp_name,
d.dept_name,
p.project_name
FROM
employees e,
departments d,
projects p
WHERE
e.dept_id = d.dept_id
AND e.emp_id = p.emp_id;
虽然这种语法仍然有效,但推荐使用显式的JOIN语法,因为它更清晰且更容易理解连接条件。