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