employees 表
departments 表
locations 表
job_grades 表
两表连接，将 employees 表的 department_id 与 departments 表的 department_id 相关联
SELECT employee_id, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
# 或
SELECT employee_id, department_name
FROM employees JOIN departments
on employees.department_id = departments.department_id;
若查询字段在多个表中都出现，则需指明该字段所在的表，如 department_id 在 SELECT 中需写为 employees. department_id 或 departments.department_id，或者用表的别名代替表名（若给表起了别名，在 SELECT 和 WHERE 中必须使用别名，相当于别名覆盖了原表名）
SELECT e.employee_id, e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
# 或
SELECT e.employee_id, e.last_name, d.department_name, l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;
等值连接和非等值连接
上述为等值连接
非等值连接
SELECT last_name, salary, garde_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
自连接和非自连接
上述为非自连接
自连接，表中自我引用，如 employees 表中 manager_id 相当于对 employee_id 的引用
SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;
内连接和外连接
上述均为内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表不匹配的行以外，还查询到左表或右表中不匹配的行
外连接的分类：左外连接（还查询左表不匹配的行）、右外连接、满外连接
左外连接
SQL92语法（MySQL不支持）
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);SQL99语法，可以查询到 emplees 表中 department_id 为NULL的数据，即查询到所有员工的 department_name
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;右外连接
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;满外连接
MySQL不支持 FULL OUTER JOIN
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
UNION 操作符
UNION 操作符返回两个查询的结果的并集，去除重复记录
UNION ALL 返回两个查询的结果的并集，不去除重复记录
在实际使用中能用 UNION ALL 就不使用 UNION，因此满外连接使用上图①④进行 UNION ALL
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;SQL99新特性
自然连接 NATURAL JOIN
可以理解为SQL92中的等值连接，会自动查询两张连接表中所有相同的字段，然后进行等值连接
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
AND e.manager_id = d.manager_id;
# 等同于
SELECT employee_id, last_name, department_name
FROM employees e NATURAL JOIN departments d;USING
替换等值连接的 =，如上述查询可以写为
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
USING (department_id, manager_id);
