多个表可以连接起来进行查询。

左右连表

左连接、右连接、内连接

展示用户信息&部门名称

主表 left outer join 从表 on 主表.x = 从表.id

select * from info left outer join depart on info.depart_id = depart.id;

select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;

从表 right outer join 主表 on 主表.x = 从表.id

select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;

为了更加直接的查看效果，我们分别在 depart 表 和 info 中额外插入一条数据。

insert into depart(title) values("运维");

这样一来主从表就有区别：

• info主表，就以info数据为主，depart为辅。

  select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;

• depart主表，，就以depart数据为主，info为辅。

  select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;

select * from info left outer join depart on ....
select * from depart left outer join info on ....

简写：select * from depart left join info on ....

-- 内连接:    表  inner join 表  on 条件
select * from info inner join depart on info.depart_id=depart.id;
+----+----------+-------------------+------+-----------+----+--------+
| id | name     | email             | age  | depart_id | id | title  |
+----+----------+-------------------+------+-----------+----+--------+
|  1 | mufeng   | 598779784@qq.com  |   19 |         1 |  1 | 开发   |
|  2 | 于超     | pyyu@live.com     |   49 |         1 |  1 | 开发   |
|  3 | deepwind | deepwind@live.com |    9 |         2 |  2 | 运营   |
|  4 | tony     | tony@live.com     |   29 |         1 |  1 | 开发   |
|  5 | kelly    | kelly@live.com    |   99 |         3 |  3 | 销售   |
|  6 | james    | james@live.com    |   49 |         1 |  1 | 开发   |
|  7 | gailun   | gailun@live.com   |   49 |         1 |  1 | 开发   |
+----+----------+-------------------+------+-----------+----+--------+

到目前为止SQL执行顺序：
    join 
    on 
    where 
    group by
    having 
    select
    order by
    limit

上下连表

用的少

select id,title from depart 
union
select id,name from info;
select id,title from depart 
union
select email,name from info;
-- 列数需相同

select id from depart 
union
select id from info;
-- 自动去重

select id from depart 
union all
select id from info;
-- 保留所有