等值连接与非等值连接

等值连接

连接条件为等于号
例：

select employee_id, department_name, dept.department_id
from employees emp, departments dept
where emp.department_id = dept.department_id;

非等值连接

连接条件不是等号
例：

select e.last_name, e.salary, j.grade_level
from employees e, job_grades j
where e.salary >= j.lowest_sal and e.salary <= j.highest_sal;

自连接与非自连接

自连接

相同的表进行连接
查询员工id,员工姓名及其管理者的id和姓名

select emp.employee_id, emp.last_name, mgr.employee_id, mgr.last_name
from employees emp, employees mgr
where emp.manager_id = mgr.manager_id;

非自连接

不同的表进行连接

select employee_id, department_name, dept.department_id
from employees emp, departments dept
where emp.department_id = dept.department_id;

内连接与外连接

内连接

合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行（只含匹配项）

select employee_id, department_name, dept.department_id
from employees emp, departments dept
where emp.department_id = dept.department_id;
# 等价于
select employee_id, department_name, dept.department_id
from employees emp join departments dept
on emp.department_id = dept.department_id;
# 等价于
select employee_id, department_name, dept.department_id
from employees emp inner join departments dept
on emp.department_id = dept.department_id;
# 等价于
select employee_id, department_name, dept.department_id
from employees emp join departments dept
where emp.department_id = dept.department_id;

SQL99语法实现内连接

SELECT last_name,department_name,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 employee_id, department_name, dept.department_id
from employees emp left outer join departments dept
on emp.department_id = dept.department_id;
# 等价于
select employee_id, department_name, dept.department_id
from employees emp left join departments dept
on emp.department_id = dept.department_id;

右外连接

两个表在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行。

select employee_id, department_name, dept.department_id
from employees emp right outer join departments dept
on emp.department_id = dept.department_id;
# 等价于
select employee_id, department_name, dept.department_id
from employees emp right join departments dept
on emp.department_id = dept.department_id;

满外连接

MySQL不支持full outer join