关于mysql:mysql的连接查询

1. 图示各种连贯：

INNER JOIN(JOIN):

LEFT JOIN:

RIGHT JOIN:

2. 应用案例

创立 a\_table 和 b\_table，并插入测试数据

CREATE TABLE `a_table` (`a_id` int(11) DEFAULT NULL,
  `a_name` varchar(10) DEFAULT NULL,
  `a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into a_table values(1,'老潘','总裁部');
insert into a_table values(2,'老王','秘书部');
insert into a_table values(3,'老张','设计部');
insert into a_table values(4,'老李','运行部');

CREATE TABLE `b_table` (`b_id` int(11) DEFAULT NULL,
  `b_name` varchar(10) DEFAULT NULL,
  `b_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into b_table values(2,'老王','秘书部');
insert into b_table values(3,'老张','设计部');
insert into b_table values(5,'老刘','人事部');
insert into b_table values(6,'老黄','生产部');
insert into b_table values(7,'老张','测试部');

各种连贯测试的 sql 脚本：

-- left join
left join select a.a_name,a.a_part,b.b_name,b.b_part from a_table a 
left join b_table b on a.a_id = b.b_id;
-- 主表与左连贯表中若有 1:n 的连贯，选取左连贯表中最大的 id 进行左连贯查问
select a.a_name,a.a_part,b.b_name,b.b_part
from a_table a
left join(
    select * from b_table t1 
    join (select max(b_id) as id from b_table group by b_name ) t2 on t1.b_id = t2.id
)b on a.a_name = b.b_name;
-- inner join 
select a.a_name,a.a_part,b.b_name,b.b_part
from a_table a
inner join b_table b on a.a_id = b.b_id;
-- right join
select a.a_name,a.a_part,b.b_name,b.b_part
from a_table a
right join b_table b on a.a_id = b.b_id;

以上参考自：https://blog.csdn.net/plg17/article/details/78758593
https://blog.csdn.net/J\_\_Max/article/details/87453024