$3K2$UAXJM)2GWBB]AO__~J.png

中图（内连接）

  1. select employee_id, department_name
  2. from employees e join departments d
  3. on e.department_id = d.department_id;

左上图（左外连接）

  1. select employee_id, department_name
  2. from employees e left join departments d
  3. on e.department_id = d.department_id;

右上图（右外连接）

  1. select employee_id, department_name
  2. from employees e right join departments d
  3. on e.department_id = d.department_id;

左中图

  1. select employee_id, department_name
  2. from employees e left join departments d
  3. on e.department_id = d.department_id
  4. where d.department_id is null;

右中图

  1. select employee_id, department_name
  2. from employees e right join departments d
  3. on e.department_id = d.department_id
  4. where e.department_id is null;

左下图（满外连接）

方式一：左上图 union all右中图

  1. select employee_id, department_name
  2. from employees e left join departments d
  3. on e.department_id = d.department_id
  4. union all
  5. select employee_id, department_name
  6. from employees e right join departments d
  7. on e.department_id = d.department_id
  8. where e.department_id is null;

方式二：左中图 union all右上图

  1. select employee_id, department_name
  2. from employees e left join departments d
  3. on e.department_id = d.department_id
  4. where d.department_id is null
  5. union all
  6. select employee_id, department_name
  7. from employees e right join departments d
  8. on e.department_id = d.department_id;

右下图

左中图 union all右中图

  1. select employee_id, department_name
  2. from employees e left join departments d
  3. on e.department_id = d.department_id
  4. where d.department_id is null
  5. union all
  6. select employee_id, department_name
  7. from employees e right join departments d
  8. on e.department_id = d.department_id
  9. where e.department_id is null;