连接查询

分类：
– 内连接 [inner] join on
– 外连接
• 左外连接 left [outer] join on
• 右外连接 right [outer] join on
含义：又称多表查询
使用 ON 子句创建多表连接

SELECT employee_id, city, department_name
FROM employees e 
JOIN departments d
ON d.department_id = e.department_id 
JOIN locations l
ON d.location_id = l.location_id;

join连接

SELECT
    bt.id,
    NAME,
    boyName 
FROM
    beauty bt
inner join
    boys b
on
    bt.boyfriend_id = b.id;

内连接

等值连接

SELECT
    last_name,
    department_name,
    job_title
from
    employees e
join
    departments d
on
    e.department_id = d.department_id
join
    jobs j
on
    e.job_id = j.job_id
order by
    department_name desc;

非等值连接

SELECT
    salary,
    grade_level
from
    employees e
join 
    job_grades j
on
    e.salary between j.lowest_sal and j.highest_sal;

自连接

SELECT
    e.last_name yuangong,
    m.last_name laoban
from
    employees e
join 
    employees m
on
    e.manager_id = m.employee_id
where 
    e.last_name like('%k%');

外连接：
用于查询一个表中有，另一个表中没有的记录
左外连接

select 
bt.name
from
    beauty bt
left join
    boys b
on
    bt.boyfriend_id = b.id
where
    b.id is null;

右外连接
全外连接
full join

交叉连接
cross join
证明笛卡尔乘积