内连接

等值连接

结果为两个表的交集部分

-- sql92语法
select t1.name, t2.name 
from user t1, department t2
where t1.department_id=t2.id

-- sql99语法，inner可以省略
select t1.name, t2.name 
from user t1 
inner join department t2
on t1.department_id=t2.id

非等值连接

一个表中的某个列在另一个表的某两列区间内

-- sql92语法
select t1.name, t1.salary, t2.grade 
from salary t1, grade t2
where t1.salary>t2.min_salary and t1.salary<=t2.max_salary

-- sql99语法，inner可以省略
select t1.name, t1.salary, t2.grade 
from salary t1 
inner join grade t2
on t1.salary>t2.min_salary and t1.salary<=t2.max_salary

自连接

一个表跟自己连接

-- sql92语法
select t1.name, t2.name 
from user t1, user t2
where t1.leader_id=t2.id

-- sql99语法，inner可以省略
select t1.name, t2.name 
from user t1
inner join user t2
on t1.leader_id=t2.id

外连接（sql99语法）

主表中所有数据，从表中如果有匹配的显示，如果没有匹配的显示null

左外连接

左边的表是主表，left outer join，outer可以省略

-- 查询所有人的部门
select t1.name, t2.name 
from user t1
left join department t2
on t1.department_id=t2.id 

-- 查询有部门的人
select t1.name, t2.name 
from user t1
left join department t2
on t1.department_id=t2.id 
where t2.id is not null

-- 查询部门是空的人
select t1.name, t2.name 
from user t1
left join department t2
on t1.department_id=t2.id 
where t2.id is null

右外连接

右边的表是主表

-- 查询所有人的部门
select t2.name, t1.name 
from department t1
right outer join user t2
on t1.id=t2.department_id

全外连接

mysql不支持全外连接

交叉连接（sql99语法）

笛卡尔积

select t1.name, t2.name 
from user t1
cross join department t2;